Message-ID: <4E850FC0.509@agliodbs.com>
Date: Thu, 29 Sep 2011 17:39:28 -0700
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US;
	rv:1.9.2.15) Gecko/20110303 Lightning/1.0b2 Thunderbird/3.1.9
MIME-Version: 1.0
To: postgres performance list <pgsql-performance@postgresql.org>
Subject: Shortcutting too-large offsets?
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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