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


SELECT 
    *, 
    ctid,
    row_number() OVER (PARTITION BY col1, col2, col3 ORDER BY ctid) 
FROM test


col1
col2
col3
ctid
row_number


a
a
a
(0,1)
1

a
a
a
(0,3)
2

a
a
a
(0,6)
3

b
b
b
(0,2)
1

c
c
c
(0,4)
1

c
c
c
(0,5)
2



SELECT 6


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


ctid


(0,3)

(0,6)

(0,5)



SELECT 3


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
)

DELETE 3


SELECT * FROM test


col1
col2
col3


a
a
a

b
b
b

c
c
c



SELECT 3