Re: Why does index not use for CTE query?

Robins Tharakan <robins.tharakan@xxxxxxxxxx> · Tue, 27 Dec 2011 16:02:31 +0530

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@xxxxxxxxxx <mailto:robins.tharakan@xxxxxxxxxx>> 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?

<<attachment: smime.p7s>>