[PERFORM] poor pefrormance with regexp searches on large tables

2011-08-10 Thread Grzegorz Blinowski
Dear All, I have some problems with regexp queries performance - common sense tells me that my queries should run faster than they do. The database - table in question has 590 K records, table's size is 3.5GB. I am effectively querying a single attribute "subject" which has an average size of 2KB

Re: [PERFORM] poor pefrormance with regexp searches on large tables

2011-08-10 Thread Tomas Vondra
On 10 Srpen 2011, 16:26, Grzegorz Blinowski wrote: > Now, the query above takes about 60sec to execute; exactly: 70s for the > first run and 60s for the next runs. In my opinion this is too long: It > should take 35 s to read the whole table into RAM (assuming 100 MB/s > transfers - half the HDD b

Re: [PERFORM] poor pefrormance with regexp searches on large tables

2011-08-10 Thread pasman pasmański
Try to use single regular expression. 2011/8/10, Grzegorz Blinowski : > Dear All, > > I have some problems with regexp queries performance - common sense tells me > that my queries should run faster than they do. > > The database - table in question has 590 K records, table's size is 3.5GB. I > am

Re: [PERFORM] poor pefrormance with regexp searches on large tables

2011-08-10 Thread Kevin Grittner
Grzegorz Blinowski 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 R

[PERFORM] Autovacuum running out of memory

2011-08-10 Thread Alexis Lê-Quôc
Greetings, I've been hitting a "out of memory error" during autovacuum of relatively large tables (compared to the amount of RAM available). I'm trying to trace the cause of the issue; the answer is somewhere below and I don't know how to interpret the data. I can solve the issue right now by usin

Re: [PERFORM] poor pefrormance with regexp searches on large tables

2011-08-10 Thread Grzegorz Blinowski
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-redha

Re: [PERFORM] poor pefrormance with regexp searches on large tables

2011-08-10 Thread Tomas Vondra
Dne 10.8.2011 19:01, Grzegorz Blinowski napsal(a): > 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 han

Re: [PERFORM] poor pefrormance with regexp searches on large tables

2011-08-10 Thread Kevin Grittner
Grzegorz Blinowski wrote: > the problem is not disk transfer/access but rather the way > Postgres handles regexp queries. As a diagnostic step, could you figure out some non-regexp way to select about the same percentage of rows with about the same distribution across the table, and compare ti

Re: [PERFORM] Autovacuum running out of memory

2011-08-10 Thread Tom Lane
=?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= writes: > I've been hitting a "out of memory error" during autovacuum of > relatively large tables (compared to the amount of RAM available). > The error message is: > [10236]: [1-1] user=,db=,remote= ERROR: out of memory > [10236]: [2-1] user=,db=,remote= DE

Re: [PERFORM] poor pefrormance with regexp searches on large tables

2011-08-10 Thread Kevin Grittner
Grzegorz Blinowski wrote: > autovacuum| off BTW, that's generally not a good idea -- it leaves you much more vulnerable to bloat which could cause performance problems to manifest in any number of ways. You might want to calculate your heap bloat on this table. -Kevin -- S

Re: [PERFORM] poor pefrormance with regexp searches on large tables

2011-08-10 Thread Kevin Grittner
"Kevin Grittner" wrote: > So far I haven't seen any real indication that the time is spent > in evaluating the regular expressions Just as a reality check here, I ran some counts against a moderately-sized table (half a million rows). Just counting the rows unconditionally was about five time

Re: [PERFORM] Autovacuum running out of memory

2011-08-10 Thread Alexis Lê-Quôc
On Wed, Aug 10, 2011 at 1:17 PM, Tom Lane wrote: > Alexis Le-Quoc writes: >> I've been hitting a "out of memory error" during autovacuum of >> relatively large tables (compared to the amount of RAM available). > >> The error message is: >> [10236]: [1-1] user=,db=,remote= ERROR:  out of memory >>

Re: [PERFORM] Autovacuum running out of memory

2011-08-10 Thread Tom Lane
=?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= writes: > On Wed, Aug 10, 2011 at 1:17 PM, Tom Lane wrote: >> However, I find it a bit odd that you're getting this failure in what >> appears to be a 64-bit build. That means you're not running out of >> address space, so you must actually be out of RAM+swap

Re: [PERFORM] Autovacuum running out of memory

2011-08-10 Thread Alexis Lê-Quôc
On Wed, Aug 10, 2011 at 2:54 PM, Tom Lane wrote: > Alexis Le-Quoc writes: >> On Wed, Aug 10, 2011 at 1:17 PM, Tom Lane wrote: >>> However, I find it a bit odd that you're getting this failure in what >>> appears to be a 64-bit build.  That means you're not running out of >>> address space, so yo

Re: [PERFORM] Autovacuum running out of memory

2011-08-10 Thread Tom Lane
=?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= writes: > On Wed, Aug 10, 2011 at 2:54 PM, Tom Lane wrote: >> Hmph. Is there other stuff being run on the same instance? Are there a >> whole lot of active PG processes? Maybe Amazon isn't really giving you >> a whole 8GB, or there are weird address space r