8.0.1 to 8.0.13 upgrade added 15% lack of time of query execution

From: Aleksandr Vinokurov <aleksandr(dot)vin(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: 8.0.1 to 8.0.13 upgrade added 15% lack of time of query execution
Date: 2007-09-04 10:37:36
Message-ID: 46DD3570.5090801@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello all,

I beg your pardon if I do not know some thing, but I was disappointed
after taken an upgrade from 8.0.1 to 8.0.13, now the query, that I had
optimized so far to run for 92 sec on 8.0.1, takes 106 seconds on 8.0.13.

The plan seems to stay unmodified, except statistics used for its
construction, but one nested loop's actual time was largely increased:

[For 8.0.13]
Nested Loop Left Join (cost=113409.50..137207.88 rows=12214 width=146)
(actual time=37839.806..104026.224 rows=26139 loops=1)

[For 8.0.1]
Nested Loop Left Join (cost=116564.46..119674.74 rows=1597 width=146)
(actual time=32120.292..90484.361 rows=26139 loops=1)

What I have done upgrading:
1. As rel.notes says I don't need dump/restore to perform my upgrade,
even initdb is not needed for me, as I don't want to add security
enhancements for now.
2. Compiled 8.0.13 from sources and have installed to a separate
location (/usr/local/pgsql instead of old /usr/bin, /usr/lib,..)
3. chown-ed all stuff to postgres in /usr/local/pgsql
4. run postmaster with the old data directory specified.
5. reindexed database.

Both plans with queries echoed are attached to this email.

Will appreciate any help in navigating me to the right way.

With best regards,
Aleksandr.

Attachment Content-Type Size
8.0.13.txt text/plain 11.8 KB
8.0.1.txt text/plain 11.7 KB

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2007-09-04 10:45:18 Re: Use of delete...returning in function problem
Previous Message Dirk Jagdmann 2007-09-04 09:38:29 Re: EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?