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: 54112.193.179.187.70.1253786172.squirrel@sq.gransy.com (view raw or flat)
Thread:
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:
>
> http://pgsql.privatepaste.com/download/a3SdI8j2km
>
> 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): http://explain.depesz.com/s/1dT
bad (8.4): http://explain.depesz.com/s/seT

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

regards
Tomas


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-2014 The PostgreSQL Global Development Group