My assumption was that WITH acted just like subselects, but apparently they don't? Using WITH doesn't use the expected index.
(the below also at:
create view promotion_details1 as (
select * from (select code from promotions)_
);
create view promotion_details2 as (
with info as (select code from promotions) select * from info
);
explain analyze
select * from promotion_details1 where code = 'slickdeals';
explain analyze
select * from promotion_details2 where code = 'slickdeals';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on promotions (cost=72.54..6435.31 rows=3014 width=32) (actual time=0.122..0.196 rows=113 loops=1)
Recheck Cond: (code = 'slickdeals'::citext)
-> Bitmap Index Scan on promotions_code_idx (cost=0.00..71.79 rows=3014 width=0) (actual time=0.111..0.111 rows=113 loops=1)
Index Cond: (code = 'slickdeals'::citext)
Total runtime: 0.236 ms
(5 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
CTE Scan on info (cost=15539.25..29102.81 rows=3014 width=32) (actual time=184.303..661.816 rows=113 loops=1)
Filter: (code = 'slickdeals'::citext)
Rows Removed by Filter: 602712
CTE info
-> Seq Scan on promotions (cost=0.00..15539.25 rows=602825 width=32) (actual time=0.018..145.272 rows=602825 loops=1)
Total runtime: 697.495 ms
(6 rows)