pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedGIN vs BTREE - query planner picking the wrong one some times
2+ messages / 2 participants
[nested] [flat]
* GIN vs BTREE - query planner picking the wrong one some times
@ 2016-06-28 21:16 Max Kremer <mkremer@trialfire.com>
0 siblings, 1 reply; 2+ messages in thread
From: Max Kremer @ 2016-06-28 21:16 UTC (permalink / raw)
To: pgsql-admin
Using postgres 9.5 and I'm trying to improve performance of searches using
the LIKE operator on column containing a URL.
Consider a table like this with about 50 million rows
CREATE TABLE page_hit (
timestamp_ timestamp without time zone NOT NULL,
location_url character varying(2048)
)
and a query like this
SELECT count(*)
FROM page_hit
WHERE location_url LIKE 'http://mysite.foo.com/path/morepath%';
The above query makes use of the following index
CREATE INDEX location_idx
ON page_hit
USING btree
(location_url varchar_pattern_ops);
The works great..BUT... to support LIKE wild cards in other parts of the
string (for example a leading %) I created a GIN index as follows
CREATE INDEX location_idx_gin
ON page_hit
USING gin
(location COLLATE pg_catalog."default" gin_trgm_ops);
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.
The BTREE index outperforms the GIN index (in the case of a trailing %) but
I want the planner to pick the GIN index in the case of leading %. Is there
a way to do this?
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
ᐧ
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: GIN vs BTREE - query planner picking the wrong one some times
@ 2016-06-29 17:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Max Kremer <mkremer@trialfire.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2016-06-29 17:20 UTC (permalink / raw)
To: Max Kremer <mkremer@trialfire.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2016-06-29 17:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-06-28 21:16 GIN vs BTREE - query planner picking the wrong one some times Max Kremer <mkremer@trialfire.com>
2016-06-29 17:20 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox