At one interview I was asked about VIEWs. I answered honestly: in real projects I had barely run into them; for aggregates it is safer to keep a separate table. One of the interviewers said, “You understand nothing about VIEWs,” and everyone laughed.
A lot of time has passed and the number of VIEWs in my code never grew, but the question stayed with me: what if things have changed? New engines have shipped. So I brought up MySQL 8.4.11 and PostgreSQL 17.11, loaded byte-for-byte identical data into both — a million orders, two million line items, 780 thousand payments — and ran the main scenarios one after another.
The test rig is public and reproducible: github.com/alex-frolov/mysql-postgresql-view-test.
Here is what came out of it.
Methodology
I measure server-side time: the root actual time from EXPLAIN ANALYZE in MySQL, Execution Time in PostgreSQL. Two warm-ups, seven measurements, take the median. Comparing absolute milliseconds across machines makes no sense; only ratios between identical queries matter. The query window is the same everywhere — June, merchant with ID 42.
A simple view costs nothing
A wrapper over orders with no aggregation runs at one and a half to two milliseconds in both engines, the spread stays within noise, and the plans match the direct query. MySQL folds the definition into the query (the MERGE algorithm), PostgreSQL expands it through the rewrite rule before the planner even sees it. The optimizer simply does not notice that you queried a view.
A reusable filter and a stable read contract are free. Boring — and boring is the best case here.
One CAST, thirteen times more expensive
An aggregating daily-revenue view:
CREATE VIEW v_merchant_daily AS
SELECT o.merchant_id,
CAST(o.created_at AS DATE) AS day,
COUNT(*) AS orders_cnt,
SUM(o.amount_total) AS revenue
FROM orders o
WHERE o.status IN ('paid', 'shipped', 'completed')
GROUP BY o.merchant_id, CAST(o.created_at AS DATE);
The query on top is exactly what anyone would write — filter by merchant, filter by day, order by day.
PostgreSQL returns it in 24.5 ms. The same result fetched directly from the table takes 1.85 ms. A thirteenfold difference out of nowhere.
My first explanation was the standard one: “the aggregate computes before the filter, the engine groups the whole table.” I opened the plan and found out I was wrong. No full-table aggregation anywhere: both predicates get pushed below the grouping, exactly 469 rows are aggregated — the same as in the direct query.
The difference sits in another line: Rows Removed by Filter: 9531. The direct query uses the (merchant_id, created_at) index on both columns. Through the view, the date condition arrives as an expression — CAST(created_at AS DATE) >= '2026-06-01' — and an expression cannot serve as a range bound on the index. Only the merchant condition survives down to the index: all ten thousand of that merchant's orders get pulled out of the heap from scattered pages, and there the filter throws away 9531 of them. That comes to 8971 buffer accesses against 440.
MySQL spends 4.85 ms against 2.16 ms in the same spot — a little over two times. It evaluates the CAST right inside the index (index condition pushdown), cutting candidate rows off before they ever leave the table.
A subquery with the same text took 26.9 ms, a CTE took 24.6 ms. The problem is not CREATE VIEW; it is the semantics. Any construct with the same grouping behaves the same way.
The takeaway I keep: the view exposes a column called day while the index lives on created_at. It hands you an interface that looks like a table but cannot reach the index — silently. Ever since, whenever I see CREATE VIEW ... GROUP BY in a pull request, I ask which columns it exposes and whether indexes exist underneath them.
The cascade that beat the handwritten query
Three views stacked: a daily aggregate, a sum on top, a join with the reference table at the very top. Every level recomputes on every access. The obvious expectation: the deeper the stack, the worse.
PostgreSQL confirmed it: 832 ms against 243 ms for the single-pass query.
MySQL refuted it: the cascade took 1436 ms while the direct query took 2432 ms. The stack of three views was almost twice as fast as the handwritten single-pass report.
The reason is the shape of the join, not the depth. The direct query joins orders with the reference table before grouping — a nested loop doing 750000 point lookups by primary key. In the cascade the join moves to the top, where after two aggregations only 75 rows remain out of 750 thousand.
PostgreSQL's cascade loses parallelism, and its intermediate 48 thousand groups do not fit into the default 4 MB work_mem: 30 MB spilled to disk per execution. Across nine runs the temp_bytes counter grew by 278 MB — for a report that returns ten rows.
Whether a cascade is expensive is a question of join shape and memory settings, not depth. You cannot predict it; you can only test it, and at production volumes.
ORDER BY inside a view is a time bomb
A codebase classic: CREATE VIEW ... ORDER BY created_at DESC — “so it’s definitely sorted”. Today both engines return sorted data instantly, reading the index backwards. Tomorrow the optimizer recalculates statistics, picks a different access path, and code that silently relied on ordering starts returning a random ten.
The SQL standard does not guarantee row order for a view, and the MySQL documentation says so in plain text. Such a bug does not crash, does not log anything, and surfaces six months later as a user complaint.
Where is the cache?
Reading a materialized view with a unique index takes 0.24 ms against 24.5 ms for the live aggregate — a hundred times faster. Tempting to credit caching. But neither engine has a result cache: MySQL removed the Query Cache in 8.0, PostgreSQL never had one. Thirty repetitions of the same query produce a flat series — every run computes again.
A materialized view is faster not because it caches, but because there is nothing left to compute. The price sits elsewhere: REFRESH on a million orders takes 1146 ms and is always full — no incremental refresh in PostgreSQL.
What reading does to writing
Fifteen-second insert windows, batches of eight hundred rows. With two background readers hammering the aggregating view, write throughput dropped by 7% in MySQL and by 16% in PostgreSQL. Replacing the live view with a summary table maintained incrementally by the writer removed the drop entirely.
A view itself does not get in the way of writes. A constant reader of a heavy aggregate does.
The number that matters: scale
| Orders | MySQL, view/direct | PostgreSQL, view/direct |
|---|---|---|
| 10,000 | 1.6× | 2.7× |
| 1,000,000 | 2.3× | 10.7× |
| 10,000,000 | 2.5× | 50.4× |
MySQL holds steady across the whole range — index condition pushdown works at any volume. The PostgreSQL gap grows with the data. And a summary table maintained by the writer does not depend on volume at all: 0.02–0.20 ms from ten thousand to ten million orders — thousands of times faster than a live view at the top end.
Up to tens of thousands of rows, choosing between a live view and a summary table is a matter of taste. Closer to a million it becomes a matter of architecture. Past a million there is no choice left.
Takeaways
- A simple view really consumes nothing; both engines look straight through it.
- An aggregating view in the hot path is a bad approach: its cost grows faster than the data.
- Check which columns a view exposes and whether indexes sit underneath.
- ORDER BY inside a view breeds silent bugs instead of convenience.
- A materialized view pays off only where reads greatly outnumber writes and data lag upsets nobody.
- A summary table with incremental maintenance is orders of magnitude cheaper and volume-independent.
Full walkthrough with query plans, memory measurements and the write-load scenarios: frolov.guru/en/writing/mysql-postgresql-view-test. The rig reproduces from github.com/alex-frolov/mysql-postgresql-view-test.
The list of niches I named at that interview has not changed by a single item. What changed is this: behind every item there is now a query plan instead of a habit.
I'm Aleksander Frolov, a senior/staff backend engineer building highload PHP systems (Symfony, payments, auctions). I write about architecture and performance on frolov.guru.
Top comments (1)
The index-bound explanation is the part worth repeating: through the view the predicate arrives as
CAST(created_at AS DATE) >= ..., which cannot serve as a range bound, so only the merchant condition reaches the index and the engine pulls ten thousand scattered rows and throws away 9,531 of them.Rows Removed by Filteris precisely where this class of slowdown hides, because the plan still reads as an index scan.The cascade of three views beating the handwritten single-pass query on MySQL is the result I'd have called a measurement error: depth isn't the cost driver, join shape and
work_memare, and 278 MB of temp writes across nine runs for a report that returns ten rows is a strong argument for testing at production volume instead of reasoning about it. Did you try an expression index onCAST(created_at AS DATE)to see whether it closes the PostgreSQL gap on the view path, or does the grouping still force the wide heap read?