Thread: Why does index not use for CTE query?

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?

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

This message has been digitally signed by the sender.

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.

Hi,

I believe then, may be some understanding of CTE may help here. Postgres
would try to execute a CTE query independently as if there was no WHERE
clause outside it. This means that if you run ten UNION ALLs as you say,
if they are queries that are probably better off using table scans, an
Index would not be used.

(I am open to correction by any one else here but) Putting a WHERE
clause outside a CTE (which has a ten or hundreds of UNION ALLs as you
mention, inside) would still not make use of the index. Even if the
WHERE condition was a very restrictive query (such as a=2). This is
expected as normal behaviour.

--
Robins Tharakan

On 12/27/2011 02:24 PM, AI Rumman wrote:
> 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.tharakan@comodo.com <mailto:robins.tharakan@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?
>
>
>

This message has been digitally signed by the sender.