From: David Johnston <polobo@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: ORDER BY, LIMIT and indexes
Date: Mon, 5 Aug 2013 18:54:28 -0700 (PDT)
Message-ID: <1375754068993-5766429.post@n5.nabble.com> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: polobo@yahoo.com
Subject: Re: ORDER BY, LIMIT and indexes
In-Reply-To: <1375754068993-5766429.post@n5.nabble.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