From: Tom Lane <tgl@sss.pgh.pa.us>
To: Max Kremer <mkremer@trialfire.com>
cc: pgsql-admin@postgresql.org
Subject: Re: GIN vs BTREE - query planner picking the wrong one some times
In-reply-to: 
 <CAEbO6DW9EhgsEXnnOso=R74OpiEb28pBLfRhfcVu_hRNrkitwQ@mail.gmail.com>
References: 
 <CAEbO6DW9EhgsEXnnOso=R74OpiEb28pBLfRhfcVu_hRNrkitwQ@mail.gmail.com>
Comments: In-reply-to Max Kremer <mkremer@trialfire.com>
	message dated "Tue, 28 Jun 2016 17:16:18 -0400"
Date: Wed, 29 Jun 2016 13:20:34 -0400
Message-ID: <21192.1467220834@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Max Kremer <mkremer@trialfire.com> writes:
> The problem is when I run EXPLAIN ANALYZE on the above query now its only
> ever using location_idx_gin and ignoring location_idx which kinda sucks
> cause the location_idx index is faster at trailing % queries. The query
> planner seems to ignore my BTREE index in all cases and uses the GIN index.

I tried to reproduce that, but in my hands the planner just about always
prefers the btree if it has a choice.  Especially for longer strings such
as in your example.  (It looks to me like the cost estimate for GIN is
mostly proportional to the number of trigrams in the query string; I find
that GIN is estimated to cost about the same for LIKE strings as short as
'the%', but loses badly for anything longer.)

Can you show a concrete example (with sample data) where this doesn't
hold?

> Some metrics (queries trailing %):
>  - BTREE : <1 second explain: https://explain.depesz.com/s/7wgx
>  - GIN   :   3.8 seconds explain: https://explain.depesz.com/s/wYhk

The obfuscation of the index conditions makes these just about useless
for telling what's going on :-(

			regards, tom lane


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