Bad execution plan on simple recursive query. Vacuum and analyse performed. Why merge join is used instead of nested loop?
On table plm.link_plm statistics ok.
vacuum analyze plm.link_plm.
Table size
select count(*) from plm.link_plm
-- 12s -> 69568347 rows.
Test with and without nested loop
set enable_hashjoin to on;
set enable_mergejoin to on;
with recursive r as (
select l.* from plm.link_plm l where l.ci = 14546722
union all select l.* from r join plm.link_plm l on l.ci = r.pi
) select distinct * from r;
-- merge join used, duration more than 1 hour
below the execution plan:
HashAggregate (cost=0.00..0.00 rows=0 width=0)
Group Key: r.pi, r.ci, r.name, r.type, r.matrix, r.wipfirst, r.relfirst, r.partition, r.j
CTE r
-> Recursive Union (cost=0.00..0.00 rows=0 width=0)
-> Bitmap Heap Scan on link_plm l (cost=0.00..0.00 rows=0 width=0)
Recheck Cond: (ci = 14546722)
-> Bitmap Index Scan on link_plm_ci_pi_idx (cost=0.00..0.00 rows=0 width=0)
Index Cond: (ci = 14546722)
-> Merge Join (cost=0.00..0.00 rows=0 width=0)
-> Sort (cost=0.00..0.00 rows=0 width=0)
Sort Key: r_1.pi
-> WorkTable Scan on r r_1 (cost=0.00..0.00 rows=0 width=0)
-> Materialize (cost=0.00..0.00 rows=0 width=0)
-> Sort (cost=0.00..0.00 rows=0 width=0)
Sort Key: l_1.ci
-> Seq Scan on link_plm l_1 (cost=0.00..0.00 rows=0 width=0)
-> CTE Scan on r r (cost=0.00..0.00 rows=0 width=0)
set enable_hashjoin to off;
set enable_mergejoin to off;
with recursive r as (
select l.* from plm.link_plm l where l.ci = 14546722
union all select l.* from r join plm.link_plm l on l.ci = r.pi
) select distinct * from r;
-- nested loop on index used, duration 0.1s