Re: Array of integer indexed nested-loop semi join

[Jeff Janes <jeff.janes@xxxxxxxxx>]

On Fri, May 20, 2022 at 6:42 AM Mickael van der Beek <mickael.van.der.beek@xxxxxxxxx> wrote:

Query:

EXPLAIN (
  ANALYZE,
  VERBOSE,
  COSTS,
  BUFFERS,
  TIMING
)
SELECT
  fu.w2_page_idxs
FROM
  fact_users
    AS fu
WHERE
  EXISTS (
    SELECT
    FROM
      (
        SELECT
          ARRAY[idx] AS page_idx
        FROM
          fact_pages
        WHERE
          attribute_idxs && ARRAY[300000160]
        FETCH FIRST 1 ROWS ONLY
      )
        AS fp
    WHERE
      fu.w2_page_idxs && fp.page_idx
  )
;

Without any surprises, the planner is using a sequential scan on the "fact_users" table which is very large instead of using the GIN index set on the "w2_page_idxs" column.

For me, using the subquery in and _expression_, instead of the EXISTS, does get it to use the gin index.  And I think it must give the same results.

SELECT
  fu.w2_page_idxs
FROM  fact_users AS fu
WHERE
      fu.w2_page_idxs && ARRAY[(select idx from fact_pages where attribute_idxs && ARRAY[3003] FETCH FIRST 1 ROWS ONLY)];

But why are you using intarray?  That is unnecessary here, and by creating ambiguity about the array operators it might be harmful. 

Cheers,

Jeff