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 people (
    id serial primary key,
    travel jsonb
);

insert into people (travel) values (
'{
    "name": "Lucy",
    "trips": [
      {
        "continent": "Asia",
        "city": "Tokyo"
      },
      {
        "continent": "Asia",
        "city": "Bangkok"
      },
      {
        "continent": "Europe",
        "city": "Paris"
      },
      {
        "continent": "Europe",
        "city": "London"
      }
    ]
  }
'::jsonb);

insert into people (travel) values (
'{
    "name": "Tom",
    "trips": [
      {
        "continent": "Asia",
        "city": "Tokyo"
      },
      {
        "continent": "Asia",
        "city": "Kyoto"
      },
      {
        "continent": "Europe",
        "city": "Frankfurt"
      },
      {
        "continent": "Europe",
        "city": "London"
      }
    ]
  }
'::jsonb);

insert into people (travel) values (
'{
    "name": "Lenny",
    "trips": [
      {
        "continent": "Asia",
        "city": "Tokyo"
      },
      {
        "continent": "Asia",
        "city": "Bangkok"
      },
      {
        "continent": "America",
        "city": "New York"
      },
      {
        "continent": "America",
        "city": "Seattle"
      }
    ]
  }
'::jsonb);

SELECT * FROM people

1 rows affected

1 rows affected

1 rows affected

id	travel
1	{"name": "Lucy", "trips": [{"city": "Tokyo", "continent": "Asia"}, {"city": "Bangkok", "continent": "Asia"}, {"city": "Paris", "continent": "Europe"}, {"city": "London", "continent": "Europe"}]}
2	{"name": "Tom", "trips": [{"city": "Tokyo", "continent": "Asia"}, {"city": "Kyoto", "continent": "Asia"}, {"city": "Frankfurt", "continent": "Europe"}, {"city": "London", "continent": "Europe"}]}
3	{"name": "Lenny", "trips": [{"city": "Tokyo", "continent": "Asia"}, {"city": "Bangkok", "continent": "Asia"}, {"city": "New York", "continent": "America"}, {"city": "Seattle", "continent": "America"}]}

id	travel	value
1	{"name": "Lucy", "trips": [{"city": "Tokyo", "continent": "Asia"}, {"city": "Bangkok", "continent": "Asia"}, {"city": "Paris", "continent": "Europe"}, {"city": "London", "continent": "Europe"}]}	{"city": "Tokyo", "continent": "Asia"}
1	{"name": "Lucy", "trips": [{"city": "Tokyo", "continent": "Asia"}, {"city": "Bangkok", "continent": "Asia"}, {"city": "Paris", "continent": "Europe"}, {"city": "London", "continent": "Europe"}]}	{"city": "Bangkok", "continent": "Asia"}
2	{"name": "Tom", "trips": [{"city": "Tokyo", "continent": "Asia"}, {"city": "Kyoto", "continent": "Asia"}, {"city": "Frankfurt", "continent": "Europe"}, {"city": "London", "continent": "Europe"}]}	{"city": "Tokyo", "continent": "Asia"}
2	{"name": "Tom", "trips": [{"city": "Tokyo", "continent": "Asia"}, {"city": "Kyoto", "continent": "Asia"}, {"city": "Frankfurt", "continent": "Europe"}, {"city": "London", "continent": "Europe"}]}	{"city": "Kyoto", "continent": "Asia"}
3	{"name": "Lenny", "trips": [{"city": "Tokyo", "continent": "Asia"}, {"city": "Bangkok", "continent": "Asia"}, {"city": "New York", "continent": "America"}, {"city": "Seattle", "continent": "America"}]}	{"city": "Tokyo", "continent": "Asia"}
3	{"name": "Lenny", "trips": [{"city": "Tokyo", "continent": "Asia"}, {"city": "Bangkok", "continent": "Asia"}, {"city": "New York", "continent": "America"}, {"city": "Seattle", "continent": "America"}]}	{"city": "Bangkok", "continent": "Asia"}