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 C34E0D1B531 for ; Sat, 4 Oct 2003 16:50:24 +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 37654-01 for ; Sat, 4 Oct 2003 13:49:38 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id ADF5FD1B50E for ; Sat, 4 Oct 2003 13:49:37 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h94GnXhn006744; Sat, 4 Oct 2003 12:49:33 -0400 (EDT) To: Bruce Momjian Cc: Christopher Browne , pgsql-performance@postgresql.org Subject: Re: count(*) slow on large tables In-reply-to: <200310041556.h94Fuek24423@candle.pha.pa.us> References: <200310041556.h94Fuek24423@candle.pha.pa.us> Comments: In-reply-to Bruce Momjian message dated "Sat, 04 Oct 2003 11:56:40 -0400" Date: Sat, 04 Oct 2003 12:49:33 -0400 Message-ID: <6743.1065286173@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/141 X-Sequence-Number: 3889 Bruce Momjian writes: > We do have a TODO item: > * Consider using MVCC to cache count(*) queries with no WHERE clause > The idea is to cache a recent count of the table, then have > insert/delete add +/- records to the count. A COUNT(*) would get the > main cached record plus any visible +/- records. This would allow the > count to return the proper value depending on the visibility of the > requesting transaction, and it would require _no_ heap or index scan. ... and it would give the wrong answers. Unless the cache is somehow snapshot-aware, so that it can know which other transactions should be included in your count. regards, tom lane