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.
