Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZSy41-0002HQ-K1 for pgsql-performance@arkaria.postgresql.org; Sat, 22 Aug 2015 01:57:45 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZSy40-0000GL-P4 for pgsql-performance@arkaria.postgresql.org; Sat, 22 Aug 2015 01:57:44 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZSy2N-0006sR-S7 for pgsql-performance@postgresql.org; Sat, 22 Aug 2015 01:56:04 +0000 Received: from smtp2.cvent.com ([192.190.92.246]) by makus.postgresql.org with esmtps (TLS1.0:DHE_RSA_CAMELLIA_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1ZSy2J-00026G-5H for pgsql-performance@postgresql.org; Sat, 22 Aug 2015 01:56:00 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cvent.com; i=@cvent.com; q=dns/txt; s=c1211; t=1440208559; x=1471744559; h=from:to:subject:date:message-id:mime-version: content-transfer-encoding; bh=plCCt3SGiB5683V6E0q8AR8yplI2MlpyHTKdBqWgJQo=; b=DaBHXZK8pC1ExCWFG3bTkrx79lhAh5KN7g/lGOTUyBcENilLSi9nnD5d bMSbFkopSFi7WNtDrblam3PdtNINi8LUTerzx/yVPmwbx4TJJwCM4qd5C 5OCNgq1MpFVZpSUkwceH1ayrqqy8qyeLIzJ1+sfDm3WgRZiyM4AFio0H2 U=; X-IronPort-AV: E=Sophos;i="5.15,726,1432612800"; d="scan'208,217";a="7312743" From: "Guo, Yun" To: "pgsql-performance@postgresql.org" Subject: query not using GIN index Thread-Topic: query not using GIN index Thread-Index: AQHQ3H2vF7PNkAAHqEWqVZEvn//5mw== Date: Sat, 22 Aug 2015 01:55:56 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: user-agent: Microsoft-MacOutlook/14.5.3.150624 Content-Type: multipart/alternative; boundary="_000_D1FD4EEC42E82yguocventcom_" MIME-Version: 1.0 X-Pg-Spam-Score: -2.8 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --_000_D1FD4EEC42E82yguocventcom_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hi, We have a query on a column with GIN index, but query plan chooses not usin= g 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 (acces= s_tokens @> ARRAY['124e5a1f9de325fc176a7c89152ac734']) ; QUERY PLAN ---------------------------------------------------------------------------= ----------------------------------------------- Limit (cost=3D0.00..7.46 rows=3D1 width=3D157) (actual time=3D260.376..26= 0.377 rows=3D1 loops=3D1) -> Seq Scan on access_grants (cost=3D0.00..29718.03 rows=3D3985 width= =3D157) (actual time=3D260.373..260.373 rows=3D1 loops=3D1) Filter: (access_tokens @> '{124e5a1f9de325fc176a7c89152ac734}'::te= xt[]) 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 ind= ex for unknown reasons. Is the large number of tuples skewing the query planner=92s decision or the= index itself is larger than the table therefor it would decide to do table= scan? Any suggestions are greatly appreciated! Yun --_000_D1FD4EEC42E82yguocventcom_ Content-Type: text/html; charset="Windows-1252" Content-ID: <24064D57FD0F61498E78BB63D767866C@cvent.com> Content-Transfer-Encoding: quoted-printable
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

CREAT= E INDEX idx_access_grants_on_access_tokens ON access_grants USING gin (acce= ss_tokens); 

explain analyze SELECT "access_grants".* FROM "access_g= rants"  WHERE (access_tokens @> ARRAY['124e5a1f9de325fc176a7c8= 9152ac734']) ;
                    =                      = ;               QUERY PLAN
----------------------------------------------------------------------= ----------------------------------------------------
 Limit  (cost=3D0.00..7.46 rows=3D1 width=3D157) (actual tim= e=3D260.376..260.377 rows=3D1 loops=3D1)
   ->  Seq Scan on access_grants  (cost=3D0.00.= .29718.03 rows=3D3985 width=3D157) (actual time=3D260.373..260.373 rows=3D1= loops=3D1)
         Filter: (access_tokens @> '{124e5= a1f9de325fc176a7c89152ac734}'::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 th= e index for unknown reasons.

Is the large number of tuples skewing the query planner=92s decision o= r the index itself is larger than the table therefor it would decide to do = table scan?

Any suggestions are greatly appreciated!

Yun
--_000_D1FD4EEC42E82yguocventcom_--