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
Date: Wed, 24 Aug 2005 16:22:34 -0500
Message-ID: <20050824212234.GZ96732@pervasive.com> (raw)
In-Reply-To: <bca49a9f05082413435f737033@mail.gmail.com>
References: <bca49a9f05082413435f737033@mail.gmail.com>
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
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: jnasby@pervasive.com, mark.fox@gmail.com
Subject: Re: Performance indexing of a simple query
In-Reply-To: <20050824212234.GZ96732@pervasive.com>
* 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