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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
