Slower Performance on Postgres 9.1.6 vs 8.2.11

All... first let me say thank you for this forum.... I am new to it and
relatively new to postgres, more of a sysadmin than a DBA, but let me
explain my issue.   I'll try also to post relevant information as well.

Our IT group took over an app that we have running using postgres and it has
been on version 8.2.11 since we acquired it.   It is time to get current, so
I have created instances of our production database that mirror exact
hardware for our existing implementation on version 8.2.11 (running Fedora
Core 8 - wow I know) and also version 9.1.6 on Fedora 17.   I am able to
mimic the production 8.2 environment exactly without any of the load of
production and the same for the new 9.1 environment so there is no
perverting of numbers based on load that I can't control

Machines are Cloud based images running 4 (dual Core) Processors, with 15GB
of memory...  AMAZON m1.Xlarge boxes - 64 bit OS.

I'm running my query using PSQL from the server

Here is what I discovered.  I have this Query:

 SELECT s.customernumber         AS "Cust Num",
        s.name                            AS "Site",
        UPPER( p.name )               AS "Product",
        UPPER( ii.lotnumber )         AS "Lot Number",
        SUM( ii.quantityremaining ) AS "On Hand"
 FROM   inventoryitems ii
 INNER JOIN inventories  i ON  i.inventoryid = ii.inventoryid
 INNER JOIN sites          s ON  s.siteid      =  i.siteid
 INNER JOIN accounts    a ON  a.accountid   =  s.accountid
 INNER JOIN products     p ON  p.productid   = ii.productid
 WHERE  a.customernumber = 'DS-1007'
 GROUP BY s.customernumber, s.name, UPPER( p.name ), UPPER( ii.lotnumber )
 HAVING SUM( ii.quantityremaining ) > 0
 ORDER BY s.name, UPPER( p.name );

EXPLAIN ANALYZE OUTPUT on 8.2.11 is as follows:
 http://explain.depesz.com/s/JdW
-or-

(20 rows)

EXPLAIN ANALYZE OUTPUT on 9.1.6 is as follows:
  http://explain.depesz.com/s/QZVF

I KNOW, I KNOW the difference is VERY small in terms of actual time, but
percentage wise this is statistically relevant and we are under a crunch to
make our application perform better.

In looking at the explain analyze output, it appears that in every case, 9.1
out performed the 8.2.11 in actually getting the data, but the NESTED LOOP
time is slow enough to make the Total Runtime but as much as a 10th of a
second slower on average...

I have tried tweaking every parameter I can think of and here are some of
the relevant Parameters from my POSTGRESQL.CONF file  (and both machines are
running with KERNEL value " sysctl -w kernel.shmmax=665544320" )

9.1.6 values
max_connections = 250
shared_buffers = 800MB
temp_buffers = 8MB
work_mem = 10MB
maintenance_work_mem = 100MB
wal_buffers = 16MB
effective_cache_size = 8GB

8.2.11 values
max_connections = 250
shared_buffers = 600MB
temp_buffers = 1024
work_mem = 6MB
maintenance_work_mem = 100MB
wal_buffers = 64kB
effective_cache_size = 8GB

In my first attempt at migrating to 9.1 I had a different lc_collate value
at the default and the 9.1 query was running at around 2500 to 2600 ms and
that was huge...   When I re-init'd my DB with the proper lc_locale set, I
expected my issue to be gone, and while it was to the extent of performance
before, it is still slower consistently.

AGAIN, the time difference is in the nested loop nodes themselves, not in
the Index Scan's.   I don't understand this...

Any help will be greatly appreciated.

Rob Cron
rocron@pssd.com




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Slower-Performance-on-Postgres-9-1-6-vs-8-2-11-tp5729749.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.