Re: How to speed up pg_trgm / gin index scan

Hi

I have a pretty large table with syslog messages.

It is already partitioned by month, and for a single month I have e.g.


DM=# \d+ logs_01

    Column    |            Type             |
--------------+-----------------------------+
 host         | character varying(255)      |
 facility     | character varying(10)       |
 priority     | character varying(10)       |
 tag          | character varying(255)      |
 log_date     | timestamp without time zone |
 program      | character varying(255)      |
 msg          | text                        |
 seq          | bigint                      |

Indexes:
    "logs_01_pkey" PRIMARY KEY, btree (seq)
    "idx_logs_01_lower_host" btree (lower(host::text) varchar_pattern_ops)
    "logs_01_date_index" btree (log_date)
    "tridx_logs_01_msg" gin (msg gin_trgm_ops)


DM=# select count(*) from logs_01;
  count
----------
 83052864

A typical query on this table looks like this:

explain analyze
select log_date, host, msg
from logs_01 as log   where  log.msg like '%192.23.33.177%'
    and log.log_date >= '2015-1-18 1:45:24'
    and log.log_date <= '2015-1-19 1:45:24'
    order by log_date asc offset 200 limit 50;