Re: Why does index not use for CTE query?

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(dot)tharakan(at)comodo(dot)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?
>>
>>
>