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