Re: BUG #19708: Hash Join becomes about 300x slower with higher work_mem

Hi Iany,

Thank you for the report, and the nice reproduction script.

Your query grows with the 4th power of the number of rows, and the table
statistics
show 2910 rows for that table. So the plan estimates 211 quadrillion rows,
see
a decluttered plan showing the row estimates.

Hash Join (rows=211477613278003200) -- (m * n^2)^2 / (200)
Hash Cond: (l.g = r.g)
CTE x
-> Nested Loop (rows=6503500800) -- m * n ^2
-> Function Scan on generate_series g (rows=768) -- m
-> Materialize (rows=8468100) -- n^2
-> Nested Loop (rows=8468100) -- n^2
-> Seq Scan on a a1 (rows=2910) -- n
-> Materialize (rows=2910) -- n
-> Seq Scan on a a2 (rows=2910) -- n
-> CTE Scan on x l (rows=6503500800) -- m * n^2
-> Hash (rows=6503500800) -- m * n^2
-> CTE Scan on x r (rows=6503500800) -- m * n ^ 2

> CREATE TABLE a();
> INSERT INTO a DEFAULT VALUES;
If you run an analyse here you get an accurate estimate of the number rows
in the table.

If analyse your table before the table
----
CREATE TABLE a();
INSERT INTO a DEFAULT VALUES;
+ANALYSE a;

SET enable_mergejoin = off;
----

It uses the same plan

work_mem exec time
64 kB 0.271 ms
16 MB 0.227 ms

Would you be able to reproduce the issue having rows = actual rows in the
plans.

--
Alexandre