Re: [GENERAL] 8.2.4 serious slowdown

(attribution restored) Clodoaldo wrote: > I don't know if the plan would be the same but this is a bit clearer: > > WHERE COALESCE(b.quantity, 0) > COALESCE(b.deliveredsum, 0) Sim Zacks wrote: That should be true, but sometimes we get deliveries of greater quantity then we ordered. I just

Re: [GENERAL] 8.2.4 serious slowdown

His agony? Do they not know how to paint a resolute death? Yes, for the same Saint Luke paints the death of Saint Stephen as braver than that of Jesus Christ. They make Him, therefore, capable of fear, before the necessity of dying has come, and then altogether brave. But when they make Him so tr

Re: [GENERAL] 8.2.4 serious slowdown

point beyond which our senses can no longer perceive anything, although by its nature it is infinitely divisible. Of these two Infinites of science, that of greatness is the most palpable, and hence a few persons have pretended to know all things. "I will speak of the whole," said Democritus. But

Re: [GENERAL] 8.2.4 serious slowdown

who before had serious thoughts, had their awakenings and convictions greatly increased. There were many instances of persons who came from abroad on visits, or on business, who had not been long here, before, to all appearances, they were savingly wrought upon, and partook of that shower of divine

Re: [GENERAL] 8.2.4 serious slowdown

gave orders that the burning fiery furnace should be heated seven times hotter than it was before; doubtless, it was raised to the utmost degree of fierceness that human art could raise it. But the great God is also willing to show his wrath, and magnify his awful majesty and mighty power in the ex

Re: [GENERAL] 8.2.4 serious slowdown

Apparently I was suffering from brain freeze. sim Lew wrote: (attribution restored) Clodoaldo wrote: > I don't know if the plan would be the same but this is a bit clearer: > > WHERE COALESCE(b.quantity, 0) > COALESCE(b.deliveredsum, 0) Sim Zacks wrote: That should be true, but sometimes w

Re: [GENERAL] 8.2.4 serious slowdown

> I don't know if the plan would be the same but this is a bit clearer: > > WHERE COALESCE(b.quantity, 0) > COALESCE(b.deliveredsum, 0) That should be true, but sometimes we get deliveries of greater quantity then we ordered. I just want to know the times when I haven't gotten the complete order

Re: [GENERAL] 8.2.4 serious slowdown

2008/1/13, Sim Zacks <[EMAIL PROTECTED]>: > How would you rewrite something like: >WHERE (COALESCE(b.quantity, 0) - COALESCE(b.deliveredsum, 0)) > 0; > I could write: > where case when b.quantity is null then 0 else b.quantity end - case when > b.deliveredsum is null then 0 else b.deliveredsum

Re: [GENERAL] 8.2.4 serious slowdown

How would you rewrite something like: WHERE (COALESCE(b.quantity, 0) - COALESCE(b.deliveredsum, 0)) > 0; I could write: where case when b.quantity is null then 0 else b.quantity end - case when b.deliveredsum is null then 0 else b.deliveredsum end > 0 It is butt ugly, but is that the most effi

Re: [GENERAL] 8.2.4 serious slowdown

