From: | Anj Adu <fotographs(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | slow index lookup |
Date: | 2010-06-22 21:44:39 |
Message-ID: | AANLkTimoNh8cPlwR5oYE9jdmr4aR3ThAxpnAsmnujIYK@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
This query seems unreasonable slow on a well-indexed table (13 million
rows). Separate indexes are present on guardid_id , from_num and
targetprt columns.
The table was analyzed with a default stats target of 600.
Postgres 8.1.9 on 2 cpu quad core 5430 with 32G RAM (work_mem=502400)
6 x 450G 15K disks on a RAID 10 setup. (RHEL 5 )
The table size is 3.6GB (table + indexes)
explain analyze select 1 from mydev_tr_hr_dimension_2010_06_13 where
guardid_id=19 and from_num=184091764 and targetprt=25 limit 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..323.36 rows=1 width=0) (actual
time=19238.104..19238.104 rows=0 loops=1)
-> Index Scan using mydev_tr_hr_dimension_2010_06_13_from_num on
mydev_tr_hr_dimension_2010_06_13 (cost=0.00..26515.46 rows=82
width=0) (actual time=19238.103..19238.103 rows=0 loops=1)
Index Cond: (from_num = 184091764)
Filter: ((guardid_id = 19) AND (targetprt = 25))
Total runtime: 19238.126 ms
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2010-06-22 23:44:15 | Re: slow index lookup |
Previous Message | Scott Carey | 2010-06-22 17:30:35 | ALTER Table and CLUSTER does adding a new column rewrite clustered? (8.4.3) |