Four PostgreSQL arguments that came out even
Some advice gets repeated in code review because it sounds like it should matter. I picked four I have seen argued about and measured them. I expected at least two clear winners. I got three ties and one result that went the opposite way from the advice.
Setup
PostgreSQL 18.6, default settings. 2020 13-inch MacBook Pro, Intel i5-8257U, 8 GB RAM. A 2,000,000-row table u with an id, an email, a status and a timestamp. Times are execution times from EXPLAIN (ANALYZE, TIMING OFF), warm cache, first run discarded, median of the rest.
IN, ANY or a VALUES join for 1,000 ids
| median | runs | buffers | |
|---|---|---|---|
id IN (1999, 3998, ...) | 26 ms | 15 to 54 ms | 3,001 |
id = ANY('{...}'::bigint[]) | 19 ms | 15 to 20 ms | 3,001 |
JOIN (VALUES ...) v(i) ON id = i | 20 ms | 16 to 21 ms | 3,001 |
All three read exactly the same pages. The medians differ, but one IN run took 54 ms and another 15 ms, so I would not read anything into the gap. Use whichever form your driver makes easy. ANY has one practical advantage when the array is passed as a single parameter: the statement text stays the same whatever the number of ids, which is kinder to prepared statements and to pg_stat_statements.
text, smallint or enum for a status column
Three values, two million rows, each stored three ways in its own one-column table.
| table | index | |
|---|---|---|
| text | 72.6 MB | 13.9 MB |
| smallint | 73.0 MB | 13.9 MB |
| enum | 72.7 MB | 13.9 MB |
Every row has a 24-byte header and is padded to 8 bytes, so a 2-byte smallint and a 7-byte 'active' end up the same size. The indexes are identical because B-tree deduplication stores each of the three values once with a list of row pointers. In a wider table the column order can change the padding, but the type of a short status column is not where the space goes. Choose it for what it lets you check.
Columns that are usually empty
One million rows with an id and ten more integer columns. In one table the ten columns are all NULL, in the other all filled.
| table | per row | |
|---|---|---|
| ten NULL columns | 44 MB | 36 bytes |
| ten filled columns | 77 MB | 68 bytes |
A NULL takes no space in the row. It costs one bit in the row's null bitmap. Optional columns that are mostly empty are close to free, which is worth knowing before splitting a table to save space.
count(DISTINCT) against GROUP BY
The advice I have seen most often: count(DISTINCT x) is slow, rewrite it as a count over a GROUP BY subquery. On two million unique emails:
| median | |
|---|---|
count(DISTINCT email) | 1.95 s |
count(*) FROM (SELECT email FROM u GROUP BY email) | 3.12 s |
The rewrite was slower. I am not claiming it always is. Which one wins depends on the plan the GROUP BY gets and on work_mem, and both change between versions and settings. That is the point: this is advice to check with EXPLAIN on your own data, not to apply.
What I did not test
- IN lists with tens of thousands of values.
- Status columns inside a wider row, where alignment can differ.
count(DISTINCT)with morework_mem, or on columns with few distinct values.- Other hardware. Compare the ratios, not the seconds.
Reproduce it
run2.py and results2.json. Sections 1, 9, 10 and 12 of the script.