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