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?
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 :-(