pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Michael Fuhr <mike@fuhr.org>
To: Carlos Benkendorf <carlosbenkendorf@yahoo.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: Improving performance of a query
Date: Sat, 3 Sep 2005 16:11:00 -0600
Message-ID: <20050903221100.GA2985@winnie.fuhr.org> (raw)
In-Reply-To: <20050903210227.75394.qmail@web35508.mail.mud.yahoo.com>
References: <20050903210227.75394.qmail@web35508.mail.mud.yahoo.com>

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



view thread (5+ messages)  latest in thread

Message-ID: <20050903221100.GA2985@winnie.fuhr.org>
Permalink:  ../20050903221100.GA2985@winnie.fuhr.org/
Also on:    postgresql.org/message-id/20050903221100.GA2985@winnie.fuhr.org

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mike@fuhr.org, carlosbenkendorf@yahoo.com.br
  Subject: Re: Improving performance of a query
  In-Reply-To: <20050903221100.GA2985@winnie.fuhr.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox