Date: Mon, 5 Aug 2013 18:54:28 -0700 (PDT)
From: David Johnston <polobo@yahoo.com>
To: pgsql-performance@postgresql.org
Message-ID: <1375754068993-5766429.post@n5.nabble.com>
In-Reply-To: 
 <CAL_0b1tgDjqk3zCiffzgdYmLaSNtg6zq7+81PQu1pCcDoE2Ejw@mail.gmail.com>
References: 
 <CAF-QHFW2S7XHMFY-nCSbBRsFQFC--3NyVqOcW7SMeNAs28NDcQ@mail.gmail.com>
 <52004FC8.3070102@agliodbs.com>
 <CAL_0b1tgDjqk3zCiffzgdYmLaSNtg6zq7+81PQu1pCcDoE2Ejw@mail.gmail.com>
Subject: Re: ORDER BY, LIMIT and indexes
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Sergey Konoplev-2 wrote
> As an alternative solution for pagination (OFFSET) problem you might
> also use the "prev/next" technique, like
> 
> SELECT * FROM table
> WHERE id > :current_last_id
> ORDER BY id LIMIT 10
> 
> for "next", and
> 
> SELECT * FROM (
>     SELECT * FROM table
>     WHERE id < :current_first_id
>     ORDER BY id DESC
>     LIMIT 10
> ) AS sq ORDER BY id
> 
> for "prev". It will be very fast.

Even being fairly experienced at SQL generally because I haven't explored
pagination that much my awareness of the OFFSET issue led me to conclude bad
things.  Thank you for thinking to take the time for a brief moment of
enlightenment of something you likely take for granted by now.

Curious how much slower/faster these queries would run if you added:

SELECT *, first_value(id) OVER (...), last_value(id) OVER (...)  
--note the window specifications need to overcome the "ORDER BY" limitation
noted in the documentation.

to the query.  Using the window functions you know at each record what the
first and last ids are for its window.  Applicability would be
application/need specific but it would avoid having to calculate/maintain
these two values in a separate part of the application.

David J.


--
View this message in context: http://postgresql.1045698.n5.nabble.com/ORDER-BY-LIMIT-and-indexes-tp5766413p5766429.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


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