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,
|
