Hi Tom,
> I'm a bit skeptical about this one. The older code would have
> put those rows into the primary hash table rather than a dedicated
> tuplestore, but either way you risk having to dump them to disk.
Sorry, my message is not that clear, I am saying mostly outer hash
expressions.
In 18 the outer hash expression keeps nulls when the join fills the
outer side, so a NULL-keyed outer row is probed, matches nothing, and is
returned right away.
In 19 ExecHashJoinOuterGetTuple() puts it into hj_NullOuterTupleStore, which
is sized at work_mem / 16, so with the default 4MB it goes to disk
after 256kB.
To reproduce, with default settings:
SET max_parallel_workers_per_gather = 0;
CREATE TABLE big AS
SELECT CASE WHEN g % 100 = 0 THEN g % 1000 END AS k,
repeat('x', 100) AS pad
FROM generate_series(1, 300000) g;
CREATE TABLE dim AS SELECT g AS id FROM generate_series(1, 1000) g;
ANALYZE big, dim;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM big b LEFT JOIN dim d ON b.k = d.id;
SET temp_file_limit = '1MB';
SELECT count(*) FROM big b LEFT JOIN dim d ON b.k = d.id;
On 19rc1 the plan shows "temp read=4025 written=4025" with
"Batches: 1", and the last query fails with "temporary file size
exceeds temp_file_limit". On 18.6 there are no temp files and it returns
300000.
Thanks,
Shihao
