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 "conversationUsers"(
"conversationId" int
, "userId" int
, PRIMARY KEY ("userId", "conversationId")
);
INSERT INTO "conversationUsers" VALUES
(1, 1), (1,4), (1,6)
, (2, 1), (2,4), (2,6)
, (3, 1), (3,4), (3,6), (3,7) -- additional user
, (4, 1), (4,6) -- missing user
, (5, 7), (5,9) -- unrelated
;
14 rows affected
CREATE MATERIALIZED VIEW mv_conversation_users AS
SELECT "conversationId", array_agg("userId") AS users -- sorted array
FROM (
SELECT "conversationId", "userId"
FROM "conversationUsers"
ORDER BY 1, 2
) sub
GROUP BY 1
ORDER BY 1;
CREATE INDEX ON mv_conversation_users (users) INCLUDE ("conversationId");
5 rows affected
SELECT "conversationId"
FROM mv_conversation_users c
WHERE users = '{1,4,6}'::int[]; -- sorted array!
| conversationId |
|---|
| 1 |
| 2 |