From: Jean-Luc Lachance <jllachan@nsd.ca>
To: Christopher Browne <cbbrowne@libertyrms.info>
Cc: pgsql-performance@postgresql.org
Subject: Re: count(*) slow on large tables
Date: Fri, 03 Oct 2003 11:48:39 -0400
Message-ID: <3F7D9A57.B37A0DFC@nsd.ca> (raw)
References: <20031002191547.GZ87525@rlx11.zapatec.com>
<20031002193905.GD18417@wolff.to>
<3F7C98B8.C892D0E5@nsd.ca>
<60brszcng5.fsf@dev6.int.libertyrms.info>
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];;
> <http://dev6.int.libertyrms.com/;
> Christopher Browne
> (416) 646 3304 x124 (land)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jllachan@nsd.ca, cbbrowne@libertyrms.info
Subject: Re: count(*) slow on large tables
In-Reply-To: <3F7D9A57.B37A0DFC@nsd.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox