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: (view raw, whole thread or download thread mbox)
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: 

ALTER TABLE portal.metatdata rename TO portal.new_metatdata_old; 
ALTER TABLE portal.new_metatdata rename TO portal.metatdata_old; 

View this message in context:
Sent from the PostgreSQL - novice mailing list archive at

In response to


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-2017 The PostgreSQL Global Development Group