Five PostgreSQL queries that did more work than I asked for
Short summary
A developer measured five common SQL patterns that force PostgreSQL to do unnecessary work—UNION instead of UNION ALL, MATERIALIZED CTEs, ORDER BY random(), SELECT * instead of specific columns, and unindexed expressions—and found gaps ranging from 20x to 4,000x slower on a 2 million row table.
- UNION deduplicated 2 million rows unnecessarily (13.6s) versus UNION ALL (0.68s) when the developer knew the halves couldn't overlap
- MATERIALIZED keyword forced PostgreSQL to build the entire CTE before filtering, turning a 0.24ms query into 1,973ms
- ORDER BY random() LIMIT 1 sorted all 2 million rows to get one (1,313ms); TABLESAMPLE SYSTEM did it in 0.3ms, though with different guarantees
- SELECT * required visiting the table for every row (1,718 buffers, 64.8ms) versus querying just the indexed column (279 buffers, 32.8ms)
Full summary
The author tested five SQL keywords that make PostgreSQL work harder than necessary, measuring the performance gap on a 2-million-row table. UNION versus UNION ALL showed the biggest difference: 13.6 seconds versus 0.68 seconds. The problem is that UNION deduplicates results automatically while UNION ALL doesn't, and PostgreSQL can't know when two result sets don't overlap. Other culprits included MATERIALIZED on CTEs (1,973 ms vs 0.24 ms), ORDER BY random() for sampling (1,313 ms vs 0.3 ms with TABLESAMPLE), SELECT * instead of specific columns (64.8 ms vs 32.8 ms), and missing expression indexes (623 ms vs 0.5 ms for WHERE lower(email) queries). Each fix was minimal—one keyword or one index—yet the performance swings were dramatic.
The real trap is that these inefficiencies hide in development. On small tables with thousands of rows, all these queries run in milliseconds and feel fine. The differences only surface when you hit millions of rows in production. The author ran tests on a 2020 MacBook Pro with PostgreSQL 18.6 at default settings, using EXPLAIN ANALYZE to measure actual execution time with a warm cache.
What makes this useful is the specificity. The UNION ALL problem only matters when you know the two halves can't overlap—the author had to write ALL explicitly because PostgreSQL can't infer that. The CTE issue depends on whether you actually need that fence; if you don't, inlining saves you from scanning the whole table. TABLESAMPLE isn't a fair replacement for ORDER BY random() since it samples pages instead of rows, so it works for quick data exploration but not when fairness matters. The author didn't test larger work_mem settings, tables bigger than RAM, or other hardware, so the absolute numbers won't transfer everywhere—but the ratios should.