From: Jochen Erwied <jochen@erwied.eu>
To: Marc Mamin <M.Mamin@intershop.de>
Cc: antoine@inaps.org
Cc: pgsql-performance@postgresql.org
Subject: Re: Duplicate deletion optimizations
Date: Sat, 7 Jan 2012 15:18:37 +0100
Message-ID: <1723406361.20120107151837@erwied.eu> (raw)
In-Reply-To: <C4DAC901169B624F933534A26ED7DF3103E917F3@JENMAIL01.ad.intershop.net>
References: <3da8ce0cdf1da4ef873841d01ed0e70a@inaps.org>
<3907c59005bc154cf0b8ebb918303d51@inaps.org>
<1301943199.20120107125726@erwied.eu>
<C4DAC901169B624F933534A26ED7DF3103E917F3@JENMAIL01.ad.intershop.net>
Saturday, January 7, 2012, 1:21:02 PM you wrote:
> where t_imp.id is null and test.id=t_imp.id;
> =>
> where t_imp.id is not null and test.id=t_imp.id;
You're right, overlooked that one. But the increase to execute the query is
- maybe not completely - suprisingly minimal.
Because the query updating the id-column of t_imp fetches all rows from
test to be updated, they are already cached, and the second query is run
completely from cache. I suppose you will get a severe performance hit when
the table cannot be cached...
I ran the loop again, after 30 minutes I'm at about 3-5 seconds per loop,
as long as the server isn't doing something else. Under load it's at about
10-20 seconds, with a ratio of 40% updates, 60% inserts.
> and a partial index on matching rows might help (should be tested):
> (after the first updat)
> create index t_imp_ix on t_imp(t_value,t_record,output_id) where t_imp.id is not null.
I don't think this will help much since t_imp is scanned sequentially
anyway, so creating an index is just unneeded overhead.
--
Jochen Erwied | home: jochen@erwied.eu +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 | work: joe@mbs-software.de +49-2151-7294-24, FAX: -50
D-45470 Muelheim | mobile: jochen.erwied@vodafone.de +49-173-5404164
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: jochen@erwied.eu, M.Mamin@intershop.de, antoine@inaps.org
Subject: Re: Duplicate deletion optimizations
In-Reply-To: <1723406361.20120107151837@erwied.eu>
* 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