X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9C50ED1B519 for ; Mon, 8 Sep 2003 22:07:51 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 36954-08 for ; Mon, 8 Sep 2003 19:07:41 -0300 (ADT) Received: from news.hub.org (unknown [64.117.224.194]) by svr1.postgresql.org (Postfix) with ESMTP id 024C8D1B4E6 for ; Mon, 8 Sep 2003 19:07:38 -0300 (ADT) Received: from news.hub.org (host-64-117-224-194.altec1.com [64.117.224.194] (may be forged)) by news.hub.org (8.12.9/8.12.9) with ESMTP id h88M7Xsl005163 for ; Mon, 8 Sep 2003 22:07:33 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h88LiKYS098242 for pgsql-performance@postgresql.org; Mon, 8 Sep 2003 21:44:20 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: slow plan for min/max Date: Mon, 08 Sep 2003 17:41:43 -0400 Organization: cbbrowne Computing Inc Lines: 34 Message-ID: References: <1063046951.9051.68.camel@tokyo> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1003 (Gnus v5.10.3) XEmacs/21.4 (Rational FORTRAN, linux) Cancel-Lock: sha1:LTmRI82KgRPwpQorJEdOC/anwPA= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/131 X-Sequence-Number: 3497 After takin a swig o' Arrakan spice grog, scott.marlowe@ihs.com ("scott.marlowe") belched out...: > On Mon, 8 Sep 2003, Neil Conway wrote: >> On Mon, 2003-09-08 at 11:56, scott.marlowe wrote: >> > Basically, Postgresql uses an MVCC locking system that makes massively >> > parallel operation possible, but costs in certain areas, and one of those >> > areas is aggregate performance over large sets. MVCC makes it very hard >> > to optimize all but the simplest of aggregates, and even those >> > optimzations which are possible would wind up being quite ugly at the >> > parser level. >> >> As was pointed out in a thread a couple days ago, MIN/MAX() optimization >> has absolutely nothing to do with MVCC. It does, however, make >> optimizing COUNT() more difficult. > > Not exactly. While max(id) is easily optimized by query replacement, > more complex aggregates will still have perfomance issues that would not > be present in a row locking database. i.e. max((field1/field2)*field3) is > still going to cost more to process, isn't it? That sort of MAX() would be difficult to optimize in almost any case, and would mandate doing a scan across the relevant portion of the table... ... Unless you had a functional index on (field1/field2)*field3, in which case it might well be that this would cost Still Less. I still can't fathom what this has to do with MVCC; you have yet to actually connect it with that... -- let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];; http://www3.sympatico.ca/cbbrowne/lsf.html "Cars move huge weights at high speeds by controlling violent explosions many times a second. ...car analogies are always fatal..." --