Postgres 14

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 device_group_v2
(
    id        uuid not null primary key,
    name      varchar(255),
    filter    jsonb
);
create table device_entity
(
    id       uuid not null primary key,
    name     varchar(255),
    metadata jsonb
);

CREATE TABLE



CREATE TABLE


INSERT INTO device_group_v2 (id, name, filter)
VALUES('92f9ac68-7baa-4322-8eb1-e6b9e83dd2ef','Devices-UK','[{"key": "country", "value": "uk"}]');

INSERT INTO device_entity (id, name, metadata)
VALUES ('75cf91d0-01b7-42e0-9791-86def0dc4b7b','Device 7 - Metadata','{"country": "uk"}');


INSERT 0 1



INSERT 0 1


SELECT e.*
FROM device_group_v2 g
JOIN device_entity e
  ON NOT EXISTS (
    SELECT f."key", f.value
    FROM jsonb_to_recordset(g.filter) AS f("key" text, value text)

    EXCEPT

    SELECT md."key", md.value
    FROM jsonb_each_text(e.metadata) md
    )
WHERE g.name = 'Devices-UK';


id
name
metadata


75cf91d0-01b7-42e0-9791-86def0dc4b7b
Device 7 - Metadata
{"country": "uk"}



SELECT 1