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 8A175D1B45E for ; Mon, 5 Jan 2004 19:03:51 +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 41945-04 for ; Mon, 5 Jan 2004 15:03:02 -0400 (AST) Received: from cayenne.palmdigitalmedia.com (cayenne.palmdigitalmedia.com [63.110.43.230]) by svr1.postgresql.org (Postfix) with SMTP id 88875D1B4C2 for ; Mon, 5 Jan 2004 15:03:01 -0400 (AST) Received: (qmail 8356 invoked from network); 5 Jan 2004 19:03:02 -0000 Received: from unknown (HELO ?192.168.124.23?) (63.110.43.226) by 0 with SMTP; 5 Jan 2004 19:03:02 -0000 User-Agent: Microsoft-Entourage/10.1.1.2418 Date: Mon, 05 Jan 2004 14:03:12 -0500 Subject: Select max(foo) and select count(*) optimization From: John Siracusa To: Postgres Performance Message-ID: Mime-version: 1.0 Reply-To: siracusa@mindspring.com Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/46 X-Sequence-Number: 5221 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. -John