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 3D501D1B4F2 for ; Fri, 3 Oct 2003 15:40:08 +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 77399-03 for ; Fri, 3 Oct 2003 12:39:21 -0300 (ADT) Received: from beamish.nsd.ca (beamish.nsd.ca [205.150.156.194]) by svr1.postgresql.org (Postfix) with ESMTP id F0239D1B53F for ; Fri, 3 Oct 2003 12:39:20 -0300 (ADT) Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id LAA19275; Fri, 3 Oct 2003 11:39:19 -0400 X-Authentication-Warning: beamish.nsd.ca: smap set sender to using -f Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap (V2.1/2.1+anti-relay+anti-spam) id xma019271; Fri, 3 Oct 03 11:38:58 -0400 Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148]) by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id LAA18836; Fri, 3 Oct 2003 11:25:04 -0400 Message-ID: <3F7D9A57.B37A0DFC@nsd.ca> Date: Fri, 03 Oct 2003 11:48:39 -0400 From: Jean-Luc Lachance X-Mailer: Mozilla 4.8 [en] (X11; U; Linux 2.4.18-24.7.x i686) X-Accept-Language: en MIME-Version: 1.0 To: Christopher Browne Cc: pgsql-performance@postgresql.org Subject: Re: count(*) slow on large tables References: <20031002191547.GZ87525@rlx11.zapatec.com> <20031002193905.GD18417@wolff.to> <3F7C98B8.C892D0E5@nsd.ca> <60brszcng5.fsf@dev6.int.libertyrms.info> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/89 X-Sequence-Number: 3837 Well I can think of many more case where it would be usefull: SELECT COUNT(DISTINCT x) FROM ... SELECT COUNT(*) FROM ... WHERE x = ? Also having transaction number (visibility) would tip the balance more toward index_scan than seq_scan because you do not have to look up visibility in the data file. We all know this has been an issue many times. Having a different index file structure when the index is not UNIQUE would help too. The last page of a non unique index could hold more stats. Christopher Browne wrote: > > jllachan@nsd.ca (Jean-Luc Lachance) writes: > > That's one of the draw back of MVCC. > > I once suggested that the transaction number and other house keeping > > info be included in the index, but was told to forget it... > > It would solve once and for all the issue of seq_scan vs index_scan. > > It would simplify the aggregate problem. > > It would only simplify _one_ case, namely the case where someone cares > about the cardinality of a relation, and it would do that at > _considerable_ cost. > > A while back I outlined how this would have to be done, and for it to > be done efficiently, it would be anything BUT simple. > > It would be very hairy to implement it correctly, and all this would > cover is the single case of "SELECT COUNT(*) FROM SOME_TABLE;" > > If you had a single WHERE clause attached, you would have to revert to > walking through the tuples looking for the ones that are live and > committed, which is true for any DBMS. > > And it still begs the same question, of why the result of this query > would be particularly meaningful to anyone. I don't see the > usefulness; I don't see the value of going to the considerable effort > of "fixing" this purported problem. > -- > let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];; > > Christopher Browne > (416) 646 3304 x124 (land) > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster