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 [EMAIL PROTECTED] Pervasive Software http://pervasive.com 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
