1

I have the following repeatable case.

I have an uuid[] column with a gin index, which stores all unqiue uuids, without doubles and repeats.

Test table:

CREATE TABLE test 
    (
    id serial not null, 
    uuid uuid[]
    );

CREATE INDEX IF NOT EXISTS gin_uuid_idx
    ON test 
    USING gin(uuid);

Query:

SELECT * FROM "table_name" 
WHERE "uuid" && ARRAY['9070bc76-10b1-420a-b823-13f09ac637a2']::uuid[];

Query plan:

Bitmap Heap Scan on test (cost=1500.97..3199.83 rows=1157 width=36) (actual time=2.359..2.360 rows=1 loops=1)
  Recheck Cond: (uuid && '{9070bc76-10b1-420a-b823-13f09ac637a2}'::uuid[])
  Heap Blocks: exact=1
  -> Bitmap Index Scan on gin_uuid_idx (cost=0.00..1500.68 rows=1157 width=0) (actual time=2.353..2.354 rows=1 loops=1)
       Index Cond: (uuid && '{9070bc76-10b1-420a-b823-13f09ac637a2}'::uuid[])

Planning Time: 0.519 ms
Execution Time: 2.387 ms

Expected rows(1157 rows) doesnt equals real rows (expect 1 row), and planner selects non-optimal query plan.

For large tables with 10-100KK rows, query will be executing for 10-30 seconds, which is too long.

How can I fix this wrong behavior and make the query run faster?

0

Your Answer

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