Date: Tue, 3 Oct 2006 15:48:11 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Graham Davis <gdavis@refractions.net>
Cc: Chris Browne <cbbrowne@acm.org>, pgsql-performance@postgresql.org
Subject: Re: BUG #2658: Query not using index
Message-ID: <20061003204811.GA29756@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Graham Davis <gdavis@refractions.net>,
	Chris Browne <cbbrowne@acm.org>, pgsql-performance@postgresql.org
References: <200609272056.k8RKuWjY040283@wwwmaster.postgresql.org>
	<20061003000147.GA31475@wolff.to>
	<4522AA01.4020709@refractions.net>
	<60irj1gr3r.fsf@dba2.int.libertyrms.com>
	<4522B667.3060002@refractions.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4522B667.3060002@refractions.net>
User-Agent: Mutt/1.4.2.1i

On Tue, Oct 03, 2006 at 12:13:43 -0700,
  Graham Davis <gdavis@refractions.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.