Re: query not using GIN index

From: Jeff Janes <jeff.janes@gmail.com>
Date: Friday, August 21, 2015 at 10:44 PM
To: Yun <yguo@cvent.com>
Subject: Re: [PERFORM] query not using GIN index

On Fri, Aug 21, 2015 at 6:55 PM, Guo, Yun <YGuo@cvent.com> wrote:

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

What version are you running?  What are your non-default configuration settings (particularly for the *_cost parameters)?

 select name,setting from pg_settings where name like '%cost';

         name         | setting

----------------------+---------

 cpu_index_tuple_cost | 0.005

 cpu_operator_cost    | 0.0025

 cpu_tuple_cost       | 0.01

 random_page_cost     | 4

 seq_page_cost        | 1

Can you turn track_io_timing on and then report a explain (analyze, buffers) of the same query? 

I didn’t try this as our prod instance is on AWS and setting this would require a reboot.

Then do a "set enable_seqscan=off" and repeat.

This is the life saver!  After applying this, it’s able to use the index.  But should we consider it as the permanent solution?