Re: Improving performance of a query

On Sat, Sep 03, 2005 at 09:02:27PM +0000, Carlos Benkendorf wrote:
> Is there a way to improve the performance of the following query?
>
> SELECT * FROM SSIRRA where
> (YEAR = 2004 and CUSTOMER = 0000000004 and CODE = 00 and PART >= 00) or
> (YEAR = 2004 and CUSTOMER = 0000000004 and CODE > 00) or
> (YEAR = 2004 and CUSTOMER > 0000000004) or
> (YEAR > 2004)

Could you post the EXPLAIN ANALYZE output of the query?  It might
also be useful to see the EXPLAIN ANALYZE output for each of those
WHERE conditions individually.  Also, what are the table and index
definitions?  How many rows are in the table?  What version of
PostgreSQL are you using?  Do you vacuum and analyze regularly?

In simple tests in 8.0.3 with random data -- which almost certainly
has a different distribution than yours -- I see about a 10%
improvement with a multi-column index on (year, customer, code,
part) over using single-column indexes on each of those columns.
Various multi-column indexes on two or three of the columns gave
worse performance than single-column indexes.  Your results will
probably vary, however.

In my tests, 8.1beta1 with its bitmap scans was about 20-25% faster
than 8.0.3 with single-column indexes and about 35% faster with a
four-column index.  8.1beta1's use of a four-column index was about
45% faster than 8.0.3's use of single-column indexes.  Don't trust
these numbers too much, though -- I simply inserted 20,000 random
records into a table and ran the above query.

--
Michael Fuhr