From: Dan Harris <fbsd@drivefaster.net>
To: pgsql-performance@postgresql.org
Subject: Re: query performance question
Date: Thu, 05 Jun 2008 09:43:06 -0600
Message-ID: <4848098A.5070500@drivefaster.net> (raw)
In-Reply-To: <31028.217.77.161.17.1212483874.squirrel@sq.gransy.com>
References: <4844F95B.4040009@m4n.nl>
<31028.217.77.161.17.1212483874.squirrel@sq.gransy.com>
tv@fuzzy.cz wrote:
>
> 3) Build a table with totals or maybe subtotals, updated by triggers. This
> requires serious changes in application as well as in database, but solves
> issues of 1) and may give you even better results.
>
> Tomas
>
>
I have tried this. It's not a magic bullet. We do our billing based on
counts from huge tables, so accuracy is important to us. I tried
implementing such a scheme and ended up abandoning it because the
summary table became so full of dead tuples during and after large bulk
inserts that it slowed down selects on that table to an unacceptable
speed. Even with a VACUUM issued every few hundred inserts, it still
bogged down due to the constant churn of the inserts.
I ended up moving this count tracking into the application level. It's
messy and only allows a single instance of an insert program due to the
localization of the counts in program memory, but it was the only way I
found to avoid the penalty of constant table churn on the triggered inserts.
-Dan
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: fbsd@drivefaster.net
Subject: Re: query performance question
In-Reply-To: <4848098A.5070500@drivefaster.net>
* 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