From: anthony.shipman@symstream.com
To: pgsql-performance@postgresql.org
Subject: Re: overzealous sorting?
Date: Wed, 28 Sep 2011 15:13:06 +1000
Message-ID: <201109281513.06770.anthony.shipman@symstream.com> (raw)
In-Reply-To: <4E8195D3.8010809@catalyst.net.nz>
References: <201109261628.15363.anthony.shipman@symstream.com>
<201109271905.09573.anthony.shipman@symstream.com>
<4E8195D3.8010809@catalyst.net.nz>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: anthony.shipman@symstream.com
Subject: Re: overzealous sorting?
In-Reply-To: <201109281513.06770.anthony.shipman@symstream.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox