On Mon, Jun 22, 2015 at 4:51 AM, Christian Ramseyer <r...@networkz.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
