From: Richard Huxton <dev@archonet.com>
To: Yves Vindevogel <yves.vindevogel@implements.be>
Cc: pgsql-performance@postgresql.org
Subject: Re: Insert performance (OT?)
Date: Tue, 19 Jul 2005 10:39:07 +0100
Message-ID: <42DCCA3B.8070100@archonet.com> (raw)
In-Reply-To: <b201f08ef88897bc4ad87631fda3d877@implements.be>
References: <b201f08ef88897bc4ad87631fda3d877@implements.be>
Yves Vindevogel wrote:
> Hi,
>
> Suppose I have a table with 4 fields (f1, f2, f3, f4)
> I define 2 unique indexes u1 (f1, f2, f3) and u2 (f1, f2, f4)
>
> I have 3 records
> A, B, C, D (this will be inserted)
> A, B, C, E (this will pass u2, but not u1, thus not inserted)
> A, B, F, D (this will pass u1, but not u2, thus not inserted)
Are you saying you want to know whether they will be inserted before you
try to do so?
> Now, for performance ...
>
> I have tables like this with 500.000 records where there's a new upload
> of approx. 20.000 records.
> It is only now that we say index u2 to be necessary. So, until now, I
> did something like insert into ... select f1, f2, f2, max(f4) group by
> f1, f2, f3
> That is ok ... and also logically ok because of the data definition
I'm confused here - assuming you meant "select f1,f2,f3", then I don't
see how you guarantee the row doesn't alredy exist.
> I cannot do this with 2 group by's. I tried this on paper and I'm not
> succeeding.
I don't see how you can have two group-by's, or what that would mean if
you did.
> So, I must use a function that will check against u1 and u2, and then
> insert if it is ok.
> I know that such a function is way slower that my insert query.
So - you have a table, called something like "upload" with 20,000 rows
and you'd like to know whether it is safe to insert them. Well, it's
easy enough to identify which ones are duplicates.
SELECT * FROM upload JOIN main_table ON u1=f1 AND u2=f2 AND u3=f3;
SELECT * FROM upload JOIN main_table ON u1=f1 AND u2=f2 AND u3=f4;
Are you saying that deleting these rows and then inserting takes too long?
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, yves.vindevogel@implements.be
Subject: Re: Insert performance (OT?)
In-Reply-To: <42DCCA3B.8070100@archonet.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