Re: How to speed up pg_trgm / gin index scan

On Mon, Jun 22, 2015 at 4:51 AM, Christian Ramseyer <rc(at)networkz(dot)ch> wrote:

> 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;
>

I think that trigram indexes are not well-suited to searching IP addresses.

If the typical query is always an IP address for the LIKE, I think you
would want to build an index specifically tailored to that. You could make
a function to parse the IP address out of the msg, and then make a
functional index, for example. It would require you to write the query
differently. Whether it would be a btree index or a gin index would depend
on whether you can have more than one IP address in a msg.

Cheers,

Jeff