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 random_table (
    random_field JSONB
);
INSERT INTO random_table (random_field)
VALUES ('{"bar": [[[1, 2, 3, 4]]], "foo": [[["abc", "def"], ["tuw", "xyz"]]]}');
1 rows affected

SELECT *
FROM random_table
    LEFT OUTER JOIN LATERAL (
        SELECT *
        FROM jsonb_array_elements(random_table.random_field -> 'foo')
            WITH ORDINALITY AS foo_level_1(foo_1, foo_1_index)
        LEFT OUTER JOIN LATERAL (
            SELECT *
            FROM jsonb_array_elements(foo_1)
                WITH ORDINALITY AS foo_level_2(foo_2, foo_2_index)
            --LEFT OUTER JOIN LATERAL (
            --    SELECT *
            --    FROM jsonb_array_elements(foo_2)
            --        WITH ORDINALITY AS foo_level_3(foo_3, foo_3_index)
            --) AS foo_lateral_2 ON TRUE
        ) AS foo_lateral_1 ON TRUE

        FULL OUTER JOIN jsonb_array_elements(random_table.random_field -> 'bar')
            WITH ORDINALITY AS bar_level_1(bar_1, bar_1_index) ON FALSE
        LEFT OUTER JOIN LATERAL (
            SELECT *
            FROM jsonb_array_elements(bar_1)
                WITH ORDINALITY AS bar_level_2(bar_2, bar_2_index)
        ) AS bar_lateral_1 ON TRUE
    ) AS pouet ON TRUE;


random_field
foo_1
foo_1_index
foo_2
foo_2_index
bar_1
bar_1_index
bar_2
bar_2_index


{"bar": [[[1, 2, 3, 4]]], "foo": [[["abc", "def"], ["tuw", "xyz"]]]}
null
null
null
null
[[1, 2, 3, 4]]
1
[1, 2, 3, 4]
1

{"bar": [[[1, 2, 3, 4]]], "foo": [[["abc", "def"], ["tuw", "xyz"]]]}
[["abc", "def"], ["tuw", "xyz"]]
1
["abc", "def"]
1
null
null
null
null

{"bar": [[[1, 2, 3, 4]]], "foo": [[["abc", "def"], ["tuw", "xyz"]]]}
[["abc", "def"], ["tuw", "xyz"]]
1
["tuw", "xyz"]
2
null
null
null
null

random_field	foo_1	foo_1_index	foo_2	foo_2_index	bar_1	bar_1_index	bar_2	bar_2_index
{"bar": [[[1, 2, 3, 4]]], "foo": [[["abc", "def"], ["tuw", "xyz"]]]}	null	null	null	null	[[1, 2, 3, 4]]	1	[1, 2, 3, 4]	1
{"bar": [[[1, 2, 3, 4]]], "foo": [[["abc", "def"], ["tuw", "xyz"]]]}	[["abc", "def"], ["tuw", "xyz"]]	1	["abc", "def"]	1	null	null	null	null
{"bar": [[[1, 2, 3, 4]]], "foo": [[["abc", "def"], ["tuw", "xyz"]]]}	[["abc", "def"], ["tuw", "xyz"]]	1	["tuw", "xyz"]	2	null	null	null	null