Message-ID: <55D7FC3F.6070104@2ndquadrant.com>
Date: Sat, 22 Aug 2015 06:36:15 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.7.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: query not using GIN index
References: <D1FD4EEC.42E82%yguo@cvent.com>
In-Reply-To: <D1FD4EEC.42E82%yguo@cvent.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi,

On 08/22/2015 03:55 AM, Guo, Yun 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
>

I find it very likely that the explain output actually comes from a 
slightly different query, including a LIMIT 1 clause.

That might easily be the problem here, because the optimizer expects the 
3985 "matches" to be uniformly distributed in the table, so it thinks 
it'll scan just a tiny fraction of the table (1/3985) until the first 
match. But it's quite possible all at rows are end of the table, and the 
executor has to actually scan the whole table.

It's difficult to say without further details of the table and how the 
data are generated.

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

Please provide explain output from that table. It's difficult to say 
what's different without seeing the details.

Also please provide important details about the system (e.g. which 
PostgreSQL version, how much RAM, what work_mem/shared_buffers and such 
stuff).

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

What large number of tuples? The indexes are supposed to be more 
efficient the larger the table is.

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance