Re: query not using GIN index

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