Why does index not use for CTE query?

Поиск
Список
Период
Сортировка
От AI Rumman
Тема Why does index not use for CTE query?
Дата
Msg-id CAGoODpf3b1hpAJ_HsTKE=UK5sqeOA_9A=k3FdB_qBJ54PjeBwQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: Why does index not use for CTE query?
Список pgsql-general
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?

В списке pgsql-general по дате отправления:

Предыдущее
От: John R Pierce
Дата:
Сообщение: Re: Error while loading sql file
Следующее
От: Robins Tharakan
Дата:
Сообщение: Re: Why does index not use for CTE query?