optimalisation with EXCEPT clause

From: "Kincel, Martin" <MKincel(at)soitron(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: optimalisation with EXCEPT clause
Date: 2010-04-13 15:01:18
Message-ID: A5ED43533E983E4685C9E6156BE8874F0840D83D@kenya.tronet.as
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

everyday I collect a couple of thousands rows of unique data from our
systems and I INSERT them into the table. Since I need no duplicate
data, I use EXCEPT clause when INSERTing, like this:

===
INSERT INTO data SELECT * FROM new_collected_data() EXCEPT SELECT * FROM
data;
===

It works exactly as I need, but there is a small issue I am thinking
about how to improve. Yes it's performance, what else? :)

Since I am INSERTing new_collected_data() in 10000-rows chunks into a
table already containing millions of rows, it takes a few minutes
(literally), which is something I understand and accept.
However, I am wondering whether there is any way how to improve the
performance, either via indices, or ALTERing TABLE with UNIQUE
constraint or something else I might have completely forgot about.

Does anyone have any recommended approach how to speed up queries
containing EXCEPT clause?

Thanks a lot,
Winco

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Grzegorz Jaśkiewicz 2010-04-13 15:23:35 Re: optimalisation with EXCEPT clause
Previous Message Brent Friedman 2010-04-13 14:26:04 General question about speed of functions