Re: Shortcutting too-large offsets?

It may be difficult, i think. When unsorted recordset is stored in
temp table, number of records may be saved and used. Otherwise it is
unknown.

2011/9/30, Josh Berkus <josh(at)agliodbs(dot)com>:
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

--
------------
pasman