Re: Efficient rows filter for array inclusion with gin index

2024-02-28 Thread Shanti-Dominique
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 <@

Efficient rows filter for array inclusion with gin index

2024-02-28 Thread Shanti-Dominique
Hello, I have a problem writing performant queries that requires to test value inclusion within an array. First: the schema consists of an "items" table with a "ref_id" primary key (uuid, primary key). The items are hierarchical and can have a "parent_ref_id" (uuid) that references their par