X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 4D21DD1B511 for ; Tue, 6 Jan 2004 18:37:38 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 39670-07 for ; Tue, 6 Jan 2004 14:36:50 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id B1E87D1B43A for ; Tue, 6 Jan 2004 14:36:48 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id i06Iaj19022306; Tue, 6 Jan 2004 13:36:45 -0500 (EST) To: Greg Stark Cc: Clive Page , pgsql-performance@postgresql.org Subject: Re: Inefficient SELECT with OFFSET and LIMIT In-reply-to: <878ykl0wvy.fsf@stark.dyndns.tv> References: <878ykl0wvy.fsf@stark.dyndns.tv> Comments: In-reply-to Greg Stark message dated "06 Jan 2004 13:12:17 -0500" Date: Tue, 06 Jan 2004 13:36:44 -0500 Message-ID: <22305.1073414204@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/70 X-Sequence-Number: 5245 Greg Stark writes: > Clive Page writes: >> It would be nice if OFFSET could be implemented in some more efficient >> way. > You could do something like: > select myfunc(mycol) from (select mycol from table limit 50 offset 10000) as x; Note that this won't eliminate the major inefficiency, which is having to read 10000+50 rows from the table. But if myfunc() has side-effects or is very expensive to run, it'd probably be worth doing. > I think it's not easy for the optimizer to do it because there are lots of > cases where it can't. I don't actually know of any cases where it could do much of anything to avoid fetching the OFFSET rows. The problems are basically the same as with COUNT(*) optimization: without examining each row, you don't know if it would have been returned or not. We could possibly postpone evaluation of the SELECT output list until after the OFFSET step (thus automating the above hack), but even that only works if there are no set-returning functions in the output list ... regards, tom lane PS: BTW, the one-extra-row effect that Clive noted is gone in 7.4.