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.


Attachment: signature.asc
Description: OpenPGP digital signature

Reply via email to