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

Re: count(*) slow on large tables

From: Christopher Browne <cbbrowne(at)acm(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: count(*) slow on large tables
Date: 2003-10-03 21:55:25
Message-ID: m3brsydm0i.fsf@wolfe.cbbrowne.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
In the last exciting episode, jllachan(at)nsd(dot)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?

In response to

pgsql-performance by date

Next:From: Bruno Wolff IIIDate: 2003-10-03 21:59:39
Subject: Re: reindex/vacuum locking/performance?
Previous:From: Dror MatalonDate: 2003-10-03 21:53:47
Subject: Re: Speeding up Aggregates

pgsql-hackers by date

Next:From: Nigel J. AndrewsDate: 2003-10-03 22:20:55
Subject: Re: Thoughts on maintaining 7.3
Previous:From: Bruce MomjianDate: 2003-10-03 21:41:45
Subject: Re: Thoughts on maintaining 7.3

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