Lists: | pgsql-general |
---|
From: | Damien <dm_mailings(at)abelia-decors(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | speed w/ OFFSET/LIMIT |
Date: | 2003-05-27 13:39:49 |
Message-ID: | 200305271539.50039.dm_mailings@abelia-decors.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hi !
I'm running a pretty simple select query on a pretty large table (70000
records). This table has some "flag" fields, each one textually explained by
another table ( flag VARCHAR(2), flag_details VARCHAR(60))
SELECT t.* , t1.flag1_details , ... , tn.flagn_details
FROM table t
NATURAL JOIN t1
NATURAL JOIN ...
NATURAL JOIN tn
ORDER BY main_field OFFSET x LIMIT 50
There is no where statement, this query is for display purposes on a web page.
My problem is, where OFFSET is low, execution takes only a few milli-seconds,
but where OFFSET is high (50 last results for example), execution can take 10
seconds...
Is there anything I can do to speed up such a query ?
From: | "Jim C(dot) Nasby" <jim(at)nasby(dot)net> |
---|---|
To: | Damien <dm_mailings(at)abelia-decors(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: speed w/ OFFSET/LIMIT |
Date: | 2003-06-02 19:07:05 |
Message-ID: | 20030602190705.GX40542@flake.decibel.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Try using subselects instead. PGSQL has to do all the joins unless you
do a LEFT JOIN so that it knows what rows should be included. It would
be nice if the optimizer could recognize if you're jointing to a table
that has RI, since it could safely transform to a subselect then, but
I'm pretty sure it doesn't do this.
On Tue, May 27, 2003 at 03:39:49PM +0200, Damien wrote:
> Hi !
>
> I'm running a pretty simple select query on a pretty large table (70000
> records). This table has some "flag" fields, each one textually explained by
> another table ( flag VARCHAR(2), flag_details VARCHAR(60))
>
> SELECT t.* , t1.flag1_details , ... , tn.flagn_details
> FROM table t
> NATURAL JOIN t1
> NATURAL JOIN ...
> NATURAL JOIN tn
> ORDER BY main_field OFFSET x LIMIT 50
>
> There is no where statement, this query is for display purposes on a web page.
> My problem is, where OFFSET is low, execution takes only a few milli-seconds,
> but where OFFSET is high (50 last results for example), execution can take 10
> seconds...
> Is there anything I can do to speed up such a query ?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
--
Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
From: | Jan Wieck <JanWieck(at)Yahoo(dot)com> |
---|---|
To: | Damien <dm_mailings(at)abelia-decors(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: speed w/ OFFSET/LIMIT |
Date: | 2003-06-04 19:43:43 |
Message-ID: | 3EDE4BEF.5070408@Yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Damien wrote:
> Hi !
>
> I'm running a pretty simple select query on a pretty large table (70000
> records). This table has some "flag" fields, each one textually explained by
> another table ( flag VARCHAR(2), flag_details VARCHAR(60))
>
> SELECT t.* , t1.flag1_details , ... , tn.flagn_details
> FROM table t
> NATURAL JOIN t1
> NATURAL JOIN ...
> NATURAL JOIN tn
> ORDER BY main_field OFFSET x LIMIT 50
>
> There is no where statement, this query is for display purposes on a web page.
> My problem is, where OFFSET is low, execution takes only a few milli-seconds,
> but where OFFSET is high (50 last results for example), execution can take 10
> seconds...
This is because of the way OFFSET/LIMIT works internally. It basically
run's your query, throwing away (but fully creating) the first OFFSET
result rows, and aborting the query when reaching LIMIT+1 result rows
after that.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #