Re: BUG #2658: Query not using index

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Graham Davis <gdavis(at)refractions(dot)net>
Cc: Chris Browne <cbbrowne(at)acm(dot)org>, pgsql-performance(at)postgresql(dot)org
Subject: Re: BUG #2658: Query not using index
Date: 2006-10-03 20:48:11
Message-ID: 20061003204811.GA29756@wolff.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-performance

On Tue, Oct 03, 2006 at 12:13:43 -0700,
Graham Davis <gdavis(at)refractions(dot)net> wrote:
> Also, the multikey index of (assetid, ts) would already be sorted and
> that is why using such an index in this case is
> faster than doing a sequential scan that does the sorting afterwards.

That isn't necessarily true. The sequentional scan and sort will need a lot
fewer disk seeks and could run faster than using an index scan that has
the disk drives doing seeks for every tuple (in the worst case, where
the on disk order of tuples doesn't match the order in the index).

If your server is caching most of the blocks than the index scan might
give better results. You might try disabling sequentional scans to
try to coerce the other plan and see what results you get. If it is
substantially faster the other way, then you might want to look at lowering
the random page cost factor. However, since this can affect other queries
you need to be careful that you don't speed up one query at the expense
of a lot of other queries.

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Graham Davis 2006-10-03 20:52:28 Re: BUG #2658: Query not using index
Previous Message Tom Lane 2006-10-03 20:48:09 Re: BUG #2658: Query not using index

Browse pgsql-performance by date

  From Date Subject
Next Message Graham Davis 2006-10-03 20:52:28 Re: BUG #2658: Query not using index
Previous Message Tom Lane 2006-10-03 20:48:09 Re: BUG #2658: Query not using index