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.