Postgres 13

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 table1 (
    year int,
    start date,
    "end" date
);

INSERT INTO table1 VALUES
(2019, '2019-01-01', '2019-12-31'),
(4, '2019-04-01', '2020-03-31');

SELECT * FROM table1;
2 rows affected



year
start
end


2019
2019-01-01
2019-12-31

4
2019-04-01
2020-03-31


CREATE TABLE crosstab_table (
    id int,
    other text,
    medicaid text,
    commercial text
);

INSERT INTO crosstab_table VALUES
(1, 'A', 'AA', 'AAA'),
(2, 'B', 'BB', 'BBB'),
(3, 'C', 'CC', 'CCC');

SELECT * FROM crosstab_table;
3 rows affected



id
other
medicaid
commercial


1
A
AA
AAA

2
B
BB
BBB

3
C
CC
CCC


CREATE TABLE table2 (
    id int,
    other text,
    medicaid text,
    commercial text,
    year int
);
INSERT INTO table2 (id, other, medicaid, commercial, year)
SELECT 
    ct.id, ct.other, ct.medicaid, ct.commercial, 
    s.year
FROM crosstab_table ct
CROSS JOIN (
    SELECT year FROM table1
) s;

SELECT * FROM table2
6 rows affected



id
other
medicaid
commercial
year


1
A
AA
AAA
2019

2
B
BB
BBB
2019

3
C
CC
CCC
2019

1
A
AA
AAA
4

2
B
BB
BBB
4

3
C
CC
CCC
4