From: | AI Rumman <rummandba(at)gmail(dot)com> |
---|---|
To: | pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | Why does index not use for CTE query? |
Date: | 2011-12-27 08:45:07 |
Message-ID: | CAGoODpf3b1hpAJ_HsTKE=UK5sqeOA_9A=k3FdB_qBJ54PjeBwQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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?
From | Date | Subject | |
---|---|---|---|
Next Message | Robins Tharakan | 2011-12-27 08:50:12 | Re: Why does index not use for CTE query? |
Previous Message | John R Pierce | 2011-12-27 05:42:29 | Re: Error while loading sql file |