slow index lookup

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

Responses

Browse pgsql-performance by date

  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)