From: Kenneth Marshall <ktm@rice.edu>
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: query performance question
Date: Thu, 5 Jun 2008 12:16:39 -0500
Message-ID: <20080605171639.GE5624@it.is.rice.edu> (raw)
In-Reply-To: <4848098A.5070500@drivefaster.net>
References: <4844F95B.4040009@m4n.nl>
<31028.217.77.161.17.1212483874.squirrel@sq.gransy.com>
<4848098A.5070500@drivefaster.net>
Dan,
Did you try this with 8.3 and its new HOT functionality?
Ken
On Thu, Jun 05, 2008 at 09:43:06AM -0600, Dan Harris wrote:
> 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
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
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: ktm@rice.edu, fbsd@drivefaster.net
Subject: Re: query performance question
In-Reply-To: <20080605171639.GE5624@it.is.rice.edu>
* 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