Postgres 9.5

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 my_table(
    details JSON
);

CREATE TABLE


INSERT INTO my_table(details) VALUES('
    {
        "city": "London",
        "name": "Sainburry",
        "quantities": [112, 145, 222, 122, 124],
        "prices": [4, 4, 4, 0, 3],
        "dates": ["13.05.2020", "14.05.2020", "15.05.2020", "16.05.2020", "17.05.2020"]
    }
    ');

INSERT 0 1


SELECT to_date(d, 'DD.MM.YYYY') AS date -- ①
     , quantity, price
     , round(price / quantity, 4) AS ratio -- ③
FROM   my_table
CROSS  JOIN LATERAL ( -- ②
   SELECT json_array_elements_text(details->'dates'     )          AS d -- ①
        , json_array_elements_text(details->'quantities')::int     AS quantity -- ③
        , json_array_elements_text(details->'prices'    )::numeric AS price -- ③
   ) AS data
WHERE  details->>'city' = 'London';


date
quantity
price
ratio


2020-05-13
112
4
0.0357

2020-05-14
145
4
0.0276

2020-05-15
222
4
0.0180

2020-05-16
122
0
0.0000

2020-05-17
124
3
0.0242



SELECT 5

date	quantity	price	ratio
2020-05-13	112	4	0.0357
2020-05-14	145	4	0.0276
2020-05-15	222	4	0.0180
2020-05-16	122	0	0.0000
2020-05-17	124	3	0.0242