I have a table more than 1000000 rows, I am remove the duplicate data from PostgreSQL 13 using this SQL:

delete from sentence 
where ctid not in (
    select min(ctid) 
    from sentence 
    group by sentence_zh

but the SQL is too slow, and execute take too much time. I have already created an index like this:

CREATE INDEX sentence_zh_idx
ON sentence (sentence_zh);

what should I do to remove the duplicate data in this table more quickly? BTW, this is my table DDL:

CREATE TABLE public.sentence (
    word_id int8 NOT NULL,
    sentence_en varchar NOT NULL,
    sentence_zh varchar NOT NULL,
    created_time int8 NOT NULL,
    updated_time int8 NOT NULL,
    word varchar NOT NULL,
    CONSTRAINT sentence_pkey PRIMARY KEY (id)
CREATE INDEX index_dict_word_id ON public.sentence (word_id);
CREATE UNIQUE INDEX sentence_en_uniq ON public.sentence (sentence_en,word);
  • Try this solution Dec 3 '21 at 14:34
  • Do you have any idea how many duplicates there would be? Dec 3 '21 at 14:34
  • I think the duplicate may be less than 3000. @GerardH.Pille
    – Dolphin
    Dec 3 '21 at 14:35
  • Then the delete is best, as in the solution given above. Dec 3 '21 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.