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 trade (
    id serial primary key,
    year int,
    month int,
    "data" json
);

insert into trade ("year", "month", "data") VALUES (
  2018, 1, '{"2": {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}, "3": {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}}'::json
);

insert into trade ("year", "month", "data") VALUES (
  2018, 2, '{"1": {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}, "2": {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}}'::json
);
1 rows affected

1 rows affected

SELECT
 t.prices,
 make_date("year", "month", t.day::int) as date
FROM
trade
JOIN json_each(trade.data) t(day, prices) ON TRUE
WHERE
("year"|| lpad( "month"::text,2,'0') || lpad(t.day,2,'0') )::DATE
IN ( select 
           generate_series(DATE '2018-01-1',
                   DATE '2018-01-03',INTERVAL '1 DAY') ::DATE
);


prices
date


{"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}
2018-01-02

{"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}
2018-01-03


select generate_series(DATE '2018-01-1', DATE '2018-01-03',INTERVAL '1 DAY') :: date;


generate_series


2018-01-01

2018-01-02

2018-01-03

prices	date
{"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}	2018-01-02
{"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}	2018-01-03