Date: Tue, 27 Sep 2011 17:00:04 +0200
From: Marc Cousin <cousinmarc@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: overzealous sorting?
Message-ID: <20110927170004.784d7814@marco-dalibo>
In-Reply-To: <201109271905.09573.anthony.shipman@symstream.com>
References: <201109261628.15363.anthony.shipman@symstream.com>
	<201109271245.00979.anthony.shipman@symstream.com>
	<20110927105435.26d4e9c6@marco-dalibo>
	<201109271905.09573.anthony.shipman@symstream.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Le Tue, 27 Sep 2011 19:05:09 +1000,
anthony.shipman@symstream.com a =C3=A9crit :

> 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 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.
>=20
> 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.
>=20
> 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 them on disk and return just the slice I want. This is absurdly
> slow.=20
>=20
> 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.
>=20

Maybe you should try using a cursor, if you don't know where you'll
stop. This associated with a very low cursor_tuple_fraction will
probably give you what you want (a fast start plan).