From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: count(*) slow on large tables
Date: Fri, 03 Oct 2003 17:55:25 -0400
Message-ID: <m3brsydm0i.fsf@wolfe.cbbrowne.com> (raw)
References: <20031002191547.GZ87525@rlx11.zapatec.com>
<20031002193905.GD18417@wolff.to>
<3F7C98B8.C892D0E5@nsd.ca>
<60brszcng5.fsf@dev6.int.libertyrms.info>
<3F7D9A57.B37A0DFC@nsd.ca>
In the last exciting episode, jllachan@nsd.ca (Jean-Luc Lachance) wrote:
> Well I can think of many more case where it would be usefull:
>
> SELECT COUNT(DISTINCT x) FROM ...
> SELECT COUNT(*) FROM ... WHERE x = ?
Those are precisely the cases that the "other databases" ALSO fall
down on.
Maintaining those sorts of statistics would lead [in _ANY_ database;
PostgreSQL has no disadvantage in this] to needing for each and every
update to update a whole host of statistic values.
It would be fairly reasonable to have a trigger, in PostgreSQL, to
manage this sort of information. It would not be outrageously
difficult to substantially improve performance of queries, at the
considerable cost that each and every update would have to update a
statistics table.
If you're doing a whole lot of these sorts of queries, then it is a
reasonable idea to create appropriate triggers for the (probably very
few) tables where you are doing these counts.
But the notion that this should automatically be applied to all tables
always is a dangerous one. It would make update performance Suck
Badly, because the extra statistical updates would be quite expensive.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','cbbrowne.com').
http://www3.sympatico.ca/cbbrowne/multiplexor.html
I'm sorry Dave, I can't let you do that.
Why don't you lie down and take a stress pill?
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: cbbrowne@acm.org
Subject: Re: count(*) slow on large tables
In-Reply-To: <m3brsydm0i.fsf@wolfe.cbbrowne.com>
* 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