The question is how can we make it faster. -----Original Message----- From: Robert Haas [mailto:robertmhaas@xxxxxxxxx] Sent: Wednesday, September 22, 2010 3:52 AM To: Ozer, Pam Cc: Craig James; pgsql-performance@xxxxxxxxxxxxxx Subject: Re: Using Between On Tue, Sep 21, 2010 at 4:04 PM, Ozer, Pam <pozer@xxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
