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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
