Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE test (
    col1 text,
    col2 text,
    col3 text
);

INSERT INTO test VALUES
( 'a', 'a', 'a'),
( 'b', 'b', 'b'),
( 'a', 'a', 'a'),
( 'c', 'c', 'c'),
( 'c', 'c', 'c'),
( 'a', 'a', 'a');

INSERT 0 6

EXPLAIN ANALYZE DELETE 
FROM test
WHERE ctid IN 
(
    SELECT ctid 
    FROM(
        SELECT 
            *, 
            ctid,
            row_number() OVER (PARTITION BY col1, col2, col3 ORDER BY ctid) 
        FROM test
    )s
    WHERE row_number >= 2
)

QUERY PLAN

Delete on test  (cost=126.53..133.04 rows=325 width=132) (actual time=0.381..0.381 rows=0 loops=1)

  ->  Merge Semi Join  (cost=126.53..133.04 rows=325 width=132) (actual time=0.365..0.369 rows=3 loops=1)

        Merge Cond: (test.ctid = s.ctid)

        ->  Sort  (cost=46.87..48.49 rows=650 width=6) (actual time=0.067..0.070 rows=6 loops=1)

              Sort Key: test.ctid

              Sort Method: quicksort  Memory: 25kB

              ->  Seq Scan on test  (cost=0.00..16.50 rows=650 width=6) (actual time=0.012..0.015 rows=6 loops=1)

        ->  Sort  (cost=79.67..80.21 rows=217 width=132) (actual time=0.291..0.291 rows=3 loops=1)

              Sort Key: s.ctid

              Sort Method: quicksort  Memory: 25kB

              ->  Subquery Scan on s  (cost=46.87..71.24 rows=217 width=132) (actual time=0.211..0.225 rows=3 loops=1)

                    Filter: (s.row_number >= 2)

                    Rows Removed by Filter: 3

                    ->  WindowAgg  (cost=46.87..63.12 rows=650 width=110) (actual time=0.146..0.162 rows=6 loops=1)

                          ->  Sort  (cost=46.87..48.49 rows=650 width=102) (actual time=0.115..0.117 rows=6 loops=1)

                                Sort Key: test_1.col1, test_1.col2, test_1.col3, test_1.ctid

                                Sort Method: quicksort  Memory: 25kB

                                ->  Seq Scan on test test_1  (cost=0.00..16.50 rows=650 width=102) (actual time=0.005..0.005 rows=6 loops=1)

Planning time: 1.689 ms

Execution time: 0.956 ms

EXPLAIN

EXPLAIN ANALYZE DELETE FROM test WHERE test.ctid NOT IN 
(SELECT ctid FROM (
    SELECT DISTINCT ON (col1, col2) ctid,*
  FROM test)s);

QUERY PLAN

Delete on test  (cost=46.87..2786.87 rows=325 width=6) (actual time=0.056..0.056 rows=0 loops=1)

  ->  Seq Scan on test  (cost=46.87..2786.87 rows=325 width=6) (actual time=0.054..0.054 rows=0 loops=1)

        Filter: (NOT (SubPlan 1))

        Rows Removed by Filter: 3

        SubPlan 1

          ->  Materialize  (cost=46.87..54.74 rows=200 width=6) (actual time=0.014..0.014 rows=2 loops=3)

                ->  Subquery Scan on s  (cost=46.87..53.74 rows=200 width=6) (actual time=0.026..0.028 rows=3 loops=1)

                      ->  Unique  (cost=46.87..51.74 rows=200 width=102) (actual time=0.025..0.027 rows=3 loops=1)

                            ->  Sort  (cost=46.87..48.49 rows=650 width=102) (actual time=0.025..0.026 rows=3 loops=1)

                                  Sort Key: test_1.col1, test_1.col2

                                  Sort Method: quicksort  Memory: 25kB

                                  ->  Seq Scan on test test_1  (cost=0.00..16.50 rows=650 width=102) (actual time=0.005..0.006 rows=3 loops=1)

Planning time: 0.071 ms

Execution time: 0.133 ms

EXPLAIN

