Re: Possible performance regression in PostgreSQL 9.2/9.3? - Mailing list pgsql-performance

On 05/06/14 15:29, Igor Neyman wrote:
>> -----Original Message-----
>> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-
>> performance-owner@postgresql.org] On Behalf Of Linos
>> Sent: Wednesday, June 04, 2014 6:10 PM
>> To: Merlin Moncure
>> Cc: pgsql-performance@postgresql.org
>> Subject: Re: [PERFORM] Possible performance regression in PostgreSQL
>> 9.2/9.3?
>>
>> On 04/06/14 22:57, Merlin Moncure wrote:
>>> On Wed, Jun 4, 2014 at 2:58 PM, Linos <info@linos.es> wrote:
>>>> 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 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.
>>>>>> 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 find a 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 would like 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 strange behavior I am not
>> sure anymore.
>>> I would against advise adjusting postgresql.conf.  nestloops often
>>> give worse plans than other choices but can often give the best plan,
>>> sometimes by an order of magnitude or more.  planner directives should
>>> be considered a 'last resort' fix and should generally not be changed
>>> in postgresql.conf.  If i were in your shoes, I'd be breaking the
>>> query down and figuring out where it goes off the rails.   Best case
>>> scenario, you have a simplified, test case reproducible reduction of
>>> the problem that can help direct changes to the planner.  In lieu of
>>> that, I'd look at this as a special case optimization of problem
>>> queries.
>>>
>>> There is something else to try.  Can you (temporarily) raise
>>> join_collapse_limit higher (to, say 20), and see if you get a better
>>> plan (with and without other planner adjustments)?
>>>
>>> merlin
>>>
>>>
>> This is the plan with join_collapse_limit=20, enable_nestloop=false,
>> enable_material=false:
>> http://explain.depesz.com/s/PpL
>>
>> The plan with join_collapse_limit=20 but nestloops and enable_material true
>> is taking too much time, seems to have the same problem as with
>> join_collapse_limit=8.
>>
>> I will try to create a simpler reproducible example, thank you.
>>
>> 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
> Usually, when I increase join_collapse_limit, I also increase from_collaps_limit (to the same value).
>
> Regards,
> Igor Neyman
>
>

I tried that already and it didn't work, thank you Igor.

I have created a more complete example of this problem in pgsql-hackers list at:
http://www.postgresql.org/message-id/5390554B.5080503@linos.es

To continue the conversation there.

Regards,
Miguel Angel.