Skip site navigation (1) Skip section navigation (2)

Re: Slow query after upgrade to 8.4

From: tv(at)fuzzy(dot)cz
To: "Jared Beck" <jared(at)singlebrook(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org, "Leon Miller-Out" <leon(at)singlebrook(dot)com>
Subject: Re: Slow query after upgrade to 8.4
Date: 2009-09-24 09:56:12
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
> Hello postgres wizards,
> We recently upgraded from 8.1.5 to 8.4
> We have a query (slow_query.sql) which took about 9s on 8.1.5
> On 8.4, the same query takes 17.7 minutes.
> The code which generated this query is written to support the
> calculation of arbitrary arithmetic expressions across "variables" and
> "data" within our application.  The example query is a sum of three
> "variables", but please note that because the code supports arbitrary
> arithmetic, we do not use an aggregate function like sum()
> We have collected as much information as we could and zipped it up here:
> Thank you very much in advance for any suggestions you may have,
> Jared Beck

Tom Lane already replied, so I'm posting just parsed explain plans - I've
created that before noticing the reply, and I think it might be useful.

good (8.1):
bad (8.4):

As you can see, the real problem is the 'index scan / sort'.


In response to

pgsql-performance by date

Next:From: jesperDate: 2009-09-24 11:35:48
Subject: Re: Speed while runnning large transactions.
Previous:From: solAris23Date: 2009-09-24 09:49:17
Subject: Re: Index row requires 9324 bytes maximum size is 8191

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group