Re: How to speed up pg_trgm / gin index scan

On 22/06/15 19:00, Jeff Janes wrote:

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

Thanks Jeff, but the IP address was mostly an example... I should have
written this more clearly. Generally the searched string will be a
random short fragment from msg (ip, hostname, some part of an error
message etc.).

It must be matched exactly including all punctuation etc, so trigrams
look very suitable.

Cheers
Christian