Hi-
Would someone please enlighten me as
to why I'm not seeing a faster execution
time on the simple scenario below?
there are 412,485 rows in the table and the
query matches on 132,528 rows, taking
almost a minute to execute. vaccuum
analyze was just run.
Thanks!
Clark
test
-------------------------
id | integer
partnumber | character varying(32)
productlistid | integer
typeid | integer
Indexes:
"test_id" btree (id)
"test_plid" btree (productlistid)
"test_typeid" btree (typeid)
"test_plidtypeid" btree (productlistid, typeid)
explain analyze select * from test where productlistid=3 and typeid=9
order by partnumber limit 15;
QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=201073.76..201073.79 rows=15 width=722) (actual
time=58092.477..58092.518 rows=15 loops=1)
-> Sort (cost=201073.76..201451.76 rows=151200 width=722) (actual
time=58092.470..58092.505 rows=15 loops=1)
Sort Key: partnumber
-> Seq Scan on test (cost=0.00..96458.27 rows=151200 width=722)
(actual time=2.515..40201.275 rows=132528 loops=1)
Filter: ((productlistid = 3) AND (typeid = 9))
Total runtime: 59664.765 ms
(6 rows)
System specs:
PostgreSQL 7.4.2 on RedHat 9
dual AMD Athlon 2GHz processors
1 gig memory
mirrored 7200 RPM IDE disks
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
