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