2
  • 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.

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

  • 1
    The problem here is likely that running WHERE NOT column @> array['thing'] matches so many rows that the planner decides a different plan is better. It probably isn't selective enough. Or the selectivity estimates aren't good. If you, for testing purposes only, SET enable_bitmapscan = off and try again, is it slower or faster? What does explain show then? Show full explain (buffers, analyze) for all three queries as links to explain.depesz.com please. – Craig Ringer Sep 9 '16 at 0:10
  • @CraigRinger Ok, links added – Karew Sep 9 '16 at 1:34
  • The negated query returns 13285 rows and only 3171 rows do not match the criteria (80% of the rows of the table are returned). So Craig's assumption is right: the query simply returns too many rows in order to make an index scan efficient – a_horse_with_no_name Sep 9 '16 at 9:06
  • @a_horse_with_no_name Is there another type of index I can build that optimizes for this situation? Or a different WHERE clause that is more efficient for this same result? (An item is not in an array column) – Karew Sep 9 '16 at 14:59
  • No index will help if you are retrieving a substantial part of the table. – a_horse_with_no_name Sep 9 '16 at 15:55
1

I don't think there is any highly effective way to index generic versions of that expression. If you find a way to do so, I think you will be famous (well, famous within database circles, anyway).

If it is always exactly NOT column @> array['thing'] where 'thing' does not change, you could build special-purpose indexes, like

create index on foo (("column" @> array['thing']));

(or a partial index version of that)

Once 9.6 is released, you will be able to do the seq scan in parallel, and so get a boost that way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.