Postgres 11

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 JSONB );
INSERT INTO my_table  
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"]
}'::jsonb);
1 rows affected

   SELECT q.elm AS quantities, p.elm AS prices, 
          AVG(p.elm::float/q.elm::float) AS ratio
     FROM my_table t0
     LEFT JOIN LATERAL jsonb_array_elements(details -> 'quantities') 
       WITH ORDINALITY AS q(elm, i) ON TRUE
     LEFT JOIN LATERAL jsonb_array_elements(details -> 'prices') 
       WITH ORDINALITY AS p(elm, i) ON q.i = p.i
     LEFT JOIN LATERAL jsonb_array_elements(details -> 'dates') 
       WITH ORDINALITY AS d(elm, i) ON d.i = q.i
    WHERE t0.details ->> 'city' = 'London'   
    GROUP BY q.elm, p.elm
    


quantities
prices
ratio


122
0
0

112
4
0.0357142857142857

145
4
0.0275862068965517

124
3
0.0241935483870968

222
4
0.018018018018018

quantities	prices	ratio
122	0	0
112	4	0.0357142857142857
145	4	0.0275862068965517
124	3	0.0241935483870968
222	4	0.018018018018018