I know that. I wrote here only a sample. I have to have UNION ALL on the CTE expression for severral times where UNION ALL and a CONCAT SELECT will be changed. That's why I can't include the where condition in the CTE expression.
On Tue, Dec 27, 2011 at 2:50 PM, Robins Tharakan <robins.thara...@comodo.com > wrote: > Hi, > > The CTE is a distinct query and you're trying to do a SELECT * FROM t1. > Which is quite expected to do a table scan. > > If you do a WHERE i=2 *within the CTE*, you should start seeing usage of > the index where you're expecting to. > > -- > Robins Tharakan > > > On 12/27/2011 02:15 PM, AI Rumman wrote: > >> 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) >> >> Index is not using here. >> Could you tell me why? >> >> >
