Search Postgresql Archives

Re: Delete Duplicates with Using

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux