Postgres 9.6

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 bank_data
    (id int, date timestamp, asset int, liability int)
;

CREATE TABLE


INSERT INTO bank_data
    (id, date, asset, liability)
VALUES
    (1, '2001-06-30 00:00:00', 333860, 308524),
    (1, '2001-03-31 00:00:00', 336896, 311865),
    (1, '2001-09-30 00:00:00', 349343, 308524),
    (1, '2002-12-31 00:00:00', 353863, 322659),
    (2, '2001-06-30 00:00:00', 451297, 425156),
    (2, '2001-03-31 00:00:00', 411421, 391846),
    (2, '2001-09-30 00:00:00', 430178, 41356),
    (2, '2002-12-31 00:00:00', 481687, 46589),
    (3, '2001-06-30 00:00:00', 106506, 104532),
    (3, '2001-03-31 00:00:00', 104196, 102983),
    (3, '2001-09-30 00:00:00', 106383, 104865),
    (3, '2002-12-31 00:00:00', 107654, 105867)
;

INSERT 0 12


CREATE TABLE bank_sec
    (id int, date timestamp, security int)
;

CREATE TABLE


INSERT INTO bank_sec
    (id, date, security)
VALUES
    (1, '2001-06-30 00:00:00', 1),
    (1, '2001-03-31 00:00:00', 2),
    (1, '2001-09-30 00:00:00', 3),
    (1, '2002-12-31 00:00:00', 4),
    (2, '2001-06-30 00:00:00', 5),
    (2, '2001-03-31 00:00:00', 6),
    (2, '2001-09-30 00:00:00', 7),
    (2, '2002-12-31 00:00:00', 8),
    (3, '2001-06-30 00:00:00', 9),
    (3, '2001-03-31 00:00:00', 10),
    (3, '2001-09-30 00:00:00', 11),
    (3, '2002-12-31 00:00:00', 12)
;

INSERT 0 12


CREATE TABLE new_table ( 
       id int, date date, asset int, liability int, security int,
        CONSTRAINT bankkey PRIMARY KEY (id, date)
           ) ;
    

CREATE TABLE


    INSERT INTO new_table (id,date,asset,liability,security)
            SELECT s.id, 
               b.date, 
               b.asset, 
               b.liability, 
               s.security
             FROM bank_data b JOIN bank_sec s
             ON b.id = s.id AND b.date = s.date;

INSERT 0 12


select * from new_table;


id
date
asset
liability
security


1
2001-03-31
336896
311865
2

1
2001-06-30
333860
308524
1

1
2001-09-30
349343
308524
3

1
2002-12-31
353863
322659
4

2
2001-03-31
411421
391846
6

2
2001-06-30
451297
425156
5

2
2001-09-30
430178
41356
7

2
2002-12-31
481687
46589
8

3
2001-03-31
104196
102983
10

3
2001-06-30
106506
104532
9

3
2001-09-30
106383
104865
11

3
2002-12-31
107654
105867
12



SELECT 12

id	date	asset	liability	security
1	2001-03-31	336896	311865	2
1	2001-06-30	333860	308524	1
1	2001-09-30	349343	308524	3
1	2002-12-31	353863	322659	4
2	2001-03-31	411421	391846	6
2	2001-06-30	451297	425156	5
2	2001-09-30	430178	41356	7
2	2002-12-31	481687	46589	8
3	2001-03-31	104196	102983	10
3	2001-06-30	106506	104532	9
3	2001-09-30	106383	104865	11
3	2002-12-31	107654	105867	12