I have the following repeatable case.
I have an uuid[]
column with a gin
index, which stores all unqiue uuid
s, 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?