From: Wei Weng <wweng@kencast.com>
To: pgsql-sql@postgresql.org
Subject: Re: Performance problem with row count trigger
Date: Thu, 02 Apr 2009 16:17:23 -0400
Message-ID: <49D51D53.2030305@kencast.com> (raw)
In-Reply-To: <16805.1238700723@sss.pgh.pa.us>
References: <49D4CA3A.3050005@andrew.cmu.edu>
<16805.1238700723@sss.pgh.pa.us>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
On 04/02/2009 03:32 PM, Tom Lane wrote:
<blockquote cite="mid:16805.1238700723@sss.pgh.pa.us" type="cite">
<pre wrap="">Tony Cebzanov <a class="moz-txt-link-rfc2396E" href="mailto:tonyceb@andrew.cmu.edu"><tonyceb@andrew.cmu.edu></a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">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:
</pre>
</blockquote>
<pre wrap=""><!---->
</pre>
<blockquote type="cite">
<pre wrap="">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;
</pre>
</blockquote>
<pre wrap=""><!---->
</pre>
<blockquote type="cite">
<pre wrap="">CREATE TRIGGER assoc_update_assoc_count_insert AFTER INSERT ON assoc
FOR EACH ROW EXECUTE PROCEDURE update_assoc_count_insert();
</pre>
</blockquote>
<pre wrap=""><!---->
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
<a class="moz-txt-link-freetext" href="http://archives.postgresql.org/pgsql-hackers/2003-09/msg00387.php">http://archives.postgresql...;
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
</pre>
</blockquote>
<br>
So, basically other than reading from pg_class table about the tuple
count, there isn't a good way to optimize the COUNT(*)?<br>
<br>
<br>
Thanks<br>
Wei<br>
<br>
</body>
</html>
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-sql@postgresql.org
Cc: wweng@kencast.com
Subject: Re: Performance problem with row count trigger
In-Reply-To: <49D51D53.2030305@kencast.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