GIN vs BTREE - query planner picking the wrong one some times

Поиск
Список
Период
Сортировка
От Max Kremer
Тема GIN vs BTREE - query planner picking the wrong one some times
Дата
Msg-id CAEbO6DW9EhgsEXnnOso=R74OpiEb28pBLfRhfcVu_hRNrkitwQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: GIN vs BTREE - query planner picking the wrong one some times
Список 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





В списке pgsql-admin по дате отправления:

Предыдущее
От: "Gilberto Castillo"
Дата:
Сообщение: Re: [MASSMAIL]long running delete
Следующее
От: Patrick B
Дата:
Сообщение: Queries and Transactions per second - PostgreSQL 9.2