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 6D0F5D1B45F for ; Mon, 5 Jan 2004 19:53:13 +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 47404-09 for ; Mon, 5 Jan 2004 15:52:24 -0400 (AST) Received: from mail.hive.nj2.inquent.com (mc.carriermail.com [205.178.180.9]) by svr1.postgresql.org (Postfix) with SMTP id 71CA3D1B44B for ; Mon, 5 Jan 2004 15:52:08 -0400 (AST) Received: (qmail 10697 invoked from network); 5 Jan 2004 19:51:56 -0000 Received: from unknown (HELO ?10.0.2.16?) (216.208.117.7) by 205.178.180.9 with SMTP; 5 Jan 2004 19:51:56 -0000 Subject: Re: Select max(foo) and select count(*) optimization From: Rod Taylor To: siracusa@mindspring.com Cc: Postgresql Performance In-Reply-To: References: Content-Type: text/plain Message-Id: <1073332325.8958.8.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Mon, 05 Jan 2004 14:52:06 -0500 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/50 X-Sequence-Number: 5225 > 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. Actually, it's very painful. For MySQL, they've accepted the concurrancy hit in order to accomplish it -- PostgreSQL would require a more subtle approach. Anyway, with Rules you can force this: ON INSERT UPDATE counter SET tablecount = tablecount + 1; ON DELETE UPDATE counter SET tablecount = tablecount - 1; You need to create a table "counter" with a single row that will keep track of the number of rows in the table. Just remember, you've now serialized all writes to the table, but in your situation it may be worth while. max(foo) optimizations requires an extension to the aggregates system. It will likely happen within a few releases. A work around can be accomplished today through the use of LIMIT and ORDER BY.