Actually I just checked and the plan is exactly the same for those 2 clauses. Original Message Subject: Re:8.2.4 serious slowdown From: Sim Zacks <[EMAIL PROTECTED]> To: Date: Sunday, January 13, 2008 07:59:22 AM > How would you rewrite something like: > WHERE (COALESCE(b.

Re: [GENERAL] 8.2.4 serious slowdown

Hello On 11/01/2008, Sim Zacks <[EMAIL PROTECTED]> wrote: > I changed it to "where f.commited is not true" and the query now takes 1 > second as opposed to 60. > (much faster then the 3 seconds it took on 8.0.1, which could also be because > of the coalesce there) > Is it considered better pract

Re: [GENERAL] 8.2.4 serious slowdown

I changed it to "where f.commited is not true" and the query now takes 1 second as opposed to 60. (much faster then the 3 seconds it took on 8.0.1, which could also be because of the coalesce there) Is it considered better practice (or more efficient) to always use (x is not or x=value) instead

Re: [GENERAL] 8.2.4 serious slowdown

Thanks for the tuning tips. I'll definitely be taking advantage of them. Problem solved, Tom Lane found that using coalesce in my query the way I did caused it to make a bad estimation, when I changed it the query went from 60 seconds to 1 second. Sim See

Re: [GENERAL] 8.2.4 serious slowdown

Sim Zacks <[EMAIL PROTECTED]> writes: > I just upgraded my database server from 8.0.1 to 8.2.4 > Most things went very well, but I have a couple of queries that really slowed > down with the new server. The core of the problem seems to be the rowcount misestimation here: > -> Merge Left Jo

Re: [GENERAL] 8.2.4 serious slowdown

On 1/10/08, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Jan 10, 2008 12:33 PM, Sim Zacks <[EMAIL PROTECTED]> wrote: > > > Perhaps a suboptimal plan is picked due to configuration issues, e.g. > > > memory constraints? Could you post your postgresql.conf as well? > > > > Below is the postgresql.co

Re: [GENERAL] 8.2.4 serious slowdown

On Jan 10, 2008 12:33 PM, Sim Zacks <[EMAIL PROTECTED]> wrote: > > Perhaps a suboptimal plan is picked due to configuration issues, e.g. > > memory constraints? Could you post your postgresql.conf as well? > > Below is the postgresql.conf file for 8.2.4. The server has 2 GB of RAM and > it was not

Re: [GENERAL] 8.2.4 serious slowdown

Perhaps a suboptimal plan is picked due to configuration issues, e.g. memory constraints? Could you post your postgresql.conf as well? Below is the postgresql.conf file for 8.2.4. The server has 2 GB of RAM and it was not maxed out when I ran the query. As I mentioned, I tried running both 8.0.

Re: [GENERAL] 8.2.4 serious slowdown

It does contain a lot more loops, but it is the exact same query, so I don't understand why it would use that kind of plan sim Bricklen Anderson wrote: I don't an answer to your question, but an obvious difference is that the "slow" query contains many more loops. (this may already have been

Re: [GENERAL] 8.2.4 serious slowdown

On Jan 10, 2008 10:50 AM, Sim Zacks <[EMAIL PROTECTED]> wrote: > I meant I did Vacuum Analyze. > In any case, Aside from the vacuum analyze, I also tested it right after a > database restore, so there should be no need for any maintenance features. FYI, a restore does NOT restore the stats, nor d

Re: [GENERAL] 8.2.4 serious slowdown

On 1/10/08, Sim Zacks <[EMAIL PROTECTED]> wrote: > I meant I did Vacuum Analyze. > In any case, Aside from the vacuum analyze, I also tested it right after a > database restore, so there should be no need for any maintenance features. > The stats didn't look too far off, no. Perhaps a suboptimal

Re: [GENERAL] 8.2.4 serious slowdown

I don't an answer to your question, but an obvious difference is that the "slow" query contains many more loops. (this may already have been noted, I didn't see it posted however). (showing just the loops with more than one loop) -> Index Scan using assemblies_pkey on assemblies a (cost=0.00

Re: [GENERAL] 8.2.4 serious slowdown

Most of the queries that I have tested work on 8.2.4 at least as fast as on 8.0.1. This one has really thrown me for a loop. Sim Could you try 8.3 and see what happens? Keep the emails in case this thread mysteriously disappears. Please stop the histrionics. If your new query is slower,

Re: [GENERAL] 8.2.4 serious slowdown

I meant I did Vacuum Analyze. In any case, Aside from the vacuum analyze, I also tested it right after a database restore, so there should be no need for any maintenance features. Sim Isak Hansen wrote: On 1/10/08, Sim Zacks <[EMAIL PROTECTED]> wrote: I just upgraded my database server from 8

Re: [GENERAL] 8.2.4 serious slowdown

On Jan 10, 2008 11:12 AM, Clodoaldo <[EMAIL PROTECTED]> wrote: > 2008/1/10, Scott Marlowe <[EMAIL PROTECTED]>: > > > I have seen performance degradation at every new version since 7.3. > > > > Then your experience has been exactly the opposite of mine. > > I suspect some developers here make a livi

Re: [GENERAL] 8.2.4 serious slowdown

2008/1/10, Scott Marlowe <[EMAIL PROTECTED]>: > On Jan 10, 2008 9:50 AM, Clodoaldo <[EMAIL PROTECTED]> wrote: > > 2008/1/10, Sim Zacks <[EMAIL PROTECTED]>: > > > I just upgraded my database server from 8.0.1 to 8.2.4 > > > Most things went very well, but I have a couple of queries that really > >

Re: [GENERAL] 8.2.4 serious slowdown

On Jan 10, 2008 9:50 AM, Clodoaldo <[EMAIL PROTECTED]> wrote: > 2008/1/10, Sim Zacks <[EMAIL PROTECTED]>: > > I just upgraded my database server from 8.0.1 to 8.2.4 > > Most things went very well, but I have a couple of queries that really > > slowed down with the new server. > > On 8.0.1 the quer

Re: [GENERAL] 8.2.4 serious slowdown

Clodoaldo <[EMAIL PROTECTED]> writes: > 2008/1/10, Martijn van Oosterhout <[EMAIL PROTECTED]>: >> Huh? It's right there: >> http://archives.postgresql.org/pgsql-general/2008-01/msg00143.php > Where did you get that url? I can't find it here: > http://archives.postgresql.org/pgsql-general/2008-01/t

Re: [GENERAL] 8.2.4 serious slowdown

Clodoaldo escribió: > 2008/1/10, Martijn van Oosterhout <[EMAIL PROTECTED]>: > > http://archives.postgresql.org/pgsql-general/2008-01/msg00143.php > > Where did you get that url? I can't find it here: > > http://archives.postgresql.org/pgsql-general/2008-01/threads.php "Next page" -- Alvaro H

Re: [GENERAL] 8.2.4 serious slowdown

On 10/01/2008 16:11, Clodoaldo wrote: Where did you get that url? I can't find it here: http://archives.postgresql.org/pgsql-general/2008-01/threads.php It's on page 2 of the list.click "Next", and then it's a little over half-way down. Ray. ---

Re: [GENERAL] 8.2.4 serious slowdown

2008/1/10, Martijn van Oosterhout <[EMAIL PROTECTED]>: > On Thu, Jan 10, 2008 at 01:50:42PM -0200, Clodoaldo wrote: > > I posted about it > > but the whole thread disappeared from the archives. It can still be > > found here: > > > > http://archives.free.net.ph/message/20080105.004509.22be255d.es.h

Re: [GENERAL] 8.2.4 serious slowdown

On Thu, Jan 10, 2008 at 01:50:42PM -0200, Clodoaldo wrote: > I posted about it > but the whole thread disappeared from the archives. It can still be > found here: > > http://archives.free.net.ph/message/20080105.004509.22be255d.es.html Huh? It's right there: http://archives.postgresql.org/pgsql-g

Re: [GENERAL] 8.2.4 serious slowdown

Here are all of the data structures involved in this view. Query Ran: select * from assemblycanbuild CREATE OR REPLACE VIEW assemblycanbuild AS SELECT assembliesbatchid, CASE WHEN min( CASE WHEN (stock::double precision - prioruse - quantity::doubl

Re: [GENERAL] 8.2.4 serious slowdown

2008/1/10, Sim Zacks <[EMAIL PROTECTED]>: > I just upgraded my database server from 8.0.1 to 8.2.4 > Most things went very well, but I have a couple of queries that really slowed > down with the new server. > On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same > query > (I

Re: [GENERAL] 8.2.4 serious slowdown

On 1/10/08, Sim Zacks <[EMAIL PROTECTED]> wrote: > I just upgraded my database server from 8.0.1 to 8.2.4 > Most things went very well, but I have a couple of queries that really slowed > down with the new server. > On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same > quer

[GENERAL] 8.2.4 serious slowdown

I just upgraded my database server from 8.0.1 to 8.2.4 Most things went very well, but I have a couple of queries that really slowed down with the new server. On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same query (I vacuumed the database before running the query) takes