Build1 publisher3 min readPublished
A CAST in the GROUP BY key turns 440 buffer reads into 8,971 in PostgreSQL 17
Aleksander Frolov loaded a million orders into MySQL 8.4.11 and PostgreSQL 17.11 and found the aggregating view 13 times slower on Postgres, even though the planner pushed both predicates below the grouping.
The Engineer · Build desk
What happened
- Aleksander Frolov loaded byte-for-byte identical data into MySQL 8.4.11 and PostgreSQL 17.11: a million orders, two million line items and 780,000 payments.
- A wrapper view over orders with no aggregation ran at one and a half to two milliseconds on both engines, and its plan matched the plan for the direct query.
- The aggregating daily-revenue view returned in 24.5 ms on PostgreSQL 17.11, against 1.85 ms for the same result read straight from the orders table.
- The PostgreSQL plan pushed both predicates below the grouping and aggregated exactly 469 rows, the same count as the direct query fed to its aggregate.
- The same grouping written as a subquery took 26.9 ms and as a CTE 24.6 ms, so the penalty did not come from CREATE VIEW.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- constraint A view can expose a column name that the index underneath it cannot serve, and the query on top still reads as correct SQL while every caller pays a heap fetch plus a discard.
- decision Reviewing a view definition gets a concrete question: which names does it expose, and does an index exist on the expression behind each one?
- contradiction Guidance to flatten view stacks does not port across engines, since MySQL's cascade beat its own single-pass report by about 1.7 times while flattening is what helped PostgreSQL.
- capability Engine choice changes what a view-heavy reporting schema costs, because index condition pushdown holds MySQL to roughly a sixth of the PostgreSQL penalty on the identical query.
In Frolov's PostgreSQL plan the extra work shows up on one line: Rows Removed by Filter: 9531 [12]. The direct query uses an index on (merchant_id, created_at) and gets both columns out of it [11]. Through the view, the date test arrives as CAST(created_at AS DATE) >= '2026-06-01', and an expression cannot serve as a range bound, so only merchant_id narrows the scan [11]. All ten thousand of that merchant's orders come out of the heap from scattered pages, and the filter throws 9531 of them away [12]. Add back the 469 rows that reach the aggregate and you have the full ten thousand [2]. Buffer accesses go from 440 to 8971 for an identical result [13], roughly twenty times the pages [3].
Frolov started with the textbook explanation, that the aggregate computes before the filter and the engine groups the whole table; he opened the plan and found no full-table aggregation anywhere [10]. Any construct with that grouping behaves the same way, he writes [15].
MySQL 8.4.11 spends 4.85 ms in the same spot against 2.16 ms direct, because it evaluates the CAST inside the index scan through index condition pushdown [14]. That is 2.25 times [4], against 13.2 times on Postgres [1].
The three-level stack is where the engines part. A daily aggregate, a sum on top, a join to a reference table at the very top, with every level recomputed on every access [17]. PostgreSQL took 832 ms against 243 ms for the single-pass report [18], 3.4 times worse [5]. MySQL took 1436 ms against 2432 ms, so the cascade beat the handwritten query by about 1.7 times [19][6]. The reason is the join shape: the direct query joins orders to the reference table before grouping, a nested loop of 750,000 primary-key lookups, while the cascade does that join at the top, where 75 rows are left out of 750,000 [20]. Frolov also notes that PostgreSQL's cascade loses parallelism [21].
The 13x transfers to your schema only if the range column of your index is the raw timestamp [11], the view exposes a derived key that callers will filter on [7], and each group leaves enough rows behind the filter that the heap fetch dominates: here, ten thousand orders for merchant 42 inside a million [12][3]. Frolov reports the median of seven runs after two warm-ups and says absolute milliseconds across machines mean nothing, only ratios between identical queries [2]. The rig is at github.com/alex-frolov/mysql-postgresql-view-test [4].
His standing check is that a view exposes a column called day while the index lives on created_at, so whenever he sees CREATE VIEW ... GROUP BY in a pull request he asks which columns it exposes and whether indexes exist underneath them [16]. The benchmark exists because an interviewer once told him, "You understand nothing about VIEWs" [22].
What to watch
- Whether an index on the CAST expression itself closes the 24.5 ms gap on the same rig, which would put the fix in the schema and not the query.
- Whether the ratios hold for a merchant with far fewer than ten thousand orders in the window, where the heap fetch no longer dominates.
- How much of PostgreSQL's 832 ms cascade is the lost parallelism Frolov reports, which a run with different worker settings would separate out.