Max Kremer <mkremer@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
