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 mytable (
    uuid int,
    answers jsonb
);

INSERT INTO mytable VALUES
(1, '[{ "user" : "1", "ans" : "some text 1" }, { "user" : "3", "ans": "some text 3"}]');

SELECT * FROM mytable

CREATE TABLE

INSERT 0 1

uuid
answers

1
[{"ans": "some text 1", "user": "1"}, {"ans": "some text 3", "user": "3"}]

SELECT 1

SELECT
    elements ->> 'ans'
FROM
    mytable,
    jsonb_array_elements(answers) elements
WHERE 
    elements ->> 'user' = '3'

?column?

some text 3

SELECT 1

SELECT
    uuid,
    elements ->> 'ans'
FROM
    mytable,
    jsonb_array_elements(answers) elements
WHERE 
    elements ->> 'user' = '3'

uuid
?column?

1
some text 3

SELECT 1