Postgres 10

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 additional_fields
(
    issue_id integer NOT NULL,
    issue_key character varying(16) COLLATE pg_catalog."default" NOT NULL,
    project_key character varying(16) COLLATE pg_catalog."default" NOT NULL,
    field_name character varying(20) COLLATE pg_catalog."default" NOT NULL,
    value_string character varying(256) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
    value_number numeric(55, 5) DEFAULT NULL::numeric,
    value_text text COLLATE pg_catalog."default",
    type character varying(20) COLLATE pg_catalog."default",
    is_long boolean DEFAULT false,
    modified_at timestamp without time zone,
    CONSTRAINT additional_fields_pkey PRIMARY KEY (issue_key, issue_id, field_name)
);
INSERT INTO additional_fields VALUES (1, 'X-1', 'TRANSL', 'customfield_12157', 'X-1', null, null, true);
INSERT INTO additional_fields VALUES (2, 'X-2', 'TRANSL', 'customfield_12157', 'X-3', null, null, true);
INSERT INTO additional_fields VALUES (3, 'X-3', 'TRANSL', 'customfield_12158', null, null, null, true);

INSERT INTO additional_fields VALUES (1, 'Y-1', 'TRANSL', 'customfield_12157', 'X-2', null, null, true);

INSERT INTO additional_fields VALUES (4, 'Z-1', 'TRANSL', 'customfield_12157', 'Z-2', null, null, true);
INSERT INTO additional_fields VALUES (5, 'Z-2', 'TRANSL', 'customfield_12157', null, null, null, true);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT * FROM additional_fields;


issue_id
issue_key
project_key
field_name
value_string
value_number
value_text
type
is_long
modified_at


1
X-1
TRANSL
customfield_12157
X-1
null
null
true
f
null

2
X-2
TRANSL
customfield_12157
X-3
null
null
true
f
null

3
X-3
TRANSL
customfield_12158
null
null
null
true
f
null

1
Y-1
TRANSL
customfield_12157
X-2
null
null
true
f
null

4
Z-1
TRANSL
customfield_12157
Z-2
null
null
true
f
null

5
Z-2
TRANSL
customfield_12157
null
null
null
true
f
null

issue_id	issue_key	project_key	field_name	value_string	value_number	value_text	type	is_long	modified_at
1	X-1	TRANSL	customfield_12157	X-1	null	null	true	f	null
2	X-2	TRANSL	customfield_12157	X-3	null	null	true	f	null
3	X-3	TRANSL	customfield_12158	null	null	null	true	f	null
1	Y-1	TRANSL	customfield_12157	X-2	null	null	true	f	null
4	Z-1	TRANSL	customfield_12157	Z-2	null	null	true	f	null
5	Z-2	TRANSL	customfield_12157	null	null	null	true	f	null