Skip site navigation (1) Skip section navigation (2)

Re: Slow duplicate deletes

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 (view raw or flat)
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.

In response to

Responses

pgsql-novice by date

Next:From: VARTAK, SATISH CTR DFASDate: 2012-03-05 21:12:44
Subject: postgreSQL odbc driver for Sun Solaris
Previous:From: Merlin MoncureDate: 2012-03-05 19:52:38
Subject: Re: Slow duplicate deletes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group