Re: faster search

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
=:->