Five PostgreSQL queries that did more work than I asked for
Most slow queries I have looked at are not doing anything clever. They are doing extra work that nobody asked for, because one word in the SQL told PostgreSQL to.
I took five of those words and measured each one against the version without it, on the same table. The biggest gap was UNION: 13.6 seconds, against 0.68 seconds for UNION ALL on the same two halves of the table.
Setup
One table, u, with 2,000,000 rows: a bigint identity key, an email, a status, a timestamptz and an md5 note.
PostgreSQL 18.6, default settings (work_mem 4MB, shared_buffers 128MB, up to two parallel workers per query). Machine: 2020 13-inch MacBook Pro, Intel i5-8257U, 8 GB RAM. Every time below is the execution time from EXPLAIN (ANALYZE, TIMING OFF), warm cache, first run thrown away, median of the rest.
Results
| without | with | ||
|---|---|---|---|
| UNION ALL / UNION | 0.68 s | 13.6 s | two halves that cannot overlap |
| CTE inlined / MATERIALIZED | 0.24 ms | 1,973 ms | filter on the primary key |
| TABLESAMPLE / ORDER BY random() | 0.3 ms | 1,313 ms | one random row |
| one column / SELECT * | 32.8 ms | 64.8 ms | about 100,000 rows by date range |
| expression index / no index | 0.5 ms | 623 ms | WHERE lower(email) = ... |
UNION
I split the table at id = 1,000,000 and put the halves back together. With UNION ALL that is just reading the rows. With UNION, PostgreSQL has to make sure no email appears twice, so it deduplicates two million values before it can count them. It has no way to know the halves cannot overlap. I do, so I should have written ALL.
CTE with MATERIALIZED
WITH x AS (SELECT * FROM u) SELECT * FROM x WHERE id = 777777 took 0.24 ms. PostgreSQL 12 and later fold a CTE like this into the outer query, so the filter reaches the primary key index.
Add MATERIALIZED and it builds the whole CTE first, then filters it: 1,973 ms and every page of the table. The keyword is there for the cases where you want that fence. Here I did not.
One random row
ORDER BY random() LIMIT 1 reads all two million rows and sorts them by a random key to return one: 1,313 ms.
TABLESAMPLE SYSTEM (0.01) LIMIT 1 read one page and took 0.3 ms. It is not the same thing, though. SYSTEM picks whole pages, so rows that sit together come back together, and a sample this small can come back empty. For a quick look at some data it is fine. For anything that has to be fair, it is not.
SELECT *
About 100,000 rows by a created_at range, ordered by the same column, with an index on it. Asking for only created_at let PostgreSQL answer from the index alone: 279 buffers, 32.8 ms. SELECT * had to visit the table for every row: 1,718 buffers, 64.8 ms.
EXPLAIN ANALYZE does not send rows to the client, so none of this is network time. It is the table visits.
A function in WHERE
WHERE lower(email) = '[email protected]' with no matching index read the whole table in parallel: 623 ms. CREATE INDEX ON u (lower(email)) brought it to 0.5 ms and 4 buffers. An index on plain email would not have helped; the index has to be on the same expression the query uses. This one was 81 MB.
What surprised me
How small the fixes are. Four of the five are one keyword or one column list. The fifth is one index.
And how forgiving the slow versions look in development. On a table of a few thousand rows every one of these finishes in a few milliseconds, and the difference only appears when the table grows.
What I did not test
- Larger
work_mem. UNION would have deduplicated in memory with more of it, and the gap would be smaller. - Tables bigger than RAM. Everything here was in the OS cache.
- Fair random sampling.
TABLESAMPLE BERNOULLIsamples rows rather than pages, which means reading every page; I did not time it. - Other hardware. Compare the ratios, not the seconds.
Reproduce it
The script and the raw numbers: run2.py, results2.json. The same script also measures the next two posts in this series.