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 3EDFAD1B511 for ; Tue, 6 Jan 2004 02: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 21009-06 for ; Mon, 5 Jan 2004 22:34:29 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id EC6BBD1B49B for ; Mon, 5 Jan 2004 22: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 i062YQU6078269 for ; Tue, 6 Jan 2004 02: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 i062VhL0076928 for pgsql-performance@postgresql.org; Tue, 6 Jan 2004 02:31:43 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Select max(foo) and select count(*) optimization Date: Mon, 05 Jan 2004 21:31:29 -0500 Organization: cbbrowne Computing Inc Lines: 64 Message-ID: References: <1073332325.8958.8.camel@jester> 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:5NnaKoa0P3JZKv1s3Qp8O/Vhdn0= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/60 X-Sequence-Number: 5235 pg@rbt.ca (Rod Taylor) wrote: >> 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. There's a still more subtle approach that relieves the serialization constraint, at some cost... - You add rules that _insert_ a row each time there is an insert/delete ON INSERT insert into counts(table, value) values ('our_table', 1); ON DELETE insert into counts(table, value) values ('our_table', -1); - The "select count(*) from our_table" is replaced by "select sum(value) from counts where table = 'our_table'" - Periodically, a "compression" process goes through and either: a) Deletes the rows for 'our_table' and replaces them with one row with a conventionally-scanned 'count(*)' value, or b) Computes "select table, sum(value) as value from counts group by table", deletes all the existing rows in counts, and replaces them by the preceding selection, or c) Perhaps does something incremental that's like b), but which only processes parts of the "count" table at once. Process 500 rows, then COMMIT, or something of the sort... Note that this "counts" table can potentially grow _extremely_ large. The "win" comes when it gets compressed, so that instead of scanning through 500K items, it index-scans through 27, the 1 that has the "497000" that was the state of the table at the last compression, and then 26 singletons. A win comes in if an INSERT that adds in 50 rows can lead to inserting ('our_table', 50) into COUNTS, or a delete that eliminates 5000 rows puts in ('our_table', -5000). It's vital to run the "compression" reasonably often (much like VACUUM :-)) in order that the COUNTS summary table stays relatively small. -- let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];; http://www3.sympatico.ca/cbbrowne/wp.html Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. -- Brian W. Kernighan