Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 92799634E15 for ; Thu, 2 Apr 2009 17:47:27 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 50903-05 for ; Thu, 2 Apr 2009 17:47:25 -0300 (ADT) X-Greylist: delayed 00:30:00.215077 by SQLgrey-1.7.6 Received: from mta2.srv.hcvlny.cv.net (mta2.srv.hcvlny.cv.net [167.206.4.197]) by mail.postgresql.org (Postfix) with ESMTP id 1A728634AEA for ; Thu, 2 Apr 2009 17:47:26 -0300 (ADT) Received: from [192.168.1.103] (ool-60383673.static.optonline.net [96.56.54.115]) by mta2.srv.hcvlny.cv.net (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) with ESMTP id <0KHH00J0QPOZOYT0@mta2.srv.hcvlny.cv.net> for pgsql-sql@postgresql.org; Thu, 02 Apr 2009 16:17:24 -0400 (EDT) Date: Thu, 02 Apr 2009 16:17:23 -0400 From: Wei Weng Subject: Re: Performance problem with row count trigger In-reply-to: <16805.1238700723@sss.pgh.pa.us> To: pgsql-sql@postgresql.org Message-id: <49D51D53.2030305@kencast.com> MIME-version: 1.0 Content-type: text/html; charset=ISO-8859-1 Content-transfer-encoding: 7BIT References: <49D4CA3A.3050005@andrew.cmu.edu> <16805.1238700723@sss.pgh.pa.us> User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.673 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.672 X-Spam-Level: * X-Archive-Number: 200904/19 X-Sequence-Number: 32365 On 04/02/2009 03:32 PM, Tom Lane wrote:

Tony Cebzanov <tonyceb@andrew.cmu.edu> writes:

What I want to do is update the assoc_count field in the dataset table
to reflect the count of related records in the assoc field.  To do so, I
added the following trigger:

CREATE OR REPLACE FUNCTION update_assoc_count_insert()
RETURNS TRIGGER AS
'
BEGIN
    UPDATE dataset
        SET assoc_count = assoc_count + 1
        WHERE dataset_id = NEW.dataset_id;
    RETURN NEW;
END
' LANGUAGE plpgsql;

CREATE TRIGGER assoc_update_assoc_count_insert AFTER INSERT ON assoc
    FOR EACH ROW EXECUTE PROCEDURE update_assoc_count_insert();


There is basically no way that this is going to not suck :-(.  In the
first place, using an AFTER trigger means that each update queues an
AFTER trigger update event that has to be fired at statement or
transaction end.  In the second place (as Craig correctly noted) this
results in a separate update to the count-table row for each inserted
row, which tremendously bloats the count table with dead tuples.
In the third place, if you have any concurrency of insertions, it
disappears because all the inserters need to update the same count row.

If you dig in the pgsql-hackers archives, you will find that the
original scheme for this was to have each transaction accumulate its
total number of insertions minus deletions for a table in local memory,
and then insert *one* delta row into the count table just before
transaction commit.  I don't think it's possible to do that with just
user-level triggers (not least because we haven't got ON COMMIT
triggers); it would have to be a C-code addition.  The various blog
entries you cite are non-peer-reviewed oversimplifications of that
design.

Digging around, the oldest description I can find of this idea is
http://archives.postgresql.org/pgsql-hackers/2003-09/msg00387.php
although there are more recent threads rehashing the topic.

One point I don't recall anyone mentioning is that the stats subsystem
now implements a fairly large subset of this work already, namely the
initial data accumulation.  So you could imagine plugging something into
that to send the deltas to a table in addition to the stats collector.

			regards, tom lane

So, basically other than reading from pg_class table about the tuple count, there isn't a good way to optimize the COUNT(*)?

Thanks
Wei