Re: Possible performance regression in PostgreSQL 9.2/9.3? - Mailing list pgsql-performance
From | Linos |
---|---|
Subject | Re: Possible performance regression in PostgreSQL 9.2/9.3? |
Date | |
Msg-id | 538F7A65.9050207@linos.es Whole thread Raw |
In response to | Re: Possible performance regression in PostgreSQL 9.2/9.3? (Merlin Moncure <mmoncure@gmail.com>) |
Responses |
Re: Possible performance regression in PostgreSQL 9.2/9.3?
|
List | pgsql-performance |
On 04/06/14 21:36, Merlin Moncure wrote: > On Wed, Jun 4, 2014 at 8:56 AM, Linos <info@linos.es> wrote: >> Hello, >> >> Some days ago I upgraded from 8.4 to 9.3, after the upgrade some queries started performing a lot slower, the query Iam 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 triedto 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 asbefore. >> >> All this tests are done on Debian Squeeze with 2.6.32-5-amd64 kernel version, the hardware is Intel Xeon E5520, 32Gb ECCRAM, 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 outpart 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 databasein all versions. >> http://www.postgresql.org/mailpref/pgsql-performance > The rowcount estimates are garbage on all versions so a good execution > plan can be chalked up to chance. That being said, it seems like > we're getting an awful lot of regressions of this type with recent > versions. > > Can you try re-running this query with enable_nestloop and/or > enable_material disabled? (you can disable them for a particular > session via: set enable_material = false;) . This is a "ghetto fix" > but worth trying. If it was me, I'd be simplifying and optimizing the > query. > > merlin > > Much better with this options set to false, thank you Merlin, even better than 8.4 9.3 explain analyze with enable_nestloop and enable_material set to false. http://explain.depesz.com/s/94D The thing is I have plenty of queries that are now a lot slower than before, this is only one example. I would like to finda fix or workaround. I can downgrade to 9.1, I didn't try on 9.1 but it's the first version that supports exceptions inside plpython and I wouldlike to use them. Do you think this situation would be better on 9.1? Or maybe can I disable material and nestloop on postgresql.conf? I thought was bad to trick the planner but given this strangebehavior I am not sure anymore. Regards, Miguel Angel.
pgsql-performance by date: