Re: poor pefrormance with regexp searches on large tables

[Grzegorz Blinowski <g.blinowski@xxxxxxxxx>]

Thnaks for all  the help so far, I increased the shared_mem config parameter (Postgress didn't accept higher values than default, had to increase systemwide shared mem). The current config (as suggested by Kevin Grittner) is as follows:

 version                   | PostgreSQL 8.4.7 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-50), 64-bit
 autovacuum                | off
 client_encoding           | LATIN2
 effective_cache_size      | 8GB
 lc_collate                | en_US.UTF-8
 lc_ctype                  | en_US.UTF-8
 listen_addresses          | *
 log_rotation_age          | 1d
 log_rotation_size         | 0
 log_truncate_on_rotation  | on
 logging_collector         | on
 maintenance_work_mem      | 1GB
 max_connections           | 16
 max_prepared_transactions | 50
 max_stack_depth           | 8MB
 port                      | 5432
 server_encoding           | UTF8
 shared_buffers            | 1GB
 statement_timeout         | 25min
 temp_buffers              | 16384
 TimeZone                  | Europe/Berlin
 work_mem                  | 128MB


However, changing shared_mem didn't help. We also checked system I/O stats during the query - and in fact there is almost no IO (even with suboptimal shared_memory). So the problem is not disk transfer/access but rather the way Postgres handles regexp queries... As I have wirtten it is difficult to rewrite the query syntax (the SQL generation in this app is quite complex), but it should be relatively easy to at least join all OR clauses into one regexp, I can try this from the psql CLI. I will post an update if anything interesting happens...

Cheers,

Greg

On Wed, Aug 10, 2011 at 5:27 PM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote:

Grzegorz Blinowski <g.blinowski@xxxxxxxxx> wrote:

> Some performance params from postgresql.conf:

Please paste the result of running the query on this page:

http://wiki.postgresql.org/wiki/Server_Configuration

For a start, the general advice is usually to start with
shared_buffers at the lesser of 25% of system RAM or 8 GB, and
adjust from there based on benchmarks.  So you might want to try 4GB
for that one.

Just to confirm, you are using 2 Phase Commit?  (People sometimes
mistake the max_prepared_transactions setting for something related
to prepared statements.)

I concur with previous advice that using one regular _expression_
which matches all of the terms is going to be a lot faster than
matching each small regular _expression_ separately and then combining
them.

-Kevin