Postgres Runs Queries One Row at a Time

When you run SELECT SUM(total) FROM orders WHERE status = 'shipped' across 50 million rows, Postgres doesn't grab matching rows in chunks. It pulls one row from the scan, checks the filter, passes it to the aggregate, pulls the next row, repeats. Every single row walks the full operator tree individually.

That per-row overhead — function calls, virtual dispatches, pointer chasing — is invisible at 100 rows. At 50 million rows, it's the reason your analytical query takes 40 seconds when the equivalent in ClickHouse takes 0.3.

Why this matters

This pattern is called the Volcano execution model, and it's been Postgres's query engine design since the 1990s. It was a smart trade-off when memory was tight and predictable resource usage mattered more than raw throughput.

Modern CPUs process 8, 16, or 32 values per instruction using SIMD. When your query engine handles one row at a time, that parallelism sits idle. You also thrash the instruction cache because every row repeats the same operator chain. This is the gap between Postgres and every purpose-built analytical engine: DuckDB, ClickHouse, Arrow compute kernels.

How it works

Vectorized execution changes the unit of work from one row to a batch of 1,000 to 4,000 rows packed into column arrays. Each operator processes entire batches instead of individual rows.

Instead of calling the hash function four million times, the hash operator applies it to a column array using SIMD. Instead of evaluating a WHERE predicate per row, the scan compares an entire vector at once.

Operator fusion goes further. Instead of materializing intermediate results between operators (scan writes buffer → filter reads buffer → aggregate reads buffer), fused operators pipeline data through CPU registers. No intermediate materialization means far less memory traffic.

Where this helps

• Large aggregations — SUM, COUNT, AVG over millions of rows where per-row overhead dominates actual computation
• Filter-heavy scans — queries rejecting 95%+ of rows, burning CPU on data that never reaches the result
• Hash joins on big tables — building and probing hash tables one row at a time is needlessly expensive
• Real-time dashboards — when a 30-second query needs to drop to under a second without migrating off Postgres

Watch out

Stock Postgres doesn't have full vectorized execution today. The 300x improvements in the HN story come from custom query engine work. A few realities before you assume Postgres can replace your analytics warehouse:

• OLTP queries won't benefit — point lookups and small updates are already fast; batch overhead would slow them down
• Columnar storage matters — SIMD needs contiguous arrays. Row-oriented heap tables limit vectorization gains
• Extensions are partial — pg_analytics and DuckDB's Postgres scanner bring vectorized execution to Postgres data, but with compatibility trade-offs

Try it yourself

-- See the bottleneck in your own queries:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT customer_id, SUM(total)
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY customer_id;

-- Compare with DuckDB's vectorized engine on the same data:
INSTALL postgres;
LOAD postgres;
ATTACH 'dbname=shop' AS shop (TYPE postgres);

SELECT customer_id, SUM(total)
FROM shop.orders
WHERE created_at >= '2026-01-01'
GROUP BY customer_id;

TL;DR

• What happened: Engineers demonstrated 300x analytics speedups in Postgres using batch execution, operator fusion, and SIMD
• Why it matters: The Volcano row-by-row model is the hidden bottleneck behind every slow Postgres analytical query
• What to try today: Run EXPLAIN ANALYZE on your slowest aggregation, then run the same query through DuckDB's Postgres scanner and compare timings