Shortcutting too-large offsets?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux