|
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
|
