Re: poor pefrormance with regexp searches on large tables

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(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Grzegorz Blinowski <g(dot)blinowski(at)gmail(dot)com> 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
>