From: anthony.shipman@symstream.com
To: pgsql-performance@postgresql.org
Subject: Re: overzealous sorting?
Date: Tue, 27 Sep 2011 19:05:09 +1000
Message-ID: <201109271905.09573.anthony.shipman@symstream.com> (raw)
In-Reply-To: <20110927105435.26d4e9c6@marco-dalibo>
References: <201109261628.15363.anthony.shipman@symstream.com>
<201109271245.00979.anthony.shipman@symstream.com>
<20110927105435.26d4e9c6@marco-dalibo>
On Tuesday 27 September 2011 18:54, Marc Cousin wrote:
> The thing is, the optimizer doesn't know if your data will be in cache
> when you will run your query… if you are sure most of your data is in
> the cache most of the time, you could try to tune random_page_cost
> (lower it) to reflect that data is cached. But if the win is small on
> this query, it may not be worth it.
What I really want is to just read a sequence of records in timestamp order
between two timestamps. The number of records to be read may be in the
millions totalling more than 1GB of data so I'm trying to read them a slice
at a time but I can't get PG to do just this.
If I use offset and limit to grab a slice of the records from a large
timestamp range then PG will grab all of the records in the range, sort them
on disk and return just the slice I want. This is absurdly slow.
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.
--
Anthony Shipman | Consider the set of blacklists that
Anthony.Shipman@symstream.com | do not blacklist themselves...
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: <201109271905.09573.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