From: | "Igal (at) Lucee(dot)org" <igal(at)lucee(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Delete Duplicates with Using |
Date: | 2017-10-16 17:28:11 |
Message-ID: | b9f92384-9582-f06e-6cdc-4a1191317d4b@lucee.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
FYI,
On 10/16/2017 8:58 AM, Igal @ Lucee.org wrote:
>
>>
>> test=*# with keep as (select max(ctid) as ctid from dubletten group
>> by c1,c2,c3) delete from dubletten where ctid not in (select ctid
>> from keep);
>>
>
> I like this solution, but would using a subquery be much slower than
> the implicit join of `using`? My tables are not big in
> Postgres-standards, so it's probably not an issue, but I'm trying to
> learn as much as I can about Postgres now that I'm getting ready to
> move it to production.
I was able to compare the performance on a table with about 350k rows,
with duplicates over 7 columns and no indices.
The GROUP BY solution with the subquery (though I was using a simpler
version of it without a CTE), was taking over 30 seconds so I killed the
request.
I then ran the USING version which completed in 16 seconds and deleted
39 rows.
Best,
Igal Sapir
Lucee Core Developer
Lucee.org <http://lucee.org/>
From | Date | Subject | |
---|---|---|---|
Next Message | Khalil Khamlichi | 2017-10-16 17:47:58 | Re: time series data |
Previous Message | Seamus Abshere | 2017-10-16 17:08:35 | Re: Non-overlapping updates blocking each other |