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