From: Peter J. Holzer <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: How to do faster DML
Date: Sat, 3 Feb 2024 21:29:47 +0100
Message-ID: <20240203202947.u45hpyiwzuodbav2@hjp.at> (raw)
In-Reply-To: <CAKna9VbKVzw_rojHg1wPMhNAh3KZX7Rw-aY6ZOuaqLa3mGqDqw@mail.gmail.com>
References: <CAKna9VbS8qeUj1NpzodAxgOEJGp+g0zE9BfEsoGvYgL7Jr+CnQ@mail.gmail.com>
<CAKna9VbKVzw_rojHg1wPMhNAh3KZX7Rw-aY6ZOuaqLa3mGqDqw@mail.gmail.com>
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 need the
> index on Id column then.
>
> But, it still runs long, so thinking any other way to make the duplicate
> removal faster?
>
> Also wondering , the index creation which took ~2.5hrs+ , would that have been
> made faster any possible way by allowing more db resource through some session
> level db parameter setting?
>
> create table TAB1_New
> as
> SELECT * from TAB1 A
> where CTID in
> (select min(CTID) from TAB1
> group by ID having count(ID)>=1 );
That »having count(ID)>=1« seems redundant to me. Surely every id which
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
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: hjp-pgsql@hjp.at
Subject: Re: How to do faster DML
In-Reply-To: <20240203202947.u45hpyiwzuodbav2@hjp.at>
References: <CAKna9VbS8qeUj1NpzodAxgOEJGp+g0zE9BfEsoGvYgL7Jr+CnQ@mail.gmail.com> <CAKna9VbKVzw_rojHg1wPMhNAh3KZX7Rw-aY6ZOuaqLa3mGqDqw@mail.gmail.com> <20240203202947.u45hpyiwzuodbav2@hjp.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox