Why does index not use for CTE query? I am using Postgresql 9.1
select version(); version -------------------------------------------------------------------------------------------------------- PostgreSQL 9.1.1 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-50), 32-bit (1 row)
\d t1 Table "public.t1" Column | Type | Modifiers --------+---------+----------- i | integer | nam | text | Indexes: "t1_i_idx" btree (i)
analyze t1; explain select * from t1 where i=2; QUERY PLAN -------------------------------------------------- Seq Scan on t1 (cost=0.00..1.09 rows=4 width=9) Filter: (i = 2) (2 rows)
set enable_seqscan = off;
explain select * from t1 where i=2; QUERY PLAN -------------------------------------------------------------------- Index Scan using t1_i_idx on t1 (cost=0.00..12.32 rows=4 width=9) Index Cond: (i = 2) (2 rows)
explain with q as (select * from t1) select * from q where i=2;
select * from q where i=2; QUERY PLAN ------------------------------------------------------------------------------ CTE Scan on q (cost=10000000001.07..10000000001.23 rows=1 width=36) Filter: (i = 2) CTE q -> Seq Scan on t1 (cost=10000000000.00..10000000001.07 rows=7 width=9) (4 rows)