10

I'm using the following recursive CTE as a minimal example, but in general, the optimizer has to use default 'guessed' cardinalities for recursive CTEs:

with recursive w(n) as ( select 1 union all select n+1 from w where n<5 ) select * from w;
/*
 n
---
 1
 2
 3
 4
 5
*/

explain analyze
with recursive w(n) as ( select 1 union all select n+1 from w where n<5 ) select * from w;
/*
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 CTE Scan on w  (cost=2.95..3.57 rows=31 width=4) (actual time=0.005..0.020 rows=5 loops=1)
   CTE w
     ->  Recursive Union  (cost=0.00..2.95 rows=31 width=4) (actual time=0.003..0.017 rows=5 loops=1)
           ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
           ->  WorkTable Scan on w w_1  (cost=0.00..0.23 rows=3 width=4) (actual time=0.002..0.002 rows=1 loops=5)
                 Filter: (n < 5)
                 Rows Removed by Filter: 0
*/

Note the rows=31 estimated and rows=5 actual cardinalities in the above plan. In some cases 100 seems to be used as an estimate, I'm not sure the exact logic behind the guesses.

In my real world problem, the poor cardinality estimate is preventing a fast 'nested loops' plan from being chosen. How can I 'hint' the optimizer cardinality for a recursive CTE to work around this?

  • 5
    This is one of the many cases where statistics hints would be really nice to have. There's COST on functions, but not much else. I'd suggest raising it on pgsql-hackers, but you'd just get caught up in the n'th iteration of the "hints" debate, wasting masses of hot air and achieving nothing :-( – Craig Ringer May 14 '14 at 8:11
8

I've worked round the issue like this, but I'm hoping there is a less kludgy way:

explain analyze
with recursive w(n) as ( select 1 union all select n+1 from w where n<5 )
select * from w limit (select count(*) from w);
/*
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3.66..3.72 rows=3 width=4) (actual time=0.032..0.034 rows=5 loops=1)
   CTE w
     ->  Recursive Union  (cost=0.00..2.95 rows=31 width=4) (actual time=0.003..0.019 rows=5 loops=1)
           ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
           ->  WorkTable Scan on w w_1  (cost=0.00..0.23 rows=3 width=4) (actual time=0.002..0.002 rows=1 loops=5)
                 Filter: (n < 5)
                 Rows Removed by Filter: 0
   InitPlan 2 (returns $2)
     ->  Aggregate  (cost=0.70..0.71 rows=1 width=0) (actual time=0.029..0.030 rows=1 loops=1)
           ->  CTE Scan on w w_2  (cost=0.00..0.62 rows=31 width=0) (actual time=0.005..0.025 rows=5 loops=1)
   ->  CTE Scan on w  (cost=0.00..0.62 rows=31 width=4) (actual time=0.000..0.002 rows=5 loops=1)
*/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.