Postgres UUID array index possible with ANY?

Question

I have a table like:

CREATE TABLE IF NOT EXISTS my_table (
    id uuid NOT NULL PRIMARY KEY,
    duplicate_ids uuid[] DEFAULT NULL,
);

And my query is:

SELECT * FROM my_table WHERE 'some-uuid'=ANY(duplicate_ids)

Using EXPLAIN ANALYZE and trying lots of different indexes, I am unable to get the above to use an index.

Here's what I've tried (Postgres 12):

CREATE INDEX duplicate_ids_idx ON my_table USING GIN (duplicate_ids);
CREATE INDEX duplicate_ids_idx ON my_table USING GIN (duplicate_ids array_ops);
CREATE INDEX duplicate_ids_idx ON my_table USING BTREE (duplicate_ids);
CREATE INDEX duplicate_ids_idx ON my_table USING BTREE (duplicate_ids array_ops);

I've also ran SET enable_seqscan TO off; before these tests to enforce index usage.

Questions I've read:

Can PostgreSQL index array columns?
- Doesn't seem to apply for single values
https://dba.stackexchange.com/questions/125413/index-not-used-with-any-but-used-with-in
- Seems to be talking about indexing multiple columns and using IN

Thank you very much for your time.

Try where duplicate_ids && array['some_uuid']::uuid[] or where duplicate_ids @> array['some_uuid']::uuid[] — user330315
– user330315, Commented Nov 10, 2021 at 16:58

Aaron Meier · Accepted Answer · 2021-11-10 17:22:52Z

3

Question was answered by @a_horse_with_no_name

The solution appears to be to use something like:

SELECT * FROM my_table WHERE duplicate_ids && array['some_uuid']::uuid[]

answered Nov 10, 2021 at 17:22

Aaron Meier

9509 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres UUID array index possible with ANY?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related