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 1rWMeI-0052Ad-15 for pgsql-general@arkaria.postgresql.org; Sat, 03 Feb 2024 20:30:02 +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 1rWMeH-0035g9-9a for pgsql-general@arkaria.postgresql.org; Sat, 03 Feb 2024 20:30:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rWMeG-0035fx-VS for pgsql-general@lists.postgresql.org; Sat, 03 Feb 2024 20:30:00 +0000 Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rWMe6-004vAF-KR for pgsql-general@lists.postgresql.org; Sat, 03 Feb 2024 20:29:59 +0000 Received: by rorschach.hjp.at (Postfix, from userid 1000) id 4A41A1885F; Sat, 3 Feb 2024 21:29:47 +0100 (CET) Date: Sat, 3 Feb 2024 21:29:47 +0100 From: "Peter J. Holzer" To: pgsql-general@lists.postgresql.org Subject: Re: How to do faster DML Message-ID: <20240203202947.u45hpyiwzuodbav2@hjp.at> Mail-Followup-To: pgsql-general@lists.postgresql.org References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="ftdc7ray7jcifh4y" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --ftdc7ray7jcifh4y Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On 2024-02-03 19:25:12 +0530, Lok P wrote: > Apology. One correction, the query is like below. I. E filter will be on = on > ctid which I believe is equivalent of rowid in oracle and we will not nee= d the > index on Id column then.=A0 >=20 > =A0But, it still runs long, so thinking any other way to make the=A0dupli= cate > removal faster?=A0 >=20 > Also wondering , the index creation which took ~2.5hrs+ , would that have= been > made faster=A0any possible way by allowing more db resource through some = session > level db parameter setting? >=20 > create table TAB1_New > as > SELECT =A0* from TAB1 A > where CTID in > =A0 =A0 =A0 (select min(CTID) from TAB1 > =A0 =A0 =A0 group by ID having count(ID)>=3D1 ); That =BBhaving count(ID)>=3D1=AB seems redundant to me. Surely every id whi= ch occurs in the table occurs at least once? Since you want ID to be unique I assume that it is already almost unique - so only a small fraction of the ids will be duplicates. So I would start with creating a list of duplicates: create table tab1_dups as select id, count(*) from tab1 group by id having count(*) > 1; This will still take some time because it needs to build a temporary structure large enough to hold a count for each individual id. But at least then you'll have a much smaller table to use for further cleanup. hp --=20 _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" --ftdc7ray7jcifh4y Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmW+ojYACgkQ8g5IURL+ KF1fRw//e0kXHce88cvdxMJ3Sa2gkcSKV/ztVs6nWVgxhb5ApXAfzBHtYMYT+Jh+ UEmTgWsrX6nIGFNdaZ/vHVzHtRGWF4Fn+lMwc6huAeyis1DJnmVrfszfzcsupy8A WIhCI2XvemOaNJrTQiPKAbnEBa2K/7Xf8gWY8FKBhs6mqU3MPCrJCyMLXduXi/ZZ OkrueiL2LAINZnhAdvLdAZbS3EILo5hK4AYcZj5xb4us/zD08S3PHnC6+RHNie3y cR5bFr/lQFKrf2ivr2OcPvmAIqAOaM/vgRZYndEP8Awh/j9oJA/jN6K4dOt4a/Ey kP+wKienFGQuF+yNAYx3MV61iiQ46Kh7ZQCz0/LIkxX/lrPN+RJI1Bdrxcb4T2AS lLBaw1/B0H2soPy7FvGIg/4URqDSTBHr/7giwqB+QO1TdsnY4k2FEObOsB1cqmpU EgVJWVbhpaJ7jaxNgYp20dwiDUSJZrSY/aeoH0s7IxfuwrHtD7S9UmNOHbcWwhFA gjwd5tKHCEdkgxQu+q1SQ5BnDEIvESIRCTYyXkWl6Icv6aFbPgSJ43wXsj2HG2Tf RFCB0Vx2lFdOpO4TKl6ksrJLTxHKT+Bob5ZFzTFBBf8waQsJ4otuD++UmgrBwRiK n67l8twZrpZxe081t+1NR7OvDi28OFmVdHYfNFrJma5FZnmZc6U= =I/Q5 -----END PGP SIGNATURE----- --ftdc7ray7jcifh4y--