X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A8651D1B4E8 for ; Thu, 30 Oct 2003 04:08:32 +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 67283-07 for ; Thu, 30 Oct 2003 00:08:01 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id A11E0D1B500 for ; Thu, 30 Oct 2003 00:08:00 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id C5EB036B2F; Wed, 29 Oct 2003 23:07:59 -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 1AF46J-0001sl-00; Wed, 29 Oct 2003 23:07:59 -0500 To: Rob Nagler Cc: pgsql-performance@postgresql.org Subject: Re: vacuum locking References: <16272.318.870000.93584@gargle.gargle.HOWL> <16279.4988.496000.539525@gargle.gargle.HOWL> <200310230814.56738.mweilguni@sime.com> <29123.1066915615@sss.pgh.pa.us> <16279.61590.158000.290397@gargle.gargle.HOWL> <87vfqeveab.fsf@stark.dyndns.tv> <16281.45354.209000.280418@gargle.gargle.HOWL> <878ynaupgu.fsf@stark.dyndns.tv> <16285.17811.994000.543635@gargle.gargle.HOWL> <87d6ciy3bf.fsf@stark.dyndns.tv> <16288.19970.172547.319577@jump.bivio.com> In-Reply-To: <16288.19970.172547.319577@jump.bivio.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 29 Oct 2003 23:07:59 -0500 Message-ID: <87d6cffjvk.fsf@stark.dyndns.tv> Lines: 28 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: 200310/741 X-Sequence-Number: 4489 Rob Nagler writes: > One of the reason postgres is faster on the q1-4 is that postgres > supports OFFSET/LIMIT, and oracle doesn't. q7 and q8 are the queries > that I've referred to recently (avg of group by). Well the way to do offset/limit in Oracle is: SELECT * FROM ( SELECT ... , rownum AS n WHERE rownum <= OFFSET+LIMIT ) WHERE n > OFFSET That's basically the same thing Postgres does anyways. It actually has to do the complete query and fetch and discard the records up to the OFFSET and then stop when it hits the LIMIT. > q5 and q6 are too complex to discuss here, but the fundamental issue > is the order in which postgres decides to do things. That true for pretty 99% of all query optimization whether it's on Postgres or Oracle. I'm rather curious to see the query and explain analyze output from q5 and q6. -- greg