Postgres 16

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 party(
    id int generated always as identity PRIMARY KEY,
    family_name varchar(50) NOT NULL
);
insert into party (family_name) values
  ('Green/Ecologist'),
  ('Green/Ecologist'),
  ('Family name 1'),
  ('Family name 2')
returning *;

create type election_type as enum (
'parliament',
'presidential'
);

CREATE TABLE election(
    id int,
    country_name varchar(50) NOT NULL,
    e_type election_type NOT NULL default 'parliament',
    e_date date NOT NULL,
    vote_share numeric,
    seats int,
    seats_total int NOT NULL default 0,
    party_name_short varchar(10) NOT NULL default 'p_name_s',
    party_name varchar(255) NOT NULL default 'party_name',
    party_name_english varchar(255) NOT NULL default 'party_name_english',
    party_id int REFERENCES party(id)
);

insert into election (country_name,e_date,vote_share,party_id) values
('Austria','1983-02-02','1.4',1),
('Austria','1983-02-02','2.0',2),
('Austria','1986-02-02','4.8',1),
('Austria','1990-02-02','4.8',1),
('Austria','1990-02-02','2.0',2),
('Austria','1994-02-02','7.3',1),
('Austria','1995-02-02','4.8',1),
('Austria','1999-02-02','7.4',1)
returning *

CREATE TABLE

id	family_name
1	Green/Ecologist
2	Green/Ecologist
3	Family name 1
4	Family name 2

INSERT 0 4

CREATE TYPE

CREATE TABLE

id	country_name	e_type	e_date	vote_share	seats	party_name_short	party_name	party_name_english	party_id
null	Austria	parliament	1983-02-02	1.4	null	p_name_s	party_name	party_name_english	1
null	Austria	parliament	1983-02-02	2.0	null	p_name_s	party_name	party_name_english	2
null	Austria	parliament	1986-02-02	4.8	null	p_name_s	party_name	party_name_english	1
null	Austria	parliament	1990-02-02	4.8	null	p_name_s	party_name	party_name_english	1
null	Austria	parliament	1990-02-02	2.0	null	p_name_s	party_name	party_name_english	2
null	Austria	parliament	1994-02-02	7.3	null	p_name_s	party_name	party_name_english	1
null	Austria	parliament	1995-02-02	4.8	null	p_name_s	party_name	party_name_english	1
null	Austria	parliament	1999-02-02	7.4	null	p_name_s	party_name	party_name_english	1

INSERT 0 8

country_name	decade	vote_share
Austria	1980s	4.1000000000000000
Austria	1990s	6.5750000000000000

SELECT 2