1

Postgres has no ideas about expected rows when using recursive CTE. Here is an expamle:

create table test_recursive (
id serial primary key,
parent_id int null references test_recursive,
name text not null);

insert into test_recursive (name) (select 'name_' || generate_series::text from generate_series(1, 10000)) ;

update test_recursive  set parent_id = (select id from test_recursive as tr where tr.id < test_recursive.id order by random() limit 1);

Record with id=1 will be parent for every other record, but record with id=2 only for small part:

with recursive t1 as (
select * from test_recursive where id = 1
union all
select test_recursive.* from test_recursive join t1 on t1.id = test_recursive.parent_id)
select count(*) from t1;
 count 
-------
 10000

--same query with id=2, actual number may vary because of random()
 count 
-------
   135

And with EXPLAIN:

explain analyze with recursive t1 as (
select * from test_recursive where id = 1
union all
select test_recursive.* from test_recursive join t1 on t1.id = test_recursive.parent_id)
select * from t1;
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 CTE Scan on t1  (cost=2572.57..2576.59 rows=201 width=40) (actual time=0.025..54.495 rows=10000 loops=1)
   CTE t1
     ->  Recursive Union  (cost=0.29..2572.57 rows=201 width=17) (actual time=0.023..51.377 rows=10000 loops=1)
           ->  Index Scan using test_recursive_pkey on test_recursive  (cost=0.29..8.30 rows=1 width=17) (actual time=0.022..0.024 rows=1 loops=1)
                 Index Cond: (id = 1)
           ->  Hash Join  (cost=0.33..256.02 rows=20 width=17) (actual time=0.515..2.237 rows=454 loops=22)
                 Hash Cond: (test_recursive_1.parent_id = t1_1.id)
                 ->  Seq Scan on test_recursive test_recursive_1  (cost=0.00..218.00 rows=10000 width=17) (actual time=0.029..0.821 rows=10000 loops=22)
                 ->  Hash  (cost=0.20..0.20 rows=10 width=4) (actual time=0.113..0.113 rows=455 loops=22)
                       Buckets: 1024  Batches: 1  Memory Usage: 9kB
                       ->  WorkTable Scan on t1 t1_1  (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.048 rows=455 loops=22)
 Planning time: 0.267 ms
 Execution time: 55.161 ms
(13 rows)

There is a huge difference between planned rows count (rows=201) and actual row count (rows=10000)

Same query with id=2:

                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 CTE Scan on t1  (cost=2572.57..2576.59 rows=201 width=40) (actual time=0.020..18.596 rows=135 loops=1)
   CTE t1
     ->  Recursive Union  (cost=0.29..2572.57 rows=201 width=17) (actual time=0.019..18.526 rows=135 loops=1)
           ->  Index Scan using test_recursive_pkey on test_recursive  (cost=0.29..8.30 rows=1 width=17) (actual time=0.018..0.019 rows=1 loops=1)
                 Index Cond: (id = 2)
           ->  Hash Join  (cost=0.33..256.02 rows=20 width=17) (actual time=0.531..1.675 rows=12 loops=11)
                 Hash Cond: (test_recursive_1.parent_id = t1_1.id)
                 ->  Seq Scan on test_recursive test_recursive_1  (cost=0.00..218.00 rows=10000 width=17) (actual time=0.029..0.695 rows=10000 loops=11)
                 ->  Hash  (cost=0.20..0.20 rows=10 width=4) (actual time=0.005..0.005 rows=12 loops=11)
                       Buckets: 1024  Batches: 1  Memory Usage: 9kB
                       ->  WorkTable Scan on t1 t1_1  (cost=0.00..0.20 rows=10 width=4) (actual time=0.001..0.002 rows=12 loops=11)
 Planning time: 0.173 ms
 Execution time: 18.651 ms
(13 rows)

There is no difference in planned rows count between corresponding lines of two plans.

I tried to create statistics for this two columns, but query plan remains the same:

create statistics test_recursive_parent on id, parent_id from test_recursive;

I need to join the result of this CTE to another table with millions of record, that has foreign key to test_recursive. So I need postgresql to make correct decision on scan type and join type, and it depends on estimate row count of this query. Here is short example:

create table test_item (
id serial primary key,
tree_item_id int not null references test_recursive,
some_data_column int);
with recursive t1 as (... WHERE id=%s ...);

select t1.id, sum(test_item.some_fact_column) from t1 left join test_item on test_item.tree_item_id = t1.id;

I need to execute this query with different ids, so just

set local enable_seqscan to False;

does not works.

Is there something i can do to make postgresql a bit more precise on planned rows count, than just putting default constant there?

Tested on PostgreSQL 10.6

  • 1
    Maybe using a temp table with the recursive values? – McNets Feb 1 at 11:34
  • @McNets I made a temporary solution by wrapping query inside a function, that select recursive values into array - this is almost the same. But still that does not looks like a great solution. – duschatten Feb 1 at 11:45
  • While it is possible to estimate the number of rows in the first part of the CTE, how can the optimizer possibly know if the 2nd, recursive part is going to result in 0 rows or traverse an entire table? – ypercubeᵀᴹ Feb 1 at 12:23
  • @ypercubeᵀᴹ Thats what the whole question is about. – duschatten Feb 1 at 12:33
  • Wel,, the short answer is "No, it can't". Not without running the CTE first. So, splitting the query into 2 and using an intermediate table to store the results of the CTE (as @McNets proposed) is the only viable solution I think. – ypercubeᵀᴹ Feb 1 at 12:59

Your Answer

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

Browse other questions tagged or ask your own question.