Skip site navigation (1) Skip section navigation (2)

Re: count(*) slow on large tables

From: Jean-Luc Lachance <jllachan(at)nsd(dot)ca>
To: Christopher Browne <cbbrowne(at)libertyrms(dot)info>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: count(*) slow on large tables
Date: 2003-10-03 15:48:39
Message-ID: 3F7D9A57.B37A0DFC@nsd.ca (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
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(at)nsd(dot)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];;
> <http://dev6.int.libertyrms.com/>
> Christopher Browne
> (416) 646 3304 x124 (land)
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

In response to

Responses

pgsql-performance by date

Next:From: StefDate: 2003-10-03 16:10:02
Subject: Re: Postgres low end processing.
Previous:From: Tom LaneDate: 2003-10-03 15:42:54
Subject: Re: Postgres low end processing.

pgsql-hackers by date

Next:From: Tom LaneDate: 2003-10-03 15:53:05
Subject: Re: Oracle/PostgreSQL incompatibilities
Previous:From: Rod TaylorDate: 2003-10-03 15:44:42
Subject: Re: Question regarding coopting Database Engine

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group