Re: Using Between

The question is how can we make it faster.

-----Original Message-----
From: Robert Haas [mailto:robertmhaas(at)gmail(dot)com]
Sent: Wednesday, September 22, 2010 3:52 AM
To: Ozer, Pam
Cc: Craig James; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Using Between

On Tue, Sep 21, 2010 at 4:04 PM, Ozer, Pam <pozer(at)automotive(dot)com> wrote:
> There are 850,000 records in vehicleused.  And the database is too big to be kept in memory.

Ah. So in other words, you are retrieving about half the rows in that
table. For those kinds of queries, using the index tends to actually
be slower, because (1) you read the index in addition to reading the
table, which has CPU and I/O cost, and (2) instead of reading the
table sequentially, you end up jumping around and reading it out of
order, which tends to result in more disk seeks and defeats the OS
prefetch logic. The query planner is usually pretty smart about
making good decisions about this kind of thing. As a debugging aid
(but never in production), you can try disabling enable_seqscan and
see what plan you get that way. If it's slower, well then the query
planner did the right thing. If it's faster, then probably you need
to adjust seq_page_cost and random_page_cost a bit. But my guess is
that it will be somewhere between a lot slower and only very slightly
faster.

A whole different line of inquiry is ask the more general question
"how can I make this query faster?", but I'm not sure whether you're
unhappy with how the query is running or just curious about why the
index isn't being used.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company