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 tbl (id serial, jsonb_column jsonb);
INSERT INTO tbl(jsonb_column) VALUES
('["UserMailer", "applicant_setup_3", ["5cbffeb7-8d5e-4b52-a475-3cf320b2cee9"]]')
, ('[{"reference_id": "5cbffeb7-8d5e-4b52-a475-3cf320b2cee9", "job_dictionary": ["StatusUpdater", "FollowTwitterUsersJob"]}]')
-- some non-matching rows:
, ('[{"reference_id": "6cbffeb7-8d5e-4b52-a475-3cf320b2cee9", "job_dictionary": ["StatusUpdater", "FollowTwitterUsersJob"]}]') -- wrong UUID
, ('{"5cbffeb7-8d5e-4b52-a475-3cf320b2cee": 123}'); -- key
CREATE INDEX tbl_jsonb_column_fts_gin_idx ON tbl USING GIN (to_tsvector('simple', jsonb_column));
CREATE TABLE
INSERT 0 4
CREATE INDEX
SELECT * FROM tbl
WHERE to_tsvector('simple', jsonb_column)
@@ tsquery '5cbffeb7-8d5e-4b52-a475-3cf320b2cee9';
| id | jsonb_column |
|---|---|
| 1 | ["UserMailer", "applicant_setup_3", ["5cbffeb7-8d5e-4b52-a475-3cf320b2cee9"]] |
| 2 | [{"reference_id": "5cbffeb7-8d5e-4b52-a475-3cf320b2cee9", "job_dictionary": ["StatusUpdater", "FollowTwitterUsersJob"]}] |
SELECT 2