From: Josh Berkus <josh@agliodbs.com>
To: postgres performance list <pgsql-performance@postgresql.org>
Subject: Shortcutting too-large offsets?
Date: Thu, 29 Sep 2011 17:39:28 -0700
Message-ID: <4E850FC0.509@agliodbs.com> (raw)
All,
Here's a case which it seems like we ought to be able to optimize for:
datamart-# ORDER BY txn_timestamp DESC
datamart-# LIMIT 200
datamart-# OFFSET 6000;
QUERY PLAN
---------------------------
Limit (cost=560529.82..560529.82 rows=1 width=145) (actual
time=22419.760..22419.760 rows=0 loops=1)
-> Sort (cost=560516.17..560529.82 rows=5459 width=145) (actual
time=22418.076..22419.144 rows=5828 loops=1)
Sort Key: lh.txn_timestamp
Sort Method: quicksort Memory: 1744kB
-> Nested Loop Left Join (cost=0.00..560177.32 rows=5459
width=145) (actual time=4216.898..22398.658 rows=5828 loops=1)
-> Nested Loop Left Join (cost=0.00..88186.22 rows=5459
width=135) (actual time=4216.747..19250.891 rows=5828 loops=1)
-> Nested Loop Left Join (cost=0.00..86657.26
rows=5459 width=124) (actual time=4216.723..19206.461 rows=5828 loops=1)
... it seems like, if we get as far as the sort and the executors knows
that there are less rows than the final offset, it ought to be able to
skip the final sort.
Is there some non-obvious reason which would make this kind of
optimization difficult? Doesn't the executor know at that point how
many rows it has?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
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: josh@agliodbs.com
Subject: Re: Shortcutting too-large offsets?
In-Reply-To: <4E850FC0.509@agliodbs.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