Re: Efficient rows filter for array inclusion with gin index

Replying to myself after more investigation.

On 28/02/2024 12:05, Shanti-Dominique wrote:

>
>
> 2)
>     SELECT  *
>     FROM    items i1
>             JOIN item_paths p1 ON i1.ref_id = p1.ref_id
>             JOIN items i2 ON ARRAY[i2.ref_id] <@ p1.item_path
>     WHERE   ...
>
> 2) uses the operator <@ which is supported by the gin index, the test
> for inclusion is fast and the query does not run a sequential scan
> over the whole "item_paths" table. However, because of the
> ARRAY[i2.ref_id] construct, it performs a sequential scan on i2.
I was under the assumption that the ARRAY[] construct prevented
postgresql from efficiently using the index on the other side of the
operator, but I think I was mistaken. On a database full of data, I
tried getting around this but did not see any improvement of performance.

First I tried to add an index on the single element array:

CREATE FUNCTION uuidarr(ref_id uuid) RETURNS uuid[]
  LANGUAGE SQL
  IMMUTABLE
  RETURNS NULL ON NULL INPUT
  RETURN ARRAY[ref_id];

CREATE INDEX items_ref_id_arr2_idx ON items USING gin (uuidarr(ref_id));

EXPLAIN
SELECT  i2.*
FROM    items i1
        JOIN item_paths p1 ON i1.ref_id = p1.ref_id
        JOIN items i2 ON uuidarr(i2.ref_id) <@ p1.item_path
WHERE   i1.name = 'a';

The performance was even worse. Then I tried with a generated column:

CREATE TABLE items (
    ref_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    ref_id_array uuid[] GENERATED ALWAYS AS (uuidarr(ref_id)) STORED,
    name character varying,
    parent_ref_id uuid
);

CREATE INDEX items_ref_id_array_idx ON items USING gin (ref_id_array);

EXPLAIN
SELECT  i2.*
FROM    items i1
        JOIN item_paths p1 ON i1.ref_id = p1.ref_id
        JOIN items i2 ON i2.ref_id_array <@ p1.item_path
WHERE   i1.name = 'a';

The performance was very similar to the query with ARRAY[...]

It seems there is no good solution for the general case, apart from
changing the structure of my dataset and removing the use of arrays
entirely.

I think I'll update my codebase and use <@ where it makes sense and =
ANY in other places, but it'll be difficult to know for sure without
running the query which one will be better.