From: Greg Stark <gsstark@mit.edu>
To: dbichko@genpathpharma.com
Cc: pgsql-sql@postgresql.org
Subject: Re: OFFSET and subselects
Date: 29 Nov 2003 01:38:22 -0500
Message-ID: <87ad6ffzmp.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <cf1d5239.0311210838.3865363e@posting.google.com>
References: <cf1d5239.0311210838.3865363e@posting.google.com>
dbichko@genpathpharma.com (Dmitri Bichko) writes:
> I am running in trouble with pagination here, somehow (rather naively) I
> assumed that when doing a LIMIT and OFFSET, the subselects on the records
> before the OFFSET would not be performed, which quite apparently is not the
> case. So, LIMIT 50 OFFSET 0 takes 100ms to run, LIMIT 50 OFFSET 50 takes
> 200ms, LIMIT 50 OFFSET 100 takes 300ms; and so forth, this really becomes
> unacceptable after a few pages.
If you don't need any of the results of the subqueries in your WHERE clause
then you can do this by introducing a view in your query like:
SELECT *,
(SELECT ...) AS sub_1,
(SELECT ...) AS sub_2,
(SELECT ...) AS sub_3
FROM (
SELECT x,y,z
FROM ...
WHERE ...
)
LIMIT 50
OFFSET 50
If you do use the results of the subqueries in your where clause or order by
clause then, well, you're SOL. Since the OFFSET and LIMIT clauses only kick in
after the where clause restrictions are taken into account.
--
greg
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-sql@postgresql.org
Cc: gsstark@mit.edu, dbichko@genpathpharma.com
Subject: Re: OFFSET and subselects
In-Reply-To: <87ad6ffzmp.fsf@stark.dyndns.tv>
* 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