Re: BUG #15160: planner overestimates number of rows in join whenthere are more than 200 rows coming from CTE

There are similar issue without CTE which look pretty weird:

Good case with LIMIT 199 and adequate estimation:

hh=# explain SELECT * FROM resume WHERE resume_id IN (select id from generate_series(1, 1000) gs(id) LIMIT 199);
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Nested Loop  (cost=21.53..108.98 rows=199 width=519)
   ->  Unique  (cost=21.42..21.62 rows=199 width=4)
         ->  Sort  (cost=21.42..21.52 rows=199 width=4)
               Sort Key: gs.id
               ->  Limit  (cost=0.00..9.95 rows=199 width=4)
                     ->  Function Scan on generate_series gs  (cost=0.00..50.00 rows=1000 width=4)
   ->  Index Scan using resume_pk on resume  (cost=0.11..0.39 rows=1 width=519)
         Index Cond: (resume_id = gs.id)

Very bad case with awful estimation (only difference LIMIT 200 vs LIMIT 199):

explain SELECT * FROM resume WHERE resume_id IN (select id from generate_series(1, 1000) gs(id) LIMIT 200);
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Nested Loop  (cost=21.64..109.53 rows=45860504 width=519)
   ->  Unique  (cost=21.53..21.73 rows=200 width=4)
         ->  Sort  (cost=21.53..21.63 rows=200 width=4)
               Sort Key: gs.id
               ->  Limit  (cost=0.00..10.00 rows=200 width=4)
                     ->  Function Scan on generate_series gs  (cost=0.00..50.00 rows=1000 width=4)
   ->  Index Scan using resume_pk on resume  (cost=0.11..0.39 rows=1 width=519)
         Index Cond: (resume_id = gs.id)

It's not a problem by itself but once you start using this query with more joined tables - a lot bad things happens because 5 orders of magnitude error in selectivity estimation.

PS: in reality it forces us to use not more than 199 LIMIT in complex joins for batch operations or the database start generate funny plans.

Regards,

Maxim