pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPossible performance regression in PostgreSQL 9.2/9.3?
7+ messages / 3 participants
[nested] [flat]
* Possible performance regression in PostgreSQL 9.2/9.3?
@ 2014-06-04 13:56 Linos <info@linos.es>
0 siblings, 1 reply; 7+ messages in thread
From: Linos @ 2014-06-04 13:56 UTC (permalink / raw)
To: 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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Possible performance regression in PostgreSQL 9.2/9.3?
@ 2014-06-04 19:36 Merlin Moncure <mmoncure@gmail.com>
parent: Linos <info@linos.es>
0 siblings, 1 reply; 7+ messages in thread
From: Merlin Moncure @ 2014-06-04 19:36 UTC (permalink / raw)
To: Linos <info@linos.es>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Possible performance regression in PostgreSQL 9.2/9.3?
@ 2014-06-04 19:58 Linos <info@linos.es>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Linos @ 2014-06-04 19:58 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: 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 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.
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Possible performance regression in PostgreSQL 9.2/9.3?
@ 2014-06-04 20:57 Merlin Moncure <mmoncure@gmail.com>
parent: Linos <info@linos.es>
0 siblings, 1 reply; 7+ messages in thread
From: Merlin Moncure @ 2014-06-04 20:57 UTC (permalink / raw)
To: Linos <info@linos.es>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Possible performance regression in PostgreSQL 9.2/9.3?
@ 2014-06-04 22:09 Linos <info@linos.es>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Linos @ 2014-06-04 22:09 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Possible performance regression in PostgreSQL 9.2/9.3?
@ 2014-06-05 13:29 Igor Neyman <ineyman@perceptron.com>
parent: Linos <info@linos.es>
0 siblings, 1 reply; 7+ messages in thread
From: Igor Neyman @ 2014-06-05 13:29 UTC (permalink / raw)
To: Linos <info@linos.es>; Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
> -----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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Possible performance regression in PostgreSQL 9.2/9.3?
@ 2014-06-05 13:36 Linos <info@linos.es>
parent: Igor Neyman <ineyman@perceptron.com>
0 siblings, 0 replies; 7+ messages in thread
From: Linos @ 2014-06-05 13:36 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; Merlin Moncure <mmoncure@gmail.com>; +Cc: 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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2014-06-05 13:36 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-06-04 13:56 Possible performance regression in PostgreSQL 9.2/9.3? Linos <info@linos.es>
2014-06-04 19:36 ` Merlin Moncure <mmoncure@gmail.com>
2014-06-04 19:58 ` Linos <info@linos.es>
2014-06-04 20:57 ` Merlin Moncure <mmoncure@gmail.com>
2014-06-04 22:09 ` Linos <info@linos.es>
2014-06-05 13:29 ` Igor Neyman <ineyman@perceptron.com>
2014-06-05 13:36 ` Linos <info@linos.es>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox