From: anthony.shipman@symstream.com
To: pgsql-performance@postgresql.org
Subject: Re: overzealous sorting?
Date: Wed, 28 Sep 2011 15:13:06 +1000
User-Agent: KMail/1.9.4
References: <201109261628.15363.anthony.shipman@symstream.com>
	<201109271905.09573.anthony.shipman@symstream.com>
	<4E8195D3.8010809@catalyst.net.nz>
In-Reply-To: <4E8195D3.8010809@catalyst.net.nz>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201109281513.06770.anthony.shipman@symstream.com>

On Tuesday 27 September 2011 19:22, Mark Kirkwood wrote:
> > The query that I've shown is one of a sequence of queries with the
> > timestamp range progressing in steps of 1 hour through the timestamp
> > range. All I want PG to do is find the range in the index, find the
> > matching records in the table and return them. All of the planner's
> > cleverness just seems to get in the way.
>
> It is not immediately clear that the planner is making the wrong choices
> here. Index scans are not always the best choice, it depends heavily on
> the correlation of the column concerned to the physical order of the
> table's heap file. I suspect the reason for the planner choosing the
> bitmap scan is that said correlation is low (consult pg_stats to see).
> Now if you think that the table's heap data is cached anyway, then this
> is not such an issue - but you have to tell the planner that by reducing
> random_page_cost (as advised previously). Give it a try and report back!
>
> regards
>
> Mark

I don't expect that any of it is cached. It is supposed to be a once-a-day 
linear scan of a slice of the table. The correlation on the timestamp is 
reported as 0.0348395. I can't use cluster since it would lock the table for 
too long.

I would try a cursor but my framework for this case doesn't support cursors. 
In a later version of the framework I've tried cursors and haven't found them 
to be faster than reading in slices, in the tests I've done.

Anyway at the moment it is fast enough. 

Thanks
-- 
Anthony Shipman                 | It's caches all the way 
Anthony.Shipman@symstream.com   | down.