query not using GIN index

From: "Guo, Yun" <YGuo(at)cvent(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: query not using GIN index
Date: 2015-08-22 01:55:56
Message-ID: D1FD4EEC.42E82%yguo@cvent.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

We have a query on a column with GIN index, but query plan chooses not using the index but do an seq scan whichi is must slower

CREATE INDEX idx_access_grants_on_access_tokens ON access_grants USING gin (access_tokens);

explain analyze SELECT "access_grants".* FROM "access_grants" WHERE (access_tokens @> ARRAY['124e5a1f9de325fc176a7c89152ac734']) ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..7.46 rows=1 width=157) (actual time=260.376..260.377 rows=1 loops=1)
-> Seq Scan on access_grants (cost=0.00..29718.03 rows=3985 width=157) (actual time=260.373..260.373 rows=1 loops=1)
Filter: (access_tokens @> '{124e5a1f9de325fc176a7c89152ac734}'::text[])
Rows Removed by Filter: 796818
Total runtime: 260.408 ms

We tested on smaller table in development region and it chooses to use the index there. However, in production size table it decides to ignore the index for unknown reasons.

Is the large number of tuples skewing the query planner’s decision or the index itself is larger than the table therefor it would decide to do table scan?

Any suggestions are greatly appreciated!

Yun

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tomas Vondra 2015-08-22 04:36:15 Re: query not using GIN index
Previous Message Igor Neyman 2015-08-21 14:50:18 Re: Performance bottleneck due to array manipulation