From: anthony.shipman@symstream.com
To: pgsql-performance@postgresql.org
Subject: Re: overzealous sorting?
Date: Tue, 27 Sep 2011 19:05:09 +1000
User-Agent: KMail/1.9.4
References: <201109261628.15363.anthony.shipman@symstream.com>
	<201109271245.00979.anthony.shipman@symstream.com>
	<20110927105435.26d4e9c6@marco-dalibo>
In-Reply-To: <20110927105435.26d4e9c6@marco-dalibo>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201109271905.09573.anthony.shipman@symstream.com>

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=E2=80=A6 if you are sure most of your data i=
s 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=
=20
between two timestamps. The number of records to be read may be in the=20
millions totalling more than 1GB of data so I'm trying to read them a slice=
=20
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=20
timestamp range then PG will grab all of the records in the range, sort the=
m=20
on disk and return just the slice I want. This is absurdly slow.=20

The query that I've shown is one of a sequence of queries with the timestam=
p=20
range progressing in steps of 1 hour through the timestamp range. All I wan=
t=20
PG to do is find the range in the index, find the matching records in the=20
table and return them. All of the planner's cleverness just seems to get in=
=20
the way.

=2D-=20
Anthony Shipman                 | Consider the set of blacklists that
Anthony.Shipman@symstream.com   | do not blacklist themselves...