From: | DrYSG <ygutfreund(at)draper(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Slow duplicate deletes |
Date: | 2012-03-05 20:43:47 |
Message-ID: | 1330980227869-5538858.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
One point I might not have made clear. The reason I want to remove duplicates
is that the column "data_object.unique_id" became non-unique (someone added
duplicate rows). So I added the bigSeriel (idx) to uniquely identify the
rows, and I was using the SELECT MIN(idx) and GroupBy to pick just one of
the rows that became duplicated.
I am going to try out some of your excellent suggestions. I will report back
on how they are working.
One idea that was given to me was the following (what do you think Merlin?)
CREATE TABLE portal.new_metatdata AS
select distinct on (data_object.unique_id) * FROM portal.metadata;
Or something of this ilk should be faster because it only need to do a
sort on data_object.unique_id and then an insert. After you have
verified the results you can do:
BEGIN;
ALTER TABLE portal.metatdata rename TO portal.new_metatdata_old;
ALTER TABLE portal.new_metatdata rename TO portal.metatdata_old;
COMMIT;
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-duplicate-deletes-tp5537818p5538858.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | VARTAK, SATISH CTR DFAS | 2012-03-05 21:12:44 | postgreSQL odbc driver for Sun Solaris |
Previous Message | Merlin Moncure | 2012-03-05 19:52:38 | Re: Slow duplicate deletes |