P
P
pqgg7nwkd42020-09-18 12:58:37
PostgreSQL
pqgg7nwkd4, 2020-09-18 12:58:37

Why is postgres not using the gin index for a query? = ANY(field)?

Example ( https://www.db-fiddle.com/f/bZRFDcpt8DfvLPUBuMEi7t/0 ):

CREATE TABLE test (id SERIAL, array_field INT[]);
CREATE INDEX test_arr ON test USING gin (array_field);

INSERT INTO test (array_field)
SELECT ARRAY[round(random()*10000), round(random()*10000), round(random()*10000)]::int[]
FROM generate_series(1, 1000000);

ANALYZE test;
SET enable_seqscan = OFF;
                                                               

-- NOT USED
EXPLAIN ANALYZE SELECT * FROM test WHERE 10 = ANY(array_field);

-- USED
EXPLAIN ANALYZE SELECT * FROM test WHERE ARRAY[10]::int[] <@ array_field;


The last two queries are identical, however postgres doesn't want to use the index in the first case. Why? If this is some kind of limitation, then what is it connected with?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question