On 10/20/07, Pavel Velikhov [EMAIL PROTECTED] wrote:
Left the query running for 10+ hours and had to kill it. I guess
there
really was no need to have lots of
shared buffers (the hope was that postgresql will cache the whole
table). I
ended up doing this step inside
the application as a
John Major skrev:
Hello Nis-
I did reset the defaults before running the explain.
This line from your original post:
- Seq Scan on sequence_alignment sa (cost=1.00..110379294.60
rows=467042560 width=4)
Is an indication that you didn't (AFAIK enable_seqscan=off works by
setting the
It is not actualy a table, sorry, it is a quite complex view that involve three
large tables. When I query the view using a where clause the answer is fast
because of the use of some restrictive indexes, but when there is no where
clause the limit 1 waits until the entire table is generated and
(Please don't top-post. )
Adrian Demaestri skrev:
*/Jeff Davis [EMAIL PROTECTED]/* escribió:
On Mon, 2007-10-22 at 19:24 -0700, Adrian Demaestri wrote:
Hi,
I think planner should use other plans than seqscan to solve querys
like select * from hugetable limit 1,
We vacuum only a few of our tables nightly, this one is the last one
because it takes longer to run. I'll probably re-index it soon, but I
would appreciate any advice on how to speed up the vacuum process (and
the db in general).
Okay, here's our system:
postgres 8.1.4
Linux version
In response to Ron St-Pierre [EMAIL PROTECTED]:
We vacuum only a few of our tables nightly, this one is the last one
because it takes longer to run. I'll probably re-index it soon, but I
would appreciate any advice on how to speed up the vacuum process (and
the db in general).
I doubt
Ron St-Pierre [EMAIL PROTECTED] writes:
The table has 140,000 rows, 130 columns (mostly NUMERIC), 60 indexes. It
is probably our 'key' table in the database and gets called by almost
every query (usually joined to others). The table gets updated only
about 10 times a day. We were running
Ron St-Pierre wrote:
Okay, here's our system:
postgres 8.1.4
Upgrade to 8.1.10
Here's the table information:
The table has 140,000 rows, 130 columns (mostly NUMERIC), 60 indexes.
60 indexes? You gotta be kidding. You really have 60 columns on which
to scan?
vacuum_cost_delay = 200
Bill Moran wrote:
In response to Ron St-Pierre [EMAIL PROTECTED]:
We vacuum only a few of our tables nightly, this one is the last one
because it takes longer to run. I'll probably re-index it soon, but I
would appreciate any advice on how to speed up the vacuum process (and
the db in
Ron St-Pierre [EMAIL PROTECTED] writes:
We vacuum only a few of our tables nightly, this one is the last one because
it
takes longer to run. I'll probably re-index it soon, but I would appreciate
any
advice on how to speed up the vacuum process (and the db in general).
...
In response to Ron St-Pierre [EMAIL PROTECTED]:
Bill Moran wrote:
In response to Ron St-Pierre [EMAIL PROTECTED]:
We vacuum only a few of our tables nightly, this one is the last one
because it takes longer to run. I'll probably re-index it soon, but I
would appreciate any
Tom Lane wrote:
Here is your problem:
vacuum_cost_delay = 200
If you are only vacuuming when nothing else is happening, you shouldn't
be using vacuum_cost_delay at all: set it to 0. In any case this value
is probably much too high. I would imagine that if you watch the
machine
Alvaro Herrera wrote:
Ron St-Pierre wrote:
Okay, here's our system:
postgres 8.1.4
Upgrade to 8.1.10
Any particular fixes in 8.1.10 that would help with this?
Here's the table information:
The table has 140,000 rows, 130 columns (mostly NUMERIC), 60 indexes.
60
On Tue, 23 Oct 2007 10:00:05 -0700
Ron St-Pierre [EMAIL PROTECTED] wrote:
Alvaro Herrera wrote:
Ron St-Pierre wrote:
Okay, here's our system:
postgres 8.1.4
Upgrade to 8.1.10
Any particular fixes in 8.1.10 that would help with this?
Here's the table
On Tue, 2007-10-23 at 08:53 -0700, Ron St-Pierre wrote:
The table gets updated only
about 10 times a day.
So why are you VACUUMing it nightly? You should do this at the weekend
every 3 months...
8.1 is slower at VACUUMing indexes than later releases, so 60 indexes
are going to hurt quite a
Ron St-Pierre wrote:
Gregory Stark wrote:
We were running autovacuum but it interfered with the updates to we
shut it off.
Was it just the I/O bandwidth? I'm surprised as your
vacuum_cost_delay is quite high. Manual vacuum doesn't do anything
differently from autovacuum, neither
Ron St-Pierre wrote:
Alvaro Herrera wrote:
Ron St-Pierre wrote:
Okay, here's our system:
postgres 8.1.4
Upgrade to 8.1.10
Any particular fixes in 8.1.10 that would help with this?
I don't think so, but my guess is that you really want to avoid the
autovacuum bug which
17 matches
Mail list logo