Re: overzealous sorting?

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(dot)Shipman(at)symstream(dot)com | down.