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 staging
    ("staff_id" varchar(9), "staff_age" varchar(4), "staff_first_name" varchar(9), "staff_last_name" varchar(10), "staff_lang" varchar(13));

CREATE TABLE


INSERT INTO staging
    ("staff_id", "staff_age", "staff_first_name", "staff_last_name", "staff_lang")
VALUES
    ('18-31/55', '43', 'Marcellus', 'Lucas', NULL),
    ('18-31/55', '43', NULL, NULL, 'Guaraní'),
    ('18-31/55', NULL, 'Marcellus', 'Lucas', 'Guaraní'),
    ('22-98/02', '58', 'Franklin', 'Paul', NULL),
    ('22-98/02', '58', NULL, NULL, 'Filipino'),
    ('22-98/02', NULL, 'Franklin', 'Paul', 'Filipino'),
    ('24-21/43', '36', 'Mitchel', 'Newman', NULL),
    ('24-21/43', '36', NULL, NULL, 'Luxembourgish'),
    ('24-21/43', NULL, 'Mitchel', 'Newman', 'Luxembourgish'),
    ('24-62/82', '17', 'Hobert', 'Mckenzie', NULL),
    ('24-62/82', '17', NULL, NULL, 'Tamil'),
    ('24-62/82', NULL, 'Hobert', 'Mckenzie', 'Tamil'),
    ('33-45/91', '58', 'Malcom', 'Cunningham', NULL),
    ('33-45/91', '58', NULL, NULL, 'Belarusian'),
    ('33-45/91', NULL, 'Malcom', 'Cunningham', 'Belarusian'),
    ('34-35/30', '37', 'Louvenia', 'Black', NULL),
    ('34-35/30', '37', NULL, NULL, 'Nepali'),
    ('34-35/30', NULL, 'Louvenia', 'Black', 'Nepali'),
    ('49-13/30', '43', 'Charita', 'Chavez', NULL),
    ('49-13/30', '43', NULL, NULL, 'Gagauz'),
    ('49-13/30', NULL, 'Charita', 'Chavez', 'Gagauz'),
    ('83-99/74', '45', 'Myron', 'Ewing', NULL),
    ('83-99/74', '45', NULL, NULL, 'English'),
    ('83-99/74', NULL, 'Myron', 'Ewing', 'English'),
    ('84-14/62', '24', 'Millard', 'Leon', NULL),
    ('84-14/62', '24', NULL, NULL, 'Fijian'),
    ('84-14/62', NULL, 'Millard', 'Leon', 'Fijian'),
    ('91-35/32', '55', 'Cornelius', 'Goodwin', NULL),
    ('91-35/32', '55', NULL, NULL, 'Czech'),
    ('91-35/32', NULL, 'Cornelius', 'Goodwin', 'Czech'),
    (NULL, '17', 'Hobert', 'Mckenzie', 'Tamil'),
    (NULL, '24', 'Millard', 'Leon', 'Fijian'),
    (NULL, '36', 'Mitchel', 'Newman', 'Luxembourgish'),
    (NULL, '37', 'Louvenia', 'Black', 'Nepali'),
    (NULL, '43', 'Charita', 'Chavez', 'Gagauz'),
    (NULL, '43', 'Marcellus', 'Lucas', 'Guaraní'),
    (NULL, '45', 'Myron', 'Ewing', 'English'),
    (NULL, '55', 'Cornelius', 'Goodwin', 'Czech'),
    (NULL, '58', 'Franklin', 'Paul', 'Filipino'),
    (NULL, '58', 'Malcom', 'Cunningham', 'Belarusian');

INSERT 0 40


SELECT *
FROM staging
WHERE staff_id IS NOT NULL;


staff_id
staff_age
staff_first_name
staff_last_name
staff_lang


18-31/55
43
Marcellus
Lucas
null

18-31/55
43
null
null
Guaraní

18-31/55
null
Marcellus
Lucas
Guaraní

22-98/02
58
Franklin
Paul
null

22-98/02
58
null
null
Filipino

22-98/02
null
Franklin
Paul
Filipino

24-21/43
36
Mitchel
Newman
null

24-21/43
36
null
null
Luxembourgish

24-21/43
null
Mitchel
Newman
Luxembourgish

24-62/82
17
Hobert
Mckenzie
null

24-62/82
17
null
null
Tamil

