> It will probably be useful with EXPLAIN ANALYZE of your
> queries, not just the EXPLAIN.
it took 245 seconds to complete, see below.
> It looks like the planner thinks this is going to be really
> cheap -- so it's misestimating something somewhere. Have you
> ANALYZEd recently?
yes, but to
On Fri, May 18, 2007 at 12:02:44PM +0300, Liviu Ionescu wrote:
> the 8.2.4 plan with join_collapse_limit = 1 (with default it was worse, full
> of nested loops)
It will probably be useful with EXPLAIN ANALYZE of your queries, not just the
EXPLAIN.
> "Nested Loop Left Join (cost=32.01..2012.31 r
Ralph Mason wrote:
We have a database running on a 4 processor machine. As time goes by the IO
gets worse and worse peeking at about 200% as the machine loads up.
The weird thing is that if we restart postgres it’s fine for hours but over
time it goes bad again.
(CPU usage graph here HYPERLINK
I recently tried to upgrade to 8.2.4, but major queries I wrote for 8.1.4 are
now planned differently on 8.2.4 and are no longer usable. What the 8.1.4
planned as a series of 'hash left join's and took about 2 seconds now is
planned as 'nested loop left joins' and takes forever.
Other request w
On Fri, May 18, 2007 at 01:14:56PM +0300, Liviu Ionescu wrote:
> yes, but to be sure I did it again before issuing the request; no
> improvements...
Is this with the join collapse limit set to 1, or with default? (Default is
generally more interesting.)
/* Steinar */
--
Homepage: http://www.ses
> Is this with the join collapse limit set to 1, or with
> default? (Default is generally more interesting.)
below is the same query with the default setting.
regards,
Liviu
"Nested Loop Left Join (cost=23.35..1965.46 rows=1 width=125) (actual
time=50.408..231926.123 rows=2026 loops=1)"
"
On Fri, May 18, 2007 at 02:05:36PM +0300, Liviu Ionescu wrote:
> "-> Hash Left Join
> (cost=2.44..63.29 rows=1 width=49) (actual time=0.361..14.426 rows=2206
> loops=1)"
> " Hash Cond: (n.nodeid =
> te
> This seems to be the source of the misestimation. You might
> want to try using "n WHERE n.nodein NOT IN (SELECT nodeid
> FROM templates)" instead of "n LEFT JOIN templates USING
> (nodeid) WHERE templates.nodeid IS NULL" and see if it helps.
it helped, the new version of the query takes 2303
On Fri, May 18, 2007 at 02:51:42PM +0300, Liviu Ionescu wrote:
> it helped, the new version of the query takes 2303 ms on both 8.1.4 and 8.2.4.
And the old one?
> any idea why the 8.2.4 planner is not happy with the initial select? was it
> just a big chance that it worked in 8.1.4 or the 8.2.4 p
> > it helped, the new version of the query takes 2303 ms on both 8.1.4
> > and 8.2.4.
>
> And the old one?
slightly shorter, 2204 ms.
as a subjective perception, the entire application is slightly slower on 8.2.4,
probably there are many queries that were manually tunned for 7.x/8.1.x and now
You're not swapping are you? One explanation could be that PG is
configured to think it has access to a little more memory than the box
can really provide, which forces it to swap once it's been running for
long enough to fill up its shared buffers or after a certain number of
concurrent connectio
> > This seems to be the source of the misestimation. You might
> > want to try using "n WHERE n.nodein NOT IN (SELECT nodeid
> > FROM templates)" instead of "n LEFT JOIN templates USING
> > (nodeid) WHERE templates.nodeid IS NULL" and see if it helps.
>
> it helped, the new version of the quer
> under some alignments of the planets 8.1 has similar problems.
8.1 might have similar problems, but the point here is different: if what
was manually tuned to work in 8.1 confuses the 8.2 planner and performance
drops so much (from 2303 to 231929 ms in my case) upgrading a production
machine to
"Steinar H. Gunderson" <[EMAIL PROTECTED]> writes:
> On Fri, May 18, 2007 at 02:05:36PM +0300, Liviu Ionescu wrote:
>> "-> Hash Left Join
>> (cost=2.44..63.29 rows=1 width=49) (actual time=0.361..14.426 rows=2206
>> loops=1)"
>> "
I have an interesting problem. I have the following query that ran ok on Monday
and Tuesday and it has been running ok since I have been at this job. I have
seen it to be IO intensive, but since Wednesday it has become CPU intensive.
Database wise fresh data has been put into the tables, vacuume
> It is arguable, that updating the DB software version in an
> enterprise environment requires exactly that: check all
> production queries on the new software to identify any
> issues. In part, this is brought on by the very tuning that
> you performed against the previous software. Restore t
On Fri, May 18, 2007 at 09:02:52AM -0700, Abu Mushayeed wrote:
> I have an interesting problem. I have the following query that ran ok on
> Monday and Tuesday and it has been running ok since I have been at this
> job. I have seen it to be IO intensive, but since Wednesday it has become
> CPU inten
We have recently ported our application to the postgres database. For
the most part performance has not been an issue; however there is one
situation that is a problem and that is the initial read of rows
containing BYTEA values that have an average size of 2 kilobytes or
greater. For BYTEA values
"Mark Harris" <[EMAIL PROTECTED]> writes:
> We have recently ported our application to the postgres database. For
> the most part performance has not been an issue; however there is one
> situation that is a problem and that is the initial read of rows
> containing BYTEA values that have an average
I need some help on recommendations to solve a perf problem.
I've got a table with ~121 million records in it. Select count on it
currently takes ~45 minutes, and an update to the table to set a value on
one of the columns I finally killed after it ran 17 hours and had still
not completed. Q
Mark,
I am no expert but this looks like a file system I/O thing. I set
hw.ata.wc=1 for a SATA drive and =0 for a SCSI drive in /boot/loader.conf on
my FreeBSD systems. That seems to provide some needed tweaking.
Yudhvir
==
On 5/18/07, Mark Harris <[EMAIL PROTECTED]> wrote:
We have re
On Fri, May 18, 2007 at 12:43:40PM -0500, [EMAIL PROTECTED] wrote:
> I've got a table with ~121 million records in it. Select count on it
> currently takes ~45 minutes, and an update to the table to set a value on
> one of the columns I finally killed after it ran 17 hours and had still
> not c
[EMAIL PROTECTED] wrote:
I need some help on recommendations to solve a perf problem.
I've got a table with ~121 million records in it. Select count on it
currently takes ~45 minutes, and an update to the table to set a value
on one of the columns I finally killed after it ran 17 hours and ha
Tom,
No it is not 3 seconds to read a single value. Multiple records are
read, approximately 120 records if the raster dataset is created with
our application's default configuration.
Please read on to understand why, if you need to.
We are a GIS software company and have two basic types of data
[EMAIL PROTECTED] wrote:
I need some help on recommendations to solve a perf problem.
I've got a table with ~121 million records in it. Select count on it
currently takes ~45 minutes, and an update to the table to set a value
on one of the columns I finally killed after it ran 17 hours and h
I've tried searching the documentation to answer this question but could
not find anything. When trying to choose the optimal fillfactor for an
index, what is important the number of times the row is updated or the
column indexed upon is updated? In my case each row is updated on
average about
I have a two column table with over 160 million rows in it. As the size
of the table grows queries on this table get exponentially slower. I am
using version 8.1.5 32-bit on Red Hat Enterprise Linux 3. The hardware
is an Intel 3 Ghz Xeon with 4GB RAM, and 6 disks in a RAID 5
configuration. For
On Friday 18 May 2007 11:51, "Joshua D. Drake" <[EMAIL PROTECTED]> wrote:
> > The update query that started this all I had to kill after 17hours. It
> > should have updated all 121+ million records. That brought my select
> > count down to 19 minutes, but still a far cry from acceptable.
You're
Tom Lane wrote:
> Ron Mayer <[EMAIL PROTECTED]> writes:
>> Greg Smith wrote:
>>> Count me on the side that agrees adjusting the vacuuming parameters is
>>> the more straightforward way to cope with this problem.
>
>> Agreed for vacuum; but it still seems interesting to me that
>> across databases
Tyrrill, Ed wrote:
I have a two column table with over 160 million rows in it. As the size
of the table grows queries on this table get exponentially slower. I am
using version 8.1.5 32-bit on Red Hat Enterprise Linux 3. The hardware
is an Intel 3 Ghz Xeon with 4GB RAM, and 6 disks in a RAID 5
Tom,
Actually the 120 records I quoted is a mistake. Since it is a three band
image the number of records should be 360 records or 120 records for
each band.
Mark
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Friday, May 18, 2007 10:48 AM
To: Mark Harris
Cc: pgsql-pe
Andrew Sullivan <[EMAIL PROTECTED]> writes:
> All of that said, 17 hours seems kinda long.
I imagine he's done a bunch of those full-table UPDATEs without
vacuuming, and now has approximately a gazillion dead tuples bloating
the table.
regards, tom lane
-
"Tyrrill, Ed" <[EMAIL PROTECTED]> writes:
> Index Scan using backup_location_pkey on backup_location
> (cost=0.00..1475268.53 rows=412394 width=8) (actual
> time=3318.057..1196723.915 rows=2752 loops=1)
>Index Cond: (backup_id = 1070)
> Total runtime: 1196725.617 ms
If we take that at face v
Tyrrill, Ed wrote:
> mdsdb=# \d backup_location
> Table "public.backup_location"
> Column | Type | Modifiers
> ---+-+---
> record_id | bigint | not null
> backup_id | integer | not null
> Indexes:
> "backup_location_pkey" PRIMARY KEY, btree (record_id, backup_
Scott Marlowe <[EMAIL PROTECTED]> writes:
> Secondly, it might be more efficient for the planner to choose the
> backup_location_rid index than the combination primary key index.
Oh, I'm an idiot; I didn't notice the way the index was set up. Yeah,
that index pretty well sucks for a query on bac
Gene Hart wrote:
I've tried searching the documentation to answer this question but could
not find anything. When trying to choose the optimal fillfactor for an
index, what is important the number of times the row is updated or the
column indexed upon is updated? In my case each row is updated
Tom Lane <[EMAIL PROTECTED]> writes:
>
> Scott Marlowe <[EMAIL PROTECTED]> writes:
> > Secondly, it might be more efficient for the planner to choose the
> > backup_location_rid index than the combination primary key index.
>
> Oh, I'm an idiot; I didn't notice the way the index was set up.
> Ye
On Fri, May 18, 2007 at 02:22:52PM -0700, Tyrrill, Ed wrote:
> Total runtime: 4.951 ms
Going from 1197 seconds to 5 milliseconds. That's some sort of record in a
while, I think :-)
/* Steinar */
--
Homepage: http://www.sesse.net/
---(end of broadcast)---
"Tyrrill, Ed" <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> writes:
>> This combination of indexes:
>>
>>> Indexes:
>>> "backup_location_pkey" PRIMARY KEY, btree (record_id, backup_id)
>>> "backup_location_rid" btree (record_id)
>>
>> is really just silly. You should have the pkey and
What Postgres version is this?
8.1.3
> set enable_nestloop = off;
What's the rationale for this?
To eliminate nested loop. It does a nested loop betwwen to very large
table(millions of rows).
> HashAggregate (cost=152555.97..152567.32 rows=267 width=162)
152000 disk page fetch
Abu Mushayeed <[EMAIL PROTECTED]> writes:
> The query is as follows and it's explain plan is also attached:
The query itself seems to be a simple join over not too many rows, so
I don't see how it could be taking 24 hours. What I suspect is you're
incurring lots and lots of invocations of those
> one of them has suddenly decided to get very slow
Is there a way to predict when the system will do this? Also, why would it
suddenly go from IO intensive to CPU intensive.
Also, this query ran today and it already finished. Today it was IO intensive.
Please provide me some dir
On Fri, May 18, 2007 at 02:37:27PM -0700, Abu Mushayeed wrote:
>>> set enable_nestloop = off;
>> What's the rationale for this?
> To eliminate nested loop. It does a nested loop betwwen to very large
> table(millions of rows).
If the planner chooses a nested loop, it is because it believes it is
On Sat, May 19, 2007 at 12:32:33AM +0200, Steinar H. Gunderson wrote:
> Did you ANALYZE your tables recently? If the joins are really between
> millions of rows and the planner thinks it's a couple thousands, the stats
> sound rather off...
Sorry, I forgot your first e-mail where you said you had
I've got a table with ~121 million records in it. Select count on it
currently takes ~45 minutes, and an update to the table to set a value
on one of the columns I finally killed after it ran 17 hours and had
still not completed. Queries into the table are butt slow, and
The update query
Tom Lane <[EMAIL PROTECTED]> writes:
>> Thanks for the help guys! That was my problem. I actually need the
>> backup_location_rid index for a different query so I am going to keep
>> it.
>
> Well, you don't really *need* it; the two-column index on (record_id,
> backup_id) will serve perfectly
Craig James wrote:
> Better yet, if you can stand a short down time, you can drop indexes on
> that column, truncate, then do 121 million inserts, and finally
> reindex. That will be MUCH faster.
Or you can do a CLUSTER, which does all the same things automatically.
--
Alvaro Herrera
On 5/15/07, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
On Mon, May 14, 2007 at 08:20:49PM -0400, Tom Lane wrote:
> "Y Sidhu" <[EMAIL PROTECTED]> writes:
> > it may be table fragmentation. What kind of tables? We have 2 of them
which
> > experience lots of adds and deletes only. No updates. So a typ
On Fri, 18 May 2007, [EMAIL PROTECTED] wrote:
shared_buffers = 24MB
work_mem = 256MB
maintenance_work_mem = 512MB
You should take a minute to follow the suggestions at
http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm and set
dramatically higher values for shared_buffers and e
On Fri, 18 May 2007, Ron Mayer wrote:
Anecdotally ;-) I've found renice-ing reports to help
Let's break this down into individual parts:
1) Is there enough CPU-intensive activity in some database tasks that they
can be usefully be controlled by tools like nice? Sure.
2) Is it so likely th
50 matches
Mail list logo
