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 8FE10D1B4C7 for ; Mon, 5 Jan 2004 20:35:16 +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 57507-05 for ; Mon, 5 Jan 2004 16:34:26 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 8DF00D1B444 for ; Mon, 5 Jan 2004 16:34:26 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i05KYQU6065368 for ; Mon, 5 Jan 2004 20:34:26 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i05KStnw062330 for pgsql-performance@postgresql.org; Mon, 5 Jan 2004 20:28:55 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Select max(foo) and select count(*) optimization Date: Mon, 05 Jan 2004 15:26:15 -0500 Organization: cbbrowne Computing Inc Lines: 50 Message-ID: References: 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 (Reasonable Discussion, linux) Cancel-Lock: sha1:gy8BOild0Jka8eTJbKw2gIJDnBI= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/55 X-Sequence-Number: 5230 Oops! siracusa@mindspring.com (John Siracusa) was seen spray-painting on a wall: > Speaking of special cases (well, I was on the admin list) there are two > kinds that would really benefit from some attention. > > 1. The query "select max(foo) from bar" where the column foo has an > index. Aren't indexes ordered? If not, an "ordered index" would be > useful in this situation so that this query, rather than doing a > sequential scan of the whole table, would just "ask the index" for > the max value and return nearly instantly. > > 2. The query "select count(*) from bar" Surely the total number of > rows in a table is kept somewhere convenient. If not, it would be > nice if it could be :) Again, rather than doing a sequential scan of > the entire table, this type of query could return instantly. > > I believe MySQL does both of these optimizations (which are probably > a lot easier in that product, given its data storage system). These > were the first areas where I noticed a big performance difference > between MySQL and Postgres. > > Especially with very large tables, hearing the disks grind as > Postgres scans every single row in order to determine the number of > rows in a table or the max value of a column (even a primary key > created from a sequence) is pretty painful. If the implementation > is not too horrendous, this is an area where an orders-of-magnitude > performance increase can be had. These are both VERY frequently asked questions. In the case of question #1, the optimization you suggest could be accomplished via some Small Matter Of Programming. None of the people that have wanted the optimization have, however, offered to actually DO the programming. In the case of #2, the answer is "surely NOT." In MVCC databases, that information CANNOT be stored anywhere convenient because queries requested by transactions started at different points in time must get different answers. I think we need to add these questions and their answers to the FAQ so that the answer can be "See FAQ Item #17" rather than people having to gratuitously explain it over and over and over again. -- (reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc")) http://www.ntlug.org/~cbbrowne/finances.html Rules of the Evil Overlord #127. "Prison guards will have their own cantina featuring a wide variety of tasty treats that will deliver snacks to the guards while on duty. The guards will also be informed that accepting food or drink from any other source will result in execution."