Re: Slow COUNT

On 12/2/2005 2:02 PM, Jaime Casanova wrote:
> so the way to do it is create a trigger that record in a table the
> number of rows...

Neither, because now you have to update one single row in that new
table, which causes a row exclusive lock. That is worse than an
exclusive lock on the original table because it has the same
serialization of writers but the additional work to update the count
table as well as vacuum it.

What you need is a separate table where your trigger will insert delta
rows with +1 or -1 for insert and delete. A view will sum() over that
and tell you the true number of rows. Periodically you condense the
table by replacing all current rows with one that represents the sum().

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #