Postgres nested loop not used in recursive query

40 Views Asked by At

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
0

There are 0 best solutions below