Clark Slater wrote: > hmm, i'm baffled. i simplified the query > and it is still taking forever... > > > test > ------------------------- > id | integer > partnumber | character varying(32) > productlistid | integer > typeid | integer > > > Indexes: > "test_productlistid" btree (productlistid) > "test_typeid" btree (typeid) > "test_productlistid_typeid" btree (productlistid, typeid) > > > explain analyze select * from test where (productlistid=3 and typeid=9); > > QUERY PLAN > ----------------------------------------------------------------------- > Seq Scan on test (cost=0.00..96458.27 rows=156194 width=725) (actual > time=516.459..41930.250 rows=132528 loops=1) > Filter: ((productlistid = 3) AND (typeid = 9)) > Total runtime: 41975.154 ms > (3 rows) > >
This query is still going to take a long time, because you have to scan the whole table. Your WHERE clause is not very specific (it takes 25% of the table). Convention says that any time you want > 5-10% of a table, a sequential scan is better, because it does it in order. Now if you did: explain analyze select * from test where (productlistid=3 and typeid=9) limit 15; I think that would be very fast. I am a little surprised that it is taking 40s to scan only 400k rows, though. On an older machine of mine (with only 256M ram and dual 450MHz Celerons), I have a table with 74k rows which takes about .5 sec. At those numbers it should take more like 4s not 40. John =:->
signature.asc
Description: OpenPGP digital signature