Re: Possible performance regression in PostgreSQL 9.2/9.3?

> -----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