Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qst9K-009vyE-9b for pgsql-general@arkaria.postgresql.org; Tue, 17 Oct 2023 23:06:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qst9H-00FHPr-RR for pgsql-general@arkaria.postgresql.org; Tue, 17 Oct 2023 23:06:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qst9H-00FHPj-FD for pgsql-general@lists.postgresql.org; Tue, 17 Oct 2023 23:06:52 +0000 Received: from mail-yw1-x1136.google.com ([2607:f8b0:4864:20::1136]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qst99-001GQ6-2Y for pgsql-general@lists.postgresql.org; Tue, 17 Oct 2023 23:06:51 +0000 Received: by mail-yw1-x1136.google.com with SMTP id 00721157ae682-5a7afd45199so79025587b3.0 for ; Tue, 17 Oct 2023 16:06:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697584002; x=1698188802; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=zO7XtK/ghoVqWVIvDelRnW3jl2r9jhITkcHRE2sUZe0=; b=Q9nQLvZdgkWOXSkINxCP6ByO0uNx/H0H347u5lPXGoJm3oAsJaVS87z6Xncjha+SYf 4R0KhVF0IhbvJUF9BacJe41FZG2Gd4/BwkIptjcCm3BC+VCMl1X58Q9WtL4dO7bXSmfE 9iHgqkAVoVs//dU+8GdK3L+iqF8C9XtuCxxioBYpWhcBj6F3B0e8aJCSCVSWZRgmL2my yK5wtl1uRnVIGMVztJ9d0cDOw33RWyxd20AC71vthhbwV4FAuNA25lgILtsXrdyazmVQ l52HDt5aHyH3Gvs8F8eYES0Wtcm1Y1zd4w5S+qMcSyE5fGipdtgsGfc29+lmZeNQoluB ddyg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697584002; x=1698188802; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=zO7XtK/ghoVqWVIvDelRnW3jl2r9jhITkcHRE2sUZe0=; b=iJCvG4Xs0CNTACGjoJOWQOKp4a92WLuOYQMQo7HBoI1pGvrTKGRZn4czx5kNNdX9Rq Qr+UnXYZQpyJ+6XbctRrUU1wSB9I0vpdc54jVCllxr8dqg/5oSwt7BecYC1HR7g74pKA qHK84/ipBX8RWNWThtqXgd5uirqpAKU5JPNWvtdGIbzOW16SbYzlas4RuGLwNMyGdJfT 3ePcBERdOfpqtNU2Va8BPVRAp1GrM2EnEh2BKCdhxsPXuh5ti7BbWIV10N68sNINaR4q ML0AwksZt+PzqQwv/BUDOaKM91TQ9DHC41KIIzLJ3nu5abUQOM5hN/kzxpk1MFlp8bsp Tzig== X-Gm-Message-State: AOJu0YzPABOpD5CIZXU0uZCjyRIOTD9fzyRhodyUj5hKchM32Rt4Cul0 9288dqk3Vha4vjm7TQk9hCvYgsLxvw8= X-Google-Smtp-Source: AGHT+IFpaSeob6DANsnfYS86DRxayEYeqxd8mw+Jsr7F2xJNMGDxnr87ZoqQUVT1+6KhIpKqQtqR7g== X-Received: by 2002:a25:ae43:0:b0:d9a:bd07:cb7d with SMTP id g3-20020a25ae43000000b00d9abd07cb7dmr3793265ybe.45.1697584002131; Tue, 17 Oct 2023 16:06:42 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:7c18:e478:1f0:7aca? ([2600:1700:bd2c:b810:7c18:e478:1f0:7aca]) by smtp.googlemail.com with ESMTPSA id b34-20020a25aea2000000b00c64533e4e20sm868344ybj.33.2023.10.17.16.06.41 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 17 Oct 2023 16:06:41 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------Ro0uGstkXmRtC34JiNiOfJP0" Message-ID: <53c6f645-6b24-4594-b1c9-fa6cf8b514da@gmail.com> Date: Tue, 17 Oct 2023 18:06:41 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Need efficient way to remove (almost) duplicate rows from a table Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------Ro0uGstkXmRtC34JiNiOfJP0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit https://dba.stackexchange.com/a/138327/63913 On 10/17/23 17:48, David Gauthier wrote: > 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 ! -- Born in Arizona, moved to Babylonia. --------------Ro0uGstkXmRtC34JiNiOfJP0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit https://dba.stackexchange.com/a/138327/63913

On 10/17/23 17:48, David Gauthier wrote:
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 !

--
Born in Arizona, moved to Babylonia.
--------------Ro0uGstkXmRtC34JiNiOfJP0--