select ctid from (
    select  distinct on (col1, col2) *, ctid 
    from test ou
    where (
        select count(*) from test inr
        where inr.col1= ou.col1 and inr.col2=ou.col2) > 1)s

ctid

SELECT 0

EXPLAIN ANALYZE delete from test ju where ju.ctid in 
(select ctid from (
    select  distinct on (col1, col2) *, ctid 
    from test ou
    where (
        select count(*) from test inr
        where inr.col1= ou.col1 and inr.col2=ou.col2) > 1)s)

QUERY PLAN

Delete on test ju  (cost=12927.43..12932.88 rows=147 width=132) (actual time=0.165..0.165 rows=0 loops=1)

  ->  Merge Semi Join  (cost=12927.43..12932.88 rows=147 width=132) (actual time=0.164..0.164 rows=0 loops=1)

        Merge Cond: (ju.ctid = s.ctid)

        ->  Sort  (cost=46.87..48.49 rows=650 width=6) (actual time=0.062..0.062 rows=1 loops=1)

              Sort Key: ju.ctid

              Sort Method: quicksort  Memory: 25kB

              ->  Seq Scan on test ju  (cost=0.00..16.50 rows=650 width=6) (actual time=0.007..0.009 rows=3 loops=1)

        ->  Sort  (cost=12880.56..12880.93 rows=147 width=132) (actual time=0.101..0.101 rows=0 loops=1)

              Sort Key: s.ctid

              Sort Method: quicksort  Memory: 25kB

              ->  Subquery Scan on s  (cost=12872.17..12875.27 rows=147 width=132) (actual time=0.072..0.072 rows=0 loops=1)

                    ->  Unique  (cost=12872.17..12873.80 rows=147 width=102) (actual time=0.072..0.072 rows=0 loops=1)

                          ->  Sort  (cost=12872.17..12872.71 rows=217 width=102) (actual time=0.070..0.070 rows=0 loops=1)

                                Sort Key: ou.col1, ou.col2

                                Sort Method: quicksort  Memory: 25kB

                                ->  Seq Scan on test ou  (cost=0.00..12863.75 rows=217 width=102) (actual time=0.032..0.032 rows=0 loops=1)

                                      Filter: ((SubPlan 1) > 1)

                                      Rows Removed by Filter: 3

                                      SubPlan 1

                                        ->  Aggregate  (cost=19.75..19.76 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=3)

                                              ->  Seq Scan on test inr  (cost=0.00..19.75 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=3)

                                                    Filter: ((col1 = ou.col1) AND (col2 = ou.col2))

                                                    Rows Removed by Filter: 2

Planning time: 0.152 ms

Execution time: 0.333 ms

EXPLAIN

EXPLAIN ANALYZE delete from test a
using test b 
where a.ctid < b.ctid
and a.col1=b.col1
and a.col2=b.col2
and a.col3=b.col3

QUERY PLAN

Delete on test a  (cost=93.74..106.75 rows=1 width=12) (actual time=0.142..0.142 rows=0 loops=1)

  ->  Merge Join  (cost=93.74..106.75 rows=1 width=12) (actual time=0.140..0.140 rows=0 loops=1)

        Merge Cond: ((a.col1 = b.col1) AND (a.col2 = b.col2) AND (a.col3 = b.col3))

        Join Filter: (a.ctid < b.ctid)

        Rows Removed by Join Filter: 3

        ->  Sort  (cost=46.87..48.49 rows=650 width=102) (actual time=0.060..0.061 rows=3 loops=1)

              Sort Key: a.col1, a.col2, a.col3

              Sort Method: quicksort  Memory: 25kB

              ->  Seq Scan on test a  (cost=0.00..16.50 rows=650 width=102) (actual time=0.007..0.009 rows=3 loops=1)

        ->  Sort  (cost=46.87..48.49 rows=650 width=102) (actual time=0.072..0.072 rows=3 loops=1)

              Sort Key: b.col1, b.col2, b.col3

              Sort Method: quicksort  Memory: 25kB

              ->  Seq Scan on test b  (cost=0.00..16.50 rows=650 width=102) (actual time=0.005..0.006 rows=3 loops=1)

Planning time: 0.113 ms

Execution time: 0.262 ms

EXPLAIN