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 SCHEMA IF NOT EXISTS resources;

-- resources.resource definition

-- Drop table

-- DROP TABLE resources.resource;

CREATE TABLE resources.resource (
    id uuid NOT NULL,
    children jsonb NULL,
    ctx varchar(255) NULL,
    parentclass varchar(255) NULL,
    parentid uuid NULL,
    resourceclass varchar(255) NULL,
    resourcetype varchar(255) NULL,
    status varchar(50) NULL,
    CONSTRAINT resource_pkey PRIMARY KEY (id)
);

-- resources.userresourcemask definition

-- Drop table

-- DROP TABLE resources.userresourcemask;

CREATE TABLE resources.userresourcemask (
    id int8 NOT NULL,
    mask varchar(50) NULL,
    username varchar(255) NULL,
    resource_id uuid NULL,
    CONSTRAINT userresourcemask_pkey PRIMARY KEY (id)
);

-- resources.userresourcemask foreign keys

ALTER TABLE resources.userresourcemask ADD CONSTRAINT fk5vjgr74nhjgovctjtt2qifsu7 FOREIGN KEY (resource_id) REFERENCES resources.resource(id);

INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('53d0dd59-903f-4599-b6d0-3940ec3fd8c1'::uuid, NULL, 'CTX', 'CONTAINER', NULL, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('ce1d4807-f4c3-46dd-b1c5-04ab91004324'::uuid, NULL, 'CTX', 'CONTAINER', '53d0dd59-903f-4599-b6d0-3940ec3fd8c1'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('2c72c007-9d10-4218-8f80-bc9a835d6ad5'::uuid, NULL, 'CTX', 'CONTAINER', 'ce1d4807-f4c3-46dd-b1c5-04ab91004324'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('68d4ee65-fbb0-462d-8adf-7b295ba442a1'::uuid, NULL, 'CTX', 'CONTAINER', '2c72c007-9d10-4218-8f80-bc9a835d6ad5'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('b4d1ff43-998a-4433-b8c3-4649986aaa14'::uuid, NULL, 'CTX', 'CONTAINER', '68d4ee65-fbb0-462d-8adf-7b295ba442a1'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('59948041-0c68-453d-a93c-94dd349d627a'::uuid, NULL, 'CTX', 'CONTAINER', 'b4d1ff43-998a-4433-b8c3-4649986aaa14'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('3db044dc-724f-46df-bc5e-41d29069dbb7'::uuid, NULL, 'CTX', 'CONTAINER', '59948041-0c68-453d-a93c-94dd349d627a'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('262ce8a9-fbf8-4333-8738-797e167a06a6'::uuid, NULL, 'CTX', 'CONTAINER', '3db044dc-724f-46df-bc5e-41d29069dbb7'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('3ed8c0a4-93a6-466f-b120-36985e153afa'::uuid, NULL, 'CTX', 'CONTAINER', '262ce8a9-fbf8-4333-8738-797e167a06a6'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('56f26cec-37eb-4d51-8714-58864d60a794'::uuid, NULL, 'CTX', 'CONTAINER', '3ed8c0a4-93a6-466f-b120-36985e153afa'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('e7bf49e4-ff6e-4869-998e-412b0b6e8a55'::uuid, NULL, 'CTX', 'CONTAINER', '56f26cec-37eb-4d51-8714-58864d60a794'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('75ac78a2-579e-43c5-a202-7ea6dae6d026'::uuid, NULL, 'CTX', 'CONTAINER', 'e7bf49e4-ff6e-4869-998e-412b0b6e8a55'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('1e2206dc-17b1-463d-92e8-931e3ce9fec8'::uuid, NULL, 'CTX', 'CONTAINER', '75ac78a2-579e-43c5-a202-7ea6dae6d026'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('1b6d5d35-f266-42a0-896c-40f36a1ed1c8'::uuid, NULL, 'CTX', 'CONTAINER', '1e2206dc-17b1-463d-92e8-931e3ce9fec8'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('892cd856-d854-4804-b856-3cf25611c93c'::uuid, NULL, 'CTX', 'CONTAINER', '1b6d5d35-f266-42a0-896c-40f36a1ed1c8'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('8c1a935f-97ca-46cf-9362-24215e588b32'::uuid, NULL, 'CTX', 'CONTAINER', '892cd856-d854-4804-b856-3cf25611c93c'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('51d99087-c0e6-4d5b-9c2e-7e3a820e2fff'::uuid, NULL, 'CTX', 'CONTAINER', '8c1a935f-97ca-46cf-9362-24215e588b32'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('364cdc26-dc53-41d1-9b76-a9bd5562e8be'::uuid, NULL, 'CTX', 'CONTAINER', '51d99087-c0e6-4d5b-9c2e-7e3a820e2fff'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('03562659-8ebb-41bf-8f57-c26760eda274'::uuid, NULL, 'CTX', 'CONTAINER', '364cdc26-dc53-41d1-9b76-a9bd5562e8be'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('399e51eb-ae49-47e1-8ea4-6195213329be'::uuid, NULL, 'CTX', 'CONTAINER', '03562659-8ebb-41bf-8f57-c26760eda274'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('010348c8-cc0e-48dd-aa06-fb2bcd91bc16'::uuid, NULL, 'CTX', 'CONTAINER', '399e51eb-ae49-47e1-8ea4-6195213329be'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('dcac77e0-e7a1-4db6-9e6c-bde883809711'::uuid, NULL, 'CTX', 'CONTAINER', '010348c8-cc0e-48dd-aa06-fb2bcd91bc16'::uuid, 'CONTAINER', '-', 'CREATED');
INSERT INTO resources.resource
(id, children, ctx, parentclass, parentid, resourceclass, resourcetype, status)
VALUES('026a1f8c-a2c5-4505-be12-3d5798d754bb'::uuid, NULL, 'CTX', 'CONTAINER', 'dcac77e0-e7a1-4db6-9e6c-bde883809711'::uuid, 'CONTAINER', '-', 'CREATED');

INSERT INTO resources.userresourcemask
(id, mask, username, resource_id)
VALUES(1, 'VIEWER', 'user', '53d0dd59-903f-4599-b6d0-3940ec3fd8c1'::uuid);
INSERT INTO resources.userresourcemask
(id, mask, username, resource_id)
VALUES(24, 'ADMIN', 'admin', '53d0dd59-903f-4599-b6d0-3940ec3fd8c1'::uuid);
INSERT INTO resources.userresourcemask
(id, mask, username, resource_id)
VALUES(25, 'EDITOR', 'admin', '56f26cec-37eb-4d51-8714-58864d60a794'::uuid);

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

id	parentid	mask
53d0dd59-903f-4599-b6d0-3940ec3fd8c1	null	{VIEWER,ADMIN}
ce1d4807-f4c3-46dd-b1c5-04ab91004324	53d0dd59-903f-4599-b6d0-3940ec3fd8c1	{VIEWER,ADMIN}
2c72c007-9d10-4218-8f80-bc9a835d6ad5	ce1d4807-f4c3-46dd-b1c5-04ab91004324	{VIEWER,ADMIN}
68d4ee65-fbb0-462d-8adf-7b295ba442a1	2c72c007-9d10-4218-8f80-bc9a835d6ad5	{VIEWER,ADMIN}
b4d1ff43-998a-4433-b8c3-4649986aaa14	68d4ee65-fbb0-462d-8adf-7b295ba442a1	{VIEWER,ADMIN}
59948041-0c68-453d-a93c-94dd349d627a	b4d1ff43-998a-4433-b8c3-4649986aaa14	{VIEWER,ADMIN}
3db044dc-724f-46df-bc5e-41d29069dbb7	59948041-0c68-453d-a93c-94dd349d627a	{VIEWER,ADMIN}
262ce8a9-fbf8-4333-8738-797e167a06a6	3db044dc-724f-46df-bc5e-41d29069dbb7	{VIEWER,ADMIN}
3ed8c0a4-93a6-466f-b120-36985e153afa	262ce8a9-fbf8-4333-8738-797e167a06a6	{VIEWER,ADMIN}
56f26cec-37eb-4d51-8714-58864d60a794	3ed8c0a4-93a6-466f-b120-36985e153afa	{VIEWER,ADMIN,EDITOR}
e7bf49e4-ff6e-4869-998e-412b0b6e8a55	56f26cec-37eb-4d51-8714-58864d60a794	{VIEWER,ADMIN,EDITOR}
75ac78a2-579e-43c5-a202-7ea6dae6d026	e7bf49e4-ff6e-4869-998e-412b0b6e8a55	{VIEWER,ADMIN,EDITOR}
1e2206dc-17b1-463d-92e8-931e3ce9fec8	75ac78a2-579e-43c5-a202-7ea6dae6d026	{VIEWER,ADMIN,EDITOR}
1b6d5d35-f266-42a0-896c-40f36a1ed1c8	1e2206dc-17b1-463d-92e8-931e3ce9fec8	{VIEWER,ADMIN,EDITOR}
892cd856-d854-4804-b856-3cf25611c93c	1b6d5d35-f266-42a0-896c-40f36a1ed1c8	{VIEWER,ADMIN,EDITOR}
8c1a935f-97ca-46cf-9362-24215e588b32	892cd856-d854-4804-b856-3cf25611c93c	{VIEWER,ADMIN,EDITOR}
51d99087-c0e6-4d5b-9c2e-7e3a820e2fff	8c1a935f-97ca-46cf-9362-24215e588b32	{VIEWER,ADMIN,EDITOR}
364cdc26-dc53-41d1-9b76-a9bd5562e8be	51d99087-c0e6-4d5b-9c2e-7e3a820e2fff	{VIEWER,ADMIN,EDITOR}
03562659-8ebb-41bf-8f57-c26760eda274	364cdc26-dc53-41d1-9b76-a9bd5562e8be	{VIEWER,ADMIN,EDITOR}
399e51eb-ae49-47e1-8ea4-6195213329be	03562659-8ebb-41bf-8f57-c26760eda274	{VIEWER,ADMIN,EDITOR}
010348c8-cc0e-48dd-aa06-fb2bcd91bc16	399e51eb-ae49-47e1-8ea4-6195213329be	{VIEWER,ADMIN,EDITOR}
dcac77e0-e7a1-4db6-9e6c-bde883809711	010348c8-cc0e-48dd-aa06-fb2bcd91bc16	{VIEWER,ADMIN,EDITOR}
026a1f8c-a2c5-4505-be12-3d5798d754bb	dcac77e0-e7a1-4db6-9e6c-bde883809711	{VIEWER,ADMIN,EDITOR}