Re: Why does index not use for CTE query?

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?
>