pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOFFSET and subselects
2+ messages / 2 participants
[nested] [flat]
* OFFSET and subselects
@ 2003-11-21 16:38 Dmitri Bichko <dbichko@genpathpharma.com>
2003-11-29 06:38 ` Re: OFFSET and subselects Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 2+ messages in thread
From: dbichko@genpathpharma.com @ 2003-11-21 16:38 UTC (permalink / raw)
To: pgsql-sql
I recently noticed a behaviour which seems quite suboptimal - I am
working on a "mart" type application, which in practice means I end up
with queries which have a few filters on several central tables, and
then a few dozen subselects for other info (which seems to perform
better than several dozen equivalent LEFT JOINs do). 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.
I was wondering how I migh improve the situation? Two possibilities
come to mind: a) run the query without the subselects and store the
LIMIT 50 in a temp table, doing a select all from that with the
subselects as a separate query or b) similar, but run the queries from
the subselects as separate queries, assembling everything in the app.
Is one better than the other in any way? Are there better ways to deal
with this?
Thanks
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: OFFSET and subselects
2003-11-21 16:38 OFFSET and subselects Dmitri Bichko <dbichko@genpathpharma.com>
@ 2003-11-29 06:38 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 2+ messages in thread
From: Greg Stark @ 2003-11-29 06:38 UTC (permalink / raw)
To: dbichko@genpathpharma.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-11-29 06:38 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-21 16:38 OFFSET and subselects Dmitri Bichko <dbichko@genpathpharma.com>
2003-11-29 06:38 ` Greg Stark <gsstark@mit.edu>
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