From: Mischa Sandberg <ischamay.andbergsay@activestateway.com>
To: pgsql-performance@postgresql.org
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
Date: Tue, 14 Sep 2004 22:58:20 GMT
Message-ID: <gIK1d.26646$XP3.14809@edtnps84> (raw)
In-Reply-To: <20040914143303.GR21419@ns.snowman.net>
References: <20040912042442.49915.qmail@web41508.mail.yahoo.com>
<pB21d.193816$X12.122954@edtnps84>
<20040914141452.002e5d4e@kingfisher.intern.logi-track.com>
<20040914143303.GR21419@ns.snowman.net>
Googling 'upsert' (an Oraclism, I believe) will get you hits on Oracle
and DB2's implementation of MERGE, which does what AMOUNTS to what is
described below (one mass UPDATE...FROM, one mass INSERT...WHERE NOT
EXISTS).
No, you shouldn't iterate row-by-row through the temp table.
Whenever possible, try to do updates in one single (mass) operation.
Doing it that way gives the optimizer the best chance at amortizing
fixed costs, and batching operations.
---------
In any database other than Postgres, I would recommend doing the
INSERT /followed by/ the UPDATE. That order looks wonky --- your update
ends up pointlessly operating on the rows just INSERTED. The trick is,
UPDATE acquires and holds write locks (the rows were previously visible
to other processes), while INSERT's write locks refer to rows that no
other process could try to lock.
Stephen Frost wrote:
> * Markus Schaber (schabios@logi-track.com) wrote:
>
>>Generally, what is the fastest way for doing bulk processing of
>>update-if-primary-key-matches-and-insert-otherwise operations?
>
>
> This is a very good question, and I havn't seen much of an answer to it
> yet. I'm curious about the answer myself, actually. In the more recent
> SQL specs, from what I understand, this is essentially what the 'MERGE'
> command is for. This was recently added and unfortunately is not yet
> supported in Postgres. Hopefully it will be added soon.
>
> Otherwise, what I've done is basically an update followed by an insert
> using outer joins. If there's something better, I'd love to hear about
> it. The statements looks something like:
>
> update X
> set colA = a.colA,
> colB = a.colB
> from Y a
> where keyA = a.keyA and
> keyB = a.keyB;
>
> insert into X
> select a.keyA,
> a.keyB,
> a.colA,
> a.colB
> from Y a left join X b
> using (keyA, keyB)
> where b.keyA is NULL and
> b.keyB is NULL;
>
> With the appropriate indexes, this is pretty fast but I think a merge
> would be much faster.
>
> Thanks,
>
> Stephen
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: ischamay.andbergsay@activestateway.com
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
In-Reply-To: <gIK1d.26646$XP3.14809@edtnps84>
* 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