24-62/82
null
Hobert
Mckenzie
Tamil

33-45/91
58
Malcom
Cunningham
null

33-45/91
58
null
null
Belarusian

33-45/91
null
Malcom
Cunningham
Belarusian

34-35/30
37
Louvenia
Black
null

34-35/30
37
null
null
Nepali

34-35/30
null
Louvenia
Black
Nepali

49-13/30
43
Charita
Chavez
null

49-13/30
43
null
null
Gagauz

49-13/30
null
Charita
Chavez
Gagauz

83-99/74
45
Myron
Ewing
null

83-99/74
45
null
null
English

83-99/74
null
Myron
Ewing
English

84-14/62
24
Millard
Leon
null

84-14/62
24
null
null
Fijian

84-14/62
null
Millard
Leon
Fijian

91-35/32
55
Cornelius
Goodwin
null

91-35/32
55
null
null
Czech

91-35/32
null
Cornelius
Goodwin
Czech



SELECT 30


/* since there is only one distinct value per staff_id
, you can GROUP BY staff_id and return the MAX value */
SELECT staff_id
  ,    MAX(staff_age) AS staff_age
  ,    MAX(staff_first_name) AS staff_first_name
  ,    MAX(staff_last_name) AS staff_last_name
  ,    MAX(staff_lang) AS staff_lang
FROM staging
WHERE staff_id IS NOT NULL
GROUP BY staff_id;


staff_id
staff_age
staff_first_name
staff_last_name
staff_lang


49-13/30
43
Charita
Chavez
Gagauz

24-21/43
36
Mitchel
Newman
Luxembourgish

84-14/62
24
Millard
Leon
Fijian

83-99/74
45
Myron
Ewing
English

34-35/30
37
Louvenia
Black
Nepali

33-45/91
58
Malcom
Cunningham
Belarusian

18-31/55
43
Marcellus
Lucas
Guaraní

91-35/32
55
Cornelius
Goodwin
Czech

24-62/82
17
Hobert
Mckenzie
Tamil

22-98/02
58
Franklin
Paul
Filipino



SELECT 10


/* ignore these, I suppose */
SELECT *
FROM staging
WHERE staff_id IS NULL;


staff_id
staff_age
staff_first_name
staff_last_name
staff_lang


null
17
Hobert
Mckenzie
Tamil

null
24
Millard
Leon
Fijian

null
36
Mitchel
Newman
Luxembourgish

null
37
Louvenia
Black
Nepali

null
43
Charita
Chavez
Gagauz

null
43
Marcellus
Lucas
Guaraní

null
45
Myron
Ewing
English

null
55
Cornelius
Goodwin
Czech

null
58
Franklin
Paul
Filipino

null
58
Malcom
Cunningham
Belarusian



SELECT 10

staff_id	staff_age	staff_first_name	staff_last_name	staff_lang
18-31/55	43	Marcellus	Lucas	null
18-31/55	43	null	null	Guaraní
18-31/55	null	Marcellus	Lucas	Guaraní
22-98/02	58	Franklin	Paul	null
22-98/02	58	null	null	Filipino
22-98/02	null	Franklin	Paul	Filipino
24-21/43	36	Mitchel	Newman	null
24-21/43	36	null	null	Luxembourgish
24-21/43	null	Mitchel	Newman	Luxembourgish
24-62/82	17	Hobert	Mckenzie	null
24-62/82	17	null	null	Tamil
24-62/82	null	Hobert	Mckenzie	Tamil
33-45/91	58	Malcom	Cunningham	null
33-45/91	58	null	null	Belarusian
33-45/91	null	Malcom	Cunningham	Belarusian
34-35/30	37	Louvenia	Black	null
34-35/30	37	null	null	Nepali
34-35/30	null	Louvenia	Black	Nepali
49-13/30	43	Charita	Chavez	null
49-13/30	43	null	null	Gagauz
49-13/30	null	Charita	Chavez	Gagauz
83-99/74	45	Myron	Ewing	null
83-99/74	45	null	null	English
83-99/74	null	Myron	Ewing	English
84-14/62	24	Millard	Leon	null
84-14/62	24	null	null	Fijian
84-14/62	null	Millard	Leon	Fijian
91-35/32	55	Cornelius	Goodwin	null
91-35/32	55	null	null	Czech
91-35/32	null	Cornelius	Goodwin	Czech