GIN index not being used with NOT?

Question

I have a table with a column itemsIds containing an array of ids. I have created a gin index for that column. I'm trying to find rows that don't contain an element with the following query:

select * from "Posts" where not (ARRAY[8] @> "Posts".itemids);

The thing is that the gin index is not being used when I search with not but if the query is without it Postgres use that index.

EXPLAIN output:

Without NOT:

"Bitmap Heap Scan on ""Posts"" (cost=12.03..25.78 rows=4 width=334)" Recheck Cond: ('{8}'::integer[] @> itemids) -> Bitmap Index Scan on posts_itemids_index (cost=0.00..12.03 rows=4 width=0) Index Cond: (itemids <@ '{8}'::integer[])`

With NOT:

"Seq Scan on ""Posts"" (cost=0.00..140.76 rows=2537 width=334)" Filter: (NOT ('{8}'::integer[] @> itemids))

I tried rewriting the query with:

select * from "Posts" where (ARRAY[8] @> "Posts".itemids) = false;

Same result.

Could you please share the (complete) results from explain(analyze, verbose, buffers, settings) for this query? And the DDL for the table and indexes. All in plain text, as an update of the question. — Frank Heikens
– Frank Heikens, Commented Nov 28, 2024 at 21:40

Erwin Brandstetter · Accepted Answer · 2024-11-28 22:45:19Z

Without NOT, your query returns 4 rows (rows=4). Or at least that's the estimate, you did not provide proper EXPLAIN ANALYZE output.

The inversion with NOT (assuming the column is defined NOT NULL, and has no null elements!) returns all but 4 rows. Nothing will ever be faster than a sequential scan for this. All the more, since your table only has around 2500 rows (rows=2537). A size where indexes don't matter much, yet.

All as it should be. Except that your filter expression uses the wrong operator to ...

... find rows that don't contain an element

Either use the inverse operator <@ or switch operands:

SELECT * FROM "Posts" WHERE NOT itemids @> '{8}'; -- !

While your filter is barely selective, resulting in a seq. scan, you might as well use:

SELECT * FROM "Posts" WHERE 8 <> ALL(itemids);

Collectives™ on Stack Overflow

GIN index not being used with NOT?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related