From: Bricklen Anderson <BAnderson@PresiNET.com>
To: Jacques Caron <jc@directinfos.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: ETL optimization
Date: Thu, 23 Jun 2005 15:04:33 -0700
Message-ID: <42BB31F1.7000303@PresiNET.com> (raw)
In-Reply-To: <6.2.0.14.0.20050623234711.053c4d48@pop.interactivemediafactory.net>
References: <42BB0F9C.2050207@PresiNET.com>
<6.2.0.14.0.20050623234711.053c4d48@pop.interactivemediafactory.net>
Jacques Caron wrote:
>
> I have a similar situation, and the solution I use (though I haven't
> really tested many different situations):
> - have a trigger ON INSERT which does:
> UPDATE set whatever_value=NEW.whatever_value,... WHERE
> whatever_key=NEW.whatever.key AND...
> IF FOUND THEN
> RETURN NULL;
> ELSE
> RETURN NEW;
> END IF;
> - use COPY
>
> For optimal performance, a different trigger function is created for
> each table, which allows the query plan of the UPDATE to be cached.
>
> Let us know how that works out for you and if you find a better solution!
>
> Jacques.
>
Hi Jacques, thanks for the suggestion. I've previously tested triggers under a
variety of situations and there was no way that they would work under the load
we currently have, and the much greater load that we will be expecting soon
(~40x increase in data).
I'm in the process of testing the delete scenario right now, and at first blush
seems to perform fairly well. 2.5 million rows before aggregation, and 171000
after, in a little under 7 minutes.
Currently testing again with about 18.5 million rows. A drawback by using the
delete method is that we cannot do any of the aggregation incrementally, but so
far that hasn't been a big help anyways. I still need to test the performance of
concurrent querying against the destination table whilst the aggregation is
occurring.
--
_______________________________
This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________
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: BAnderson@PresiNET.com, jc@directinfos.com
Subject: Re: ETL optimization
In-Reply-To: <42BB31F1.7000303@PresiNET.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