Postgres 9.4

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 votes
(
    voter varchar(10),
    election_year smallint,
    election_type varchar(2),
    party varchar(3)
);

CREATE TABLE


INSERT INTO votes
    (voter, election_year, election_type, party)
VALUES
    ('2435871347', 2018, 'PO', 'EV'),
    ('2435871347', 2018, 'RU', 'EV'),
    ('2435871347', 2018, 'GE', 'EV'),
    ('2435871347', 2016, 'PO', 'EV'),
    ('2435871347', 2016, 'GE', 'EV'),
    ('10215121/8', 2016, 'GE', 'ED');

INSERT 0 6


SELECT *
       FROM votes;


voter
election_year
election_type
party


2435871347
2018
PO
EV

2435871347
2018
RU
EV

2435871347
2018
GE
EV

2435871347
2016
PO
EV

2435871347
2016
GE
EV

10215121/8
2016
GE
ED



SELECT 6


WITH
cte
AS
(
SELECT ctid,
       row_number() OVER (PARTITION BY voter,
                                       election_year
                          ORDER BY voter) rn
       FROM votes
)
DELETE FROM votes
       USING cte
       WHERE cte.rn > 1
             AND cte.ctid = votes.ctid;

DELETE 3


SELECT *
       FROM votes;


voter
election_year
election_type
party


2435871347
2018
PO
EV

2435871347
2016
PO
EV

10215121/8
2016
GE
ED



SELECT 3

voter	election_year	election_type	party
2435871347	2018	PO	EV
2435871347	2018	RU	EV
2435871347	2018	GE	EV
2435871347	2016	PO	EV
2435871347	2016	GE	EV
10215121/8	2016	GE	ED