3
  • 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

Improve this question
5
  • 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
    Commented Sep 9, 2016 at 0:10
  • @CraigRinger Ok, links added
    – Karew
    Commented Sep 9, 2016 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
    – user1822
    Commented Sep 9, 2016 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
    Commented Sep 9, 2016 at 14:59
  • No index will help if you are retrieving a substantial part of the table.
    – user1822
    Commented Sep 9, 2016 at 15:55

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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