Possible performance regression in PostgreSQL 9.2/9.3?

From: Linos <info(at)linos(dot)es>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Possible performance regression in PostgreSQL 9.2/9.3?
Date: 2014-06-04 13:56:08
Message-ID: 538F2578.9080001@linos.es
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello,

Some days ago I upgraded from 8.4 to 9.3, after the upgrade some queries started performing a lot slower, the query I am using in this example is pasted here:

http://pastebin.com/71DjEC21

Considering it is a production database users are complaining because queries are much slower than before, so I tried to downgrade to 9.2 with the same result as 9.3, I finally restored the database on 8.4 and the query is as fast as before.

All this tests are done on Debian Squeeze with 2.6.32-5-amd64 kernel version, the hardware is Intel Xeon E5520, 32Gb ECC RAM, the storage is software RAID 10 with 4 SEAGATE ST3146356SS SAS drives.

postgresql.conf:
max_connections = 250
shared_buffers = 6144MB
temp_buffers = 8MB
max_prepared_transactions = 0
work_mem = 24MB
maintenance_work_mem = 384MB
max_stack_depth = 7MB
default_statistics_target = 150
effective_cache_size = 24576MB

9.3 explain:
http://explain.depesz.com/s/jP7o

9.3 explain analyze:
http://explain.depesz.com/s/6UQT

9.2 explain:
http://explain.depesz.com/s/EW1g

8.4 explain:
http://explain.depesz.com/s/iAba

8.4 explain analyze:
http://explain.depesz.com/s/MPt

It seems to me that the total estimated cost went too high in 9.2 and 9.3 but I am not sure why, I tried commenting out part of the query and disabling indexonlyscan but still I have very bad timings and estimates.

The dump file is the same for all versions and after the restore process ended I did vacuum analyze on the restored database in all versions.

Regards,
Miguel Angel.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Evgeny Shishkin 2014-06-04 18:44:40 group commit
Previous Message Vince Lasmarias 2014-06-02 23:43:30 High CPU load when 'free -m' shows low 'free' memory even though large 'cached' memory still available