Re: faster search

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)

System specs:
PostgreSQL 7.4.2 on RedHat 9
dual AMD Athlon 2GHz processors
1 gig memory
mirrored 7200 RPM IDE disks

On Fri, 10 Jun 2005, John A Meinel wrote:

> Clark Slater wrote:
>> 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.
>
> Well, if you are matching 130k out of 400k rows, then a sequential scan
> is certainly prefered to an index scan. And then you have to sort those
> 130k rows by partnumber. This *might* be spilling to disk depending on
> what your workmem/sortmem is set to.
>
> I would also say that what you would really want is some way to get the
> whole thing from an index. And I think the way to do that is:
>
> CREATE INDEX test_partnum_listid_typeid_idx ON
> test(partnumber, productlistid, typeid);
>
> VACUUM ANALYZE test;
>
> EXPLAIN ANALYZE SELECT * FROM test
> WHERE productlistid=3 AND typeid=9
> ORDER BY partnumber, productlistid, typeid
> LIMIT 15
> ;
>
> The trick is that you have to match the order by exactly with the index,
> so the planner realizes it can do an indexed lookup to get the information.
>
> You could also just create an index on partnumber, and see how that
> affects your original query. I think the planner could use an index
> lookup on partnumber to get the ordering correct. But it will have to do
> filtering after the fact based on productlistid and typeid.
> With my extended index, I think the planner can be smarter and lookup
> all 3 by the index.
>
>>
>> Thanks!
>> Clark
>
> Good luck,
> John
> =:->
>