v11.5 on linux (I know, I know, IT is working on the upgrade to 15.2)
I have a table with millions of records and there are a lot of "almost"
duplicates that I want to get rid of in an efficient way. Best to
illustrate with a simple example...
We'll talk about deleting leaves on a tree where each twig can have many
leaves, but never 2 or more leaves of the exact same surface area. What I
have how are a lot of twigs with leaves having the same surface area (and
some different one too) and I want to get rid of the duplicates for that
twig.
create table twigs (limb_id int, branch_id int, twig_id int, surfarea
float);
insert into twigs (linb_id,branch_id,twig_id,surfarea) values
(1,1,1,100.0),
*(1,1,2,103.7),*
*(1,1,3,103.7),*
(1,1,4,110.4),
(1,2,1,120.0),
(1,2,2,123.6),
*(1,2,3,128.1),*
*(1,2,4,128.1),*
*(1,2,5,128.1),*
(2,1,1,100.0),
(2,1,3,104.4),
(2,1,4,131.9);
You can see the duplicates in red. I want to get rid of all but one of
the dups. Which "twig_id" that's left behind doesn't matter.
This would do it...
delete from twigs where limb_id=1 and branch_id=1 and twig_id=23;
delete from twigs where limb_id=1 and branch_id=2 and twig_id in (4,5);
But there are millions of these duplicates and it'll take forever like this.
I was going to approach this with a perl/DBI script, shoving the duplicate
record identifiers (limb_id, branch_id, twig_id) into perl arrays and then
submitting the delete command in a prepared statement that accepts the
arrays as values ... (a-la... my $cnt = $sth->execute_array({
ArrayTupleStatus => \my
@tuple_status},\@limb_id_arr,\@branch_id_arr,\@twig_id_arr) or die "-F-
Failed to execute '$sqf'\n";) But I'm not sure that'll be faster.
Depends on how perl/DBI handles it I suppose.
Then I was thinking it might just be better to create a parallel table and
insert records in there by copying from the table with the dups, taking
care to avoid inserting duplicates. Once done, delete the original table
and rename the copy to the original's name. If that's a good approach,
then how exactly do I articulate the insert statement to avoid the
duplicates ? This approach might also be good in that I can do it outside
a transaction. If the target table gets goofed up, the original data is
still OK, no harm done, can try again.
Any other ideas ?
Again, millions of duplicates and I don't want to overload any PG system
stuff in the process.
Thanks in Advance !
