From: Marc Cousin <cousinmarc@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: overzealous sorting?
Date: Tue, 27 Sep 2011 17:00:04 +0200
Message-ID: <20110927170004.784d7814@marco-dalibo> (raw)
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>
Le Tue, 27 Sep 2011 19:05:09 +1000,
anthony.shipman@symstream.com a écrit :
> 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.
>
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).
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: cousinmarc@gmail.com
Subject: Re: overzealous sorting?
In-Reply-To: <20110927170004.784d7814@marco-dalibo>
* 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