Re: faster search

[Clark Slater - Fri at 01:45:05PM -0400]
> Would someone please enlighten me as
> to why I'm not seeing a faster execution
> time on the simple scenario below?

Just some thoughts from a novice PG-DBA .. :-)

My general experience is that PG usually prefers sequal scans to indices if
a large portion of the table is to be selected, because it is faster to do a
seqscan than to follow an index and constantly seek between different
positions on the hard disk.

However, most of the time is spent sorting on partnumber, and you only want
15 rows, so of course you should have an index on partnumber! Picking up 15
rows will be ligtning fast with that index.

If you may want to select significantly more than 15 rows, you can also try
to make a partial index:

create index test_pli3_ti9_by_part on test (partnumber) where
productlistid=3 and typeid=9;

If 3 and 9 are not constants in the query, try to make a three-key index
(it's important with partnumber because a lot of time is spent sorting):

create index test_pli_type_part on test (productslistid,typeid,partnumber);

To get pg to recognize the index, you will probably have to help it a bit:

select * from test where productlistid=3 and typeid=9 order by
productlistid,typeid,partnumber limit 15;

--
Tobias Brox, +47-91700050