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 73097D1B44B for ; Tue, 6 Jan 2004 18:13:22 +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 37697-03 for ; Tue, 6 Jan 2004 14:12:34 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 03EE9D1B48A for ; Tue, 6 Jan 2004 14:12:33 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id B793436BFD; Tue, 6 Jan 2004 13:12:17 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 1Advgf-0000CU-00; Tue, 06 Jan 2004 13:12:17 -0500 To: Clive Page Cc: pgsql-performance@postgresql.org Subject: Re: Inefficient SELECT with OFFSET and LIMIT References: In-Reply-To: From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 06 Jan 2004 13:12:17 -0500 Message-ID: <878ykl0wvy.fsf@stark.dyndns.tv> Lines: 21 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/69 X-Sequence-Number: 5244 Clive Page writes: > SELECT myfunc(mycol) FROM table LIMIT 50 OFFSET 10000 ; > It looks as if OFFSET is implemented just be throwing away the results, > until the OFFSET has been reached. > > 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; I think it's not easy for the optimizer to do it because there are lots of cases where it can't. Consider if you had an ORDER BY clause on the myfunc output column for example. Or if myfunc was a set-returning function. -- greg