Re: [PERFORM] need help with a query

2007-10-23 Thread Pavel Velikhov
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

Re: [PERFORM] How to improve speed of 3 table join group (HUGE tables)

2007-10-23 Thread Nis Jørgensen
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

Re: [PERFORM] Seqscan

2007-10-23 Thread Adrian Demaestri
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

Re: [PERFORM] Seqscan

2007-10-23 Thread Nis Jørgensen
(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,

[PERFORM] 12 hour table vacuums

2007-10-23 Thread Ron St-Pierre
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

Re: [PERFORM] 12 hour table vacuums

2007-10-23 Thread Bill Moran
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

Re: [PERFORM] 12 hour table vacuums

2007-10-23 Thread Tom Lane
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

Re: [PERFORM] 12 hour table vacuums

2007-10-23 Thread Alvaro Herrera
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

Re: [PERFORM] 12 hour table vacuums

2007-10-23 Thread Ron St-Pierre
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

Re: [PERFORM] 12 hour table vacuums

2007-10-23 Thread Gregory Stark
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). ...

Re: [PERFORM] 12 hour table vacuums

2007-10-23 Thread Bill Moran
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

Re: [PERFORM] 12 hour table vacuums

2007-10-23 Thread Ron St-Pierre
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

Re: [PERFORM] 12 hour table vacuums

2007-10-23 Thread Ron St-Pierre
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

Re: [PERFORM] 12 hour table vacuums

2007-10-23 Thread Joshua D. Drake
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

Re: [PERFORM] 12 hour table vacuums

2007-10-23 Thread Simon Riggs
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

Re: [PERFORM] 12 hour table vacuums

2007-10-23 Thread Alvaro Herrera
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

Re: [PERFORM] 12 hour table vacuums

2007-10-23 Thread Alvaro Herrera
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