From: Guo, Yun <YGuo@cvent.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: query not using GIN index
Date: Sat, 22 Aug 2015 01:55:56 +0000
Message-ID: <D1FD4EEC.42E82%yguo@cvent.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: YGuo@cvent.com
Subject: Re: query not using GIN index
In-Reply-To: <D1FD4EEC.42E82%yguo@cvent.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox