Grzegorz Blinowski wrote:
> 2) changing long attribute storage to EXTERNAL gave 30% better
> search time (but only on the first search - i.e. before data is
> cached)
That suggests that all of the following are true:
(1) The long value was previously being compressed and stored
in-line.
(
To summarize this thread:
We have tried most of the suggestions and found two of them effective:
1) collapsing OR expressions in the WHERE clause into one '(...)|(...)'
regexp resulted in about 60% better search time
2) changing long attribute storage to EXTERNAL gave 30% better search time
(but
Grzegorz Blinowski wrote:
> A small followup regarding the suggestion to turn off compression
> - I used:
>
> ALTER TABLE archive_tender ALTER COLUMN subject SET STORAGE
> EXTERNAL
>
> to turn off compression, however I get an impression that "nothing
> happend". When exactly this alteration t
A small followup regarding the suggestion to turn off compression - I used:
ALTER TABLE archive_tender ALTER COLUMN subject SET STORAGE EXTERNAL
to turn off compression, however I get an impression that "nothing happend".
When exactly this alteration takes effect? Perhaps I should reload the
enti
"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
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
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
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
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
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
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
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
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
13 matches
Mail list logo
