pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (67+ messages)  latest in thread

Message-ID: <20240203202947.u45hpyiwzuodbav2@hjp.at>
Permalink:  ../20240203202947.u45hpyiwzuodbav2@hjp.at/
Also on:    postgresql.org/message-id/20240203202947.u45hpyiwzuodbav2@hjp.at

 · 

reply

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