Search Postgresql Archives

Re: How to speed up pg_trgm / gin index scan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux