Postgres 12

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 the_table (models jsonb);

CREATE TABLE


insert into the_table
values
('{
   "cars":[
      {
         "manufacturer":"Tesla",
         "address":"Deer Creek Road Palo Alto",
         "contact":"support@tesla.com",
         "models":[
            {
               "model":"Model S",
               "starting_price":50000
            },
            {
               "model":"Model 3",
               "starting_price":35000
            },
            {
               "model":"Model X",
               "starting_price":70000
            }
         ]
      },
      {
         "manufacturer":"BMW",
         "address":"Petuelring 130, 80809 München",
         "contact":"support@bmw.com",
         "models":[
            {
               "model":"X1",
               "starting_price":35000
            },
            {
               "model":"X2",
               "starting_price":32000
            },
            {
               "model":"X3",
               "starting_price":39000
            },
            {
               "model":"Series 5",
               "starting_price":55000
            },
            {
               "model":"Series 3",
               "starting_price":50000
            }
         ]
      }
   ]
}'::jsonb);

INSERT 0 1


select x.m ->> 'manufacturer' as manufacturer,
       x.m ->> 'address' as address,
       x.m ->> 'contact' as contact,
       m.model ->> 'model' as model,
       (m.model ->> 'starting_price')::int as starting_price
from the_table t
  cross join jsonb_array_elements(t.models -> 'cars') as x(m)
  cross join jsonb_array_elements(x.m -> 'models') as m(model);


manufacturer
address
contact
model
starting_price


Tesla
Deer Creek Road Palo Alto
support@tesla.com
Model S
50000

Tesla
Deer Creek Road Palo Alto
support@tesla.com
Model 3
35000

Tesla
Deer Creek Road Palo Alto
support@tesla.com
Model X
70000

BMW
Petuelring 130, 80809 München
support@bmw.com
X1
35000

BMW
Petuelring 130, 80809 München
support@bmw.com
X2
32000

BMW
Petuelring 130, 80809 München
support@bmw.com
X3
39000

BMW
Petuelring 130, 80809 München
support@bmw.com
Series 5
55000

BMW
Petuelring 130, 80809 München
support@bmw.com
Series 3
50000



SELECT 8

manufacturer	address	contact	model	starting_price
Tesla	Deer Creek Road Palo Alto	support@tesla.com	Model S	50000
Tesla	Deer Creek Road Palo Alto	support@tesla.com	Model 3	35000
Tesla	Deer Creek Road Palo Alto	support@tesla.com	Model X	70000
BMW	Petuelring 130, 80809 München	support@bmw.com	X1	35000
BMW	Petuelring 130, 80809 München	support@bmw.com	X2	32000
BMW	Petuelring 130, 80809 München	support@bmw.com	X3	39000
BMW	Petuelring 130, 80809 München	support@bmw.com	Series 5	55000
BMW	Petuelring 130, 80809 München	support@bmw.com	Series 3	50000