Postgres 16

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 the masters table
CREATE TABLE masters (
    ID SERIAL PRIMARY KEY,
    Reference VARCHAR(50),
    CustomFields JSONB
);

-- Insert sample data into the masters table
INSERT INTO masters (Reference, CustomFields)
VALUES
    ('John', '[{"ID": 17, "NumericValue": 1234}, {"ID": 21, "DateValue": "2023-06-14T00:00:00Z"}, {"ID": 24, "BooleanValue": false}]'),
    ('Smith', '[{"ID": 17, "NumericValue": 1234}, {"ID": 12, "DateValue": "2023-04-05T00:00:00Z"}, {"ID": 15, "ListItemValue": "india"}]');

CREATE TABLE

INSERT 0 2

select  * from masters

id
reference
customfields

1
John
[{"ID": 17, "NumericValue": 1234}, {"ID": 21, "DateValue": "2023-06-14T00:00:00Z"}, {"ID": 24, "BooleanValue": false}]

2
Smith
[{"ID": 17, "NumericValue": 1234}, {"ID": 12, "DateValue": "2023-04-05T00:00:00Z"}, {"ID": 15, "ListItemValue": "india"}]

SELECT 2

SELECT id, reference, CustomFields 
FROM masters
WHERE CustomFields @? '$[*] ? (@.DateValue > "2023-05-22T03:09:37Z")';

id
reference
customfields

1
John
[{"ID": 17, "NumericValue": 1234}, {"ID": 21, "DateValue": "2023-06-14T00:00:00Z"}, {"ID": 24, "BooleanValue": false}]

SELECT 1

id	reference	customfields
1	John	[{"ID": 17, "NumericValue": 1234}, {"ID": 21, "DateValue": "2023-06-14T00:00:00Z"}, {"ID": 24, "BooleanValue": false}]
2	Smith	[{"ID": 17, "NumericValue": 1234}, {"ID": 12, "DateValue": "2023-04-05T00:00:00Z"}, {"ID": 15, "ListItemValue": "india"}]