Check query performance improvements

[patrickvenetz]

Replacing WHERE ARRAY[id] = ARRAY[a, b, c] with WHERE id = ANY(VALUES(a), (b), (c)) might improve query performance as it leverages primary key lookup.

EXPLAIN SELECT c.id
FROM credentials c
WHERE
  c."userId" = ANY (
    VALUES
      ('9f0466d4-a628-467a-bd82-7dc02683aa2d'::uuid),
      ('0358fb92-7690-4daa-a3a2-25b010a84c51'::uuid),
      ('1293fff6-9925-430f-85e5-4a59c65031a2'::uuid),
      ('4c679fec-a9af-463b-81ac-3ca9fe92cb8b'::uuid),
      ('89a58933-ba8d-42b5-ac9b-25ef58fac97d'::uuid),
      ('075ba4e7-6c89-483f-8e2d-a7c012b963d1'::uuid),
      ('f3d61dce-0d39-45e5-9b8d-c71f9155ce9c'::uuid),
      ('ebc50809-311d-470c-b6b9-bfd4b847bf92'::uuid),
      ('870cbb08-4a8f-451d-959c-f26185633a38'::uuid),
      ('37823ff8-7292-4b60-95d9-186e99ddaff8'::uuid),
      ('d8485c36-c8d9-489c-a0f6-2995947a53bd'::uuid),
      ('8271bee7-861d-4ded-a2cb-58ba62876325'::uuid))
GROUP BY 1 ;

> Group  (cost=48.92..48.93 rows=15 width=16)

EXPLAIN SELECT c.id
FROM credentials c
WHERE
  ARRAY[c."userId"] && '{ "9f0466d4-a628-467a-bd82-7dc02683aa2d", "0358fb92-7690-4daa-a3a2-25b010a84c51", "1293fff6-9925-430f-85e5-4a59c65031a2", "4c679fec-a9af-463b-81ac-3ca9fe92cb8b", "89a58933-ba8d-42b5-ac9b-25ef58fac97d", "075ba4e7-6c89-483f-8e2d-a7c012b963d1", "f3d61dce-0d39-45e5-9b8d-c71f9155ce9c", "ebc50809-311d-470c-b6b9-bfd4b847bf92", "870cbb08-4a8f-451d-959c-f26185633a38", "37823ff8-7292-4b60-95d9-186e99ddaff8", "d8485c36-c8d9-489c-a0f6-2995947a53bd", "8271bee7-861d-4ded-a2cb-58ba62876325" }'
GROUP BY 1;

> HashAggregate  (cost=181.44..183.15 rows=570 width=16)