Skip to main content
2 of 3
added 179 characters in body
Karew
  • 182
  • 8

What is the correct/fastest way to negate @> in Postgres?

  • I have a text[] column in Postgres 9.5
  • I am planning on running a lot of queries with @> against it
  • I have a GIN index on this column

Normally you need to use the correct Postgres operator so that an index is consulted for your query. There is no operator that is the "opposite" of @> as far as I know.

I ran EXPLAIN ANALYZE on a query for this table and it takes 8 times as long to run WHERE NOT column @> array['thing'] as it does to run WHERE column @> array['thing']. The planner reports that it does a filter for WHERE NOT instead of a bitmap index scan on my index.

What is the correct way to negate @> so that my index is consulted?

I'm willing to create new indexes for this column, including functional indexes if that's the answer.

EDIT: Here are depesz links:

https://explain.depesz.com/s/uXOT
https://explain.depesz.com/s/nc7Q
https://explain.depesz.com/s/wC9m
https://explain.depesz.com/s/Eat

Karew
  • 182
  • 8