From: | Alexandre de Arruda Paes <adaldeia(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | PG using index+filter instead only use index |
Date: | 2010-03-19 13:45:50 |
Message-ID: | fe86db181003190645q3d93689bx1d76ad6ceea6ccdf@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
PostgreSQL 8.4.2 / default_statistics_target = 300
I have a strange problem for a bad choose of indexes.
client=# \d ct13t
Table "public.ct13t"
Column | Type | Modifiers
------------+--------------+-----------
ct12emp04 | integer | not null
ct03emp01 | integer | not null
ct03tradut | integer | not null
ct07emp01 | integer | not null
ct07c_cust | integer | not null
ct13dtlanc | date | not null
ct12numlot | integer | not null
ct12numlan | integer | not null
ct13emptr1 | integer |
ct13tradu1 | integer |
ct13empcc1 | integer |
ct13ccust1 | integer |
ct13duoc | character(1) |
Indexes:
"ct13t_pkey" PRIMARY KEY, btree (ct12emp04, ct03emp01, ct03tradut,
ct07emp01, ct07c_cust, ct13dtlanc, ct12numlot, ct12numlan) CLUSTER
"ict13t1" btree (ct12emp04, ct12numlot, ct12numlan)
"ict13t2" btree (ct07emp01, ct07c_cust)
"ict13t3" btree (ct13empcc1, ct13ccust1)
"ict13t4" btree (ct03emp01, ct03tradut)
"ict13t5" btree (ct13emptr1, ct13tradu1)
"uct13t" btree (ct12emp04, ct13dtlanc)
client=# explain analyze SELECT ct12emp04, ct03emp01, ct03tradut,
ct07emp01, ct07c_cust, ct13dtlanc, ct12numlot, ct12numlan FROM CT13T
WHERE ct12emp04 = '2' AND ct03emp01 = '2' AND ct03tradut = '60008' AND
ct07emp01 = '2' AND ct07c_cust = '0' AND ct13dtlanc =
'2005-01-28'::date AND ct12numlot = '82050128' AND ct12numlan = '123';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ict13t2 on ct13t (cost=0.00..5.69 rows=1 width=32)
(actual time=288.687..288.687 rows=0 loops=1)
Index Cond: ((ct07emp01 = 2) AND (ct07c_cust = 0))
Filter: ((ct12emp04 = 2) AND (ct03emp01 = 2) AND (ct03tradut =
60008) AND (ct13dtlanc = '2005-01-28'::date) AND (ct12numlot =
82050128) AND (ct12numlan = 123))
Total runtime: 288.735 ms
(4 rows)
client=# create table ad_ct13t as select * from ct13t;
SELECT
client=# alter table ad_ct13t add primary key (ct12emp04, ct03emp01,
ct03tradut, ct07emp01, ct07c_cust, ct13dtlanc, ct12numlot,
ct12numlan);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"ad_ct13t_pkey" for table "ad_ct13t"
ALTER TABLE
client=# explain analyze SELECT ct12emp04, ct03emp01, ct03tradut,
ct07emp01, ct07c_cust, ct13dtlanc, ct12numlot, ct12numlan FROM
AD_CT13T WHERE ct12emp04 = '2' AND ct03emp01 = '2' AND ct03tradut =
'60008' AND ct07emp01 = '2' AND ct07c_cust = '0' AND ct13dtlanc =
'2005-01-28'::date AND ct12numlot = '82050128' AND ct12numlan = '123';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-
Index Scan using ad_ct13t_pkey on ad_ct13t (cost=0.00..5.66 rows=1
width=32) (actual time=0.090..0.090 rows=0 loops=1)
Index Cond: ((ct12emp04 = 2) AND (ct03emp01 = 2) AND (ct03tradut =
60008) AND (ct07emp01 = 2) AND (ct07c_cust = 0) AND (ct13dtlanc =
'2005-01-28'::date) AND (ct12numlot = 82050128) AND (ct12numlan =
123))
Total runtime: 0.146 ms
(3 rows)
My question: if the cost is exactly the same, why PG choose the index
ict13t2 on ct13t and apply a filter instead use the primary key ?
In one query, it's ok. But this routine execute millions times this query.
Thanks for any help,
Alexandre
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2010-03-19 13:51:42 | Re: mysql to postgresql, performance questions |
Previous Message | Corin | 2010-03-19 12:26:35 | too complex query plan for not exists query and multicolumn indexes |