Postgres 9.6

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 foo (
    uuid int,
    info jsonb
);

INSERT INTO foo VALUES
(1, '{"bar":[6, 7]}'),
(2, '{}'),
(3, '{"bar":[2]}'),
(4, '{"bar":[1, 3]}');

SELECT * FROM foo
4 rows affected



uuid
info


1
{"bar": [6, 7]}

2
{}

3
{"bar": [2]}

4
{"bar": [1, 3]}


SELECT
    elements::int AS uuid
FROM
    foo,
    jsonb_array_elements_text(info -> 'bar') elements
WHERE uuid = 4  


uuid


1

3


SELECT 
    *
FROM foo
WHERE uuid IN (
    SELECT
        elements::int
    FROM
        foo,
        jsonb_array_elements_text(info -> 'bar') elements
    WHERE uuid = 4  
)


uuid
info


1
{"bar": [6, 7]}

3
{"bar": [2]}