Date: Wed, 24 Aug 2005 16:22:34 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Mark Fox <mark.fox@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance indexing of a simple query
Message-ID: <20050824212234.GZ96732@pervasive.com>
References: <bca49a9f05082413435f737033@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <bca49a9f05082413435f737033@mail.gmail.com>
User-Agent: Mutt/1.5.9i

Try

CREATE INDEX start_complete ON jobs( start_time, completion_time );

Try also completion_time, start_time. One might work better than the
other. Or, depending on your data, you might want to keep both.

In 8.1 you'll be able to do bitmap-based index combination, which might
allow making use of the seperate indexes.

On Wed, Aug 24, 2005 at 02:43:51PM -0600, Mark Fox wrote:
> I have a table called 'jobs' with several million rows, and the only
> columns that are important to this discussion are 'start_time' and
> 'completion_time'.
> 
> The sort of queries I want to execute (among others) are like:
> 
> SELECT * FROM jobs
> WHERE completion_time > SOMEDATE AND start_time < SOMEDATE;
> 
> In plain english:  All the jobs that were running at SOMEDATE.  The
> result of the query is on the order of 500 rows.
> 
> I've got seperate indexes on 'start_time' and 'completion_time'.
> 
> Now, if SOMEDATE is such that the number of rows with completion_time
> > SOMEDATE is small (say 10s of thousands), the query uses index scans
> and executes quickly.  If not, the query uses sequential scans and is
> unacceptably slow (a couple of minutes).  I've used EXPLAIN and
> EXPLAIN ANALYZE to confirm this.  This makes perfect sense to me.
> 
> I've played with some of the memory settings for PostgreSQL, but none
> has had a significant impact.
> 
> Any ideas on how to structure the query or add/change indexes in such
> a way to improve its performance?  In desperation, I tried using a
> subquery, but unsurprisingly it made no (positive) difference.  I feel
> like there might be a way of using an index on both 'completion_time'
> and 'start_time', but can't put a temporal lobe on the details.
> 
> 
> Mark
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461