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 A527AD1B4FC for ; Mon, 8 Sep 2003 20:37:44 +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 15147-09 for ; Mon, 8 Sep 2003 17:37:35 -0300 (ADT) Received: from news.hub.org (unknown [64.117.224.194]) by svr1.postgresql.org (Postfix) with ESMTP id D61CDD1B4E1 for ; Mon, 8 Sep 2003 17:37:33 -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 h88KbXsl079666 for ; Mon, 8 Sep 2003 20:37:33 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h88K9xsn071747 for pgsql-performance@postgresql.org; Mon, 8 Sep 2003 20:09:59 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: slow plan for min/max Date: Mon, 08 Sep 2003 15:32:16 -0400 Organization: Hub.Org Networking Services Lines: 59 Message-ID: <60n0df5b5r.fsf@dev6.int.libertyrms.info> References: Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1003 (Gnus v5.10.3) XEmacs/21.4 (Rational FORTRAN, linux) Cancel-Lock: sha1:0+8X5l7Uy3vT/qUJi+A0u9RmhIs= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/126 X-Sequence-Number: 3492 scott.marlowe@ihs.com ("scott.marlowe") writes: > On Sun, 7 Sep 2003, Pailloncy Jean-Gérard wrote: > > Asking a question about why max(id) is so much slower than select id order > by id desc limit 1, Pailloncy said: > >> I ask for the same thing. That's better ! > > This is a Frequently asked question about something that isn't > likely to change any time soon. > > 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. MVCC makes it difficult to optimize aggregates resembling COUNT(*) or SUM(*), at least vis-a-vis having this available for a whole table (e.g. - you have to be doing 'SELECT COUNT(*), SUM(SOMEFIELD) FROM THIS_TABLE' with NO "WHERE" clause). But there is nothing about MVCC that makes it particularly difficult to handle the transformation: select max(field) from some_table where another_field < still_another_field; (which isn't particularly efficient) into select field from some_table where another_field < still_another_field order by field desc limit 1; The problems observed are thus: 1. If the query asks for other data, it might be necessary to scan the table to get the other data, making the optimization irrelevant; 2. If there's a good index to key on, the transformed version might be a bunch quicker, but it is nontrivial to determine that, a priori; 3. It would be a fairly hairy optimization to throw into the query optimizer, so people are reluctant to try to do so. Note that MVCC has _nothing_ to do with any of those three problems. The MVCC-related point is that there is reluctance to create some special case that will be troublesome to maintain instead of having some comprehensive handling of _all_ aggregates. It seems a better idea to "fix them all" rather than to kludge things up by fixing one after another. -- let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;; http://cbbrowne.com/info/lisp.html Signs of a Klingon Programmer - 10. "A TRUE Klingon Warrior does not comment his code!"