Thread: speed w/ OFFSET/LIMIT
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 ?
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@postgresql.org > -- Jim C. Nasby (aka Decibel!) jim@nasby.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?"
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@Yahoo.com #