Hi Tom,
I did a little more experiment on that this weekend.
You are right about NULL keys in the inner table. 19 handles those
better.
The problem is NULL keys on the outer side. 18 just returns those rows.
19 keeps them until the end, and they go to disk once they pass
work_mem / 16.
For example, with default settings:
CREATE TABLE b (k int PRIMARY KEY);
INSERT INTO b SELECT generate_series(1, 1000);
CREATE TABLE a AS SELECT NULL::int AS k, repeat('x', 100) AS pad
FROM generate_series(1, 1000000);
VACUUM ANALYZE a, b;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT count(b.k) FROM a LEFT JOIN b ON a.k = b.k;
On release builds 18 takes 22 ms with no temp files. 19 takes 110 ms
and writes 106 MB of temp files.
Given that I think this is worth fixing in 19 and I will post a patch in a
different
thread. If you would rather leave 19 as is, I think it needs a release note.
Thanks,
Shihao
