Thread

1. Optimizer items in the release notes

   Bruce Momjian <bruce@momjian.us> — 2019-04-22T16:54:51Z

   We had a discussion in October about adding more optimizer items to the
   release notes:
   
   	https://www.postgresql.org/message-id/flat/20181010220601.GA7807%40momjian.us#11d805ea0b0fcd0552dfa99251417cc1
   
   There was no agreement on a change, but if people want to propose a
   change, please post here and we can discuss it.
   
   -- 
     Bruce Momjian  <bruce@momjian.us>        http://momjian.us
     EnterpriseDB                             http://enterprisedb.com
   
   + As you are, so once was I.  As I am, so you will be. +
   +                      Ancient Roman grave inscription +
   
   
   
   

2. Re: Optimizer items in the release notes

   Adrien Nayrat <adrien.nayrat@anayrat.info> — 2019-04-24T11:08:53Z

   On 4/22/19 6:54 PM, Bruce Momjian wrote:
   > We had a discussion in October about adding more optimizer items to the
   > release notes:
   > 
   > 	https://www.postgresql.org/message-id/flat/20181010220601.GA7807%40momjian.us#11d805ea0b0fcd0552dfa99251417cc1
   > 
   > There was no agreement on a change, but if people want to propose a
   > change, please post here and we can discuss it.
   > 
   
   Hello,
   
   Thanks Bruce to start this thread.
   
   I still think it is useful to mention changes in the optimizer for 
   several reasons:
   
   - help to understand why a plan can change between different majors 
   versions. I don't have any example but an improvement for some users 
   could be a regression for others.
   - knowing optimizer improvements can motivate users to upgrade to newer 
   versions
   - help to find bug between majors versions
   
   
   Regards,
   
   
   
   

3. Re: Optimizer items in the release notes

   David Rowley <david.rowley@2ndquadrant.com> — 2019-04-24T12:43:31Z

   On Tue, 23 Apr 2019 at 04:54, Bruce Momjian <bruce@momjian.us> wrote:
   >
   > We had a discussion in October about adding more optimizer items to the
   > release notes:
   >
   >         https://www.postgresql.org/message-id/flat/20181010220601.GA7807%40momjian.us#11d805ea0b0fcd0552dfa99251417cc1
   >
   > There was no agreement on a change, but if people want to propose a
   > change, please post here and we can discuss it.
   
   I'd say these sorts of changes are important. TBH, these days, I think
   query planner smarts are arguably one of our weakest areas when
   compared to the big commercial databases. The more we can throw in
   there about this sort of thing the better.  The strange thing about
   making improvements to the planner is often that the benefits of doing
   so can range massively. e.g has zero effect all the way up to perhaps
   thousands or even millions of times faster.  The users that get the 1
   million times speedup will likely want to know that more than some
   executor speedup that gets them 5% across the board.  I believe these
   are useful to keep in the release notes to catch the eye of all those
   people blocked from upgrading from <commercial database> due to us not
   optimising their queries the same way as they and their applications
   are accustomed to.
   
   I see from the v11 release notes that we have "E.3.3.1.4. Optimizer",
   it seems fairly simple for someone to skip this if they happen not to
   be interested in what's been changed in that area.
   
   -- 
    David Rowley                   http://www.2ndQuadrant.com/
    PostgreSQL Development, 24x7 Support, Training & Services
   
   
   
   

4. Re: Optimizer items in the release notes

   Adam Brusselback <adambrusselback@gmail.com> — 2019-04-24T18:46:15Z

   As a user, I am interested in the optimizer changes for sure, and I
   actually had wished they were highlighted more in previous releases.
   
   > I think planner smarts are arguably one of our weakest areas when
   > compared to the big commercial databases. The more we can throw in
   > there about this sort of thing the better.
   
   Completely agree on both fronts. I have run into numerous optimizations I
   had taken for granted when I worked primarily with SQL Server and were not
   present in Postgres.
   Work being done to make the Postgres optimizer smarter is great, as is
   highlighting that work in the release notes IMO.
   

5. Re: Optimizer items in the release notes

   Bruce Momjian <bruce@momjian.us> — 2019-04-26T23:49:14Z

   On Wed, Apr 24, 2019 at 02:46:15PM -0400, Adam Brusselback wrote:
   > As a user, I am interested in the optimizer changes for sure, and I
   > actually had wished they were highlighted more in previous releases.
   >
   > > I think planner smarts are arguably one of our weakest areas when
   > > compared to the big commercial databases. The more we can throw in
   > > there about this sort of thing the better.
   >
   > Completely agree on both fronts. I have run into numerous
   > optimizations I had taken for granted when I worked primarily with SQL
   > Server and were not present in Postgres.  Work being done to make the
   > Postgres optimizer smarter is great, as is highlighting that work in
   > the release notes IMO.
   
   This thread highlights the challenges of having optimizer items in the
   release notes:
   
   *  They often reply to only a small percentage of queries
   
   *  They are hard to explain
   
   I see the argument as wanting vague warm and fuzzy feelings that we are
   improving the optimizer, which we are.  I will see what I can do to get
   those ideas into the PG 12 release notes in as concrete a way as
   possible.
   
   -- 
     Bruce Momjian  <bruce@momjian.us>        http://momjian.us
     EnterpriseDB                             http://enterprisedb.com
   
   + As you are, so once was I.  As I am, so you will be. +
   +                      Ancient Roman grave inscription +
   
   
   
   

6. Re: Optimizer items in the release notes

   David Rowley <david.rowley@2ndquadrant.com> — 2019-04-27T02:04:33Z

   On Sat, 27 Apr 2019 at 11:49, Bruce Momjian <bruce@momjian.us> wrote:
   >
   > On Wed, Apr 24, 2019 at 02:46:15PM -0400, Adam Brusselback wrote:
   > > As a user, I am interested in the optimizer changes for sure, and I
   > > actually had wished they were highlighted more in previous releases.
   > >
   > > > I think planner smarts are arguably one of our weakest areas when
   > > > compared to the big commercial databases. The more we can throw in
   > > > there about this sort of thing the better.
   > >
   > > Completely agree on both fronts. I have run into numerous
   > > optimizations I had taken for granted when I worked primarily with SQL
   > > Server and were not present in Postgres.  Work being done to make the
   > > Postgres optimizer smarter is great, as is highlighting that work in
   > > the release notes IMO.
   >
   > This thread highlights the challenges of having optimizer items in the
   > release notes:
   >
   > *  They often reply to only a small percentage of queries
   
   That can often be true, but as I mentioned that it's quite common that
   the queries that the changes do effect see massive performance
   improvements. Some patch that gave us 5% across the board is unlikely
   to unblock someone from migrating to PostgreSQL, but some query
   planner smarts that reduce query times by orders of magnitude could
   unblock someone.
   
   > *  They are hard to explain
   
   That can be true, but we generally get there if not the first time
   then after a few iterations. Authors and committers of the
   improvements are likely to be able to help find suitable wording.
   
   > I see the argument as wanting vague warm and fuzzy feelings that we are
   > improving the optimizer, which we are.
   
   Not sure where the warm and fuzzy argument is from. The point I tried
   to make was that if we're making changes to PostgreSQL that are likely
   going to be useful to people, then we likely should put them in the
   release notes. It was my understanding that this was why major version
   release notes were useful.
   
   > I will see what I can do to get
   > those ideas into the PG 12 release notes in as concrete a way as
   > possible.
   
   I think the current process is really good. You take on the hard task
   of drafting them up, to which everyone is very grateful for as it's a
   pretty tedious job. Various people that might have been closer to the
   actual work done for certain items then suggest improvements.
   
   -- 
    David Rowley                   http://www.2ndQuadrant.com/
    PostgreSQL Development, 24x7 Support, Training & Services
   
   
   
   

7. Re: Optimizer items in the release notes

   Bruce Momjian <bruce@momjian.us> — 2019-04-27T02:22:26Z

   On Sat, Apr 27, 2019 at 02:04:33PM +1200, David Rowley wrote:
   > On Sat, 27 Apr 2019 at 11:49, Bruce Momjian <bruce@momjian.us> wrote:
   > >
   > > On Wed, Apr 24, 2019 at 02:46:15PM -0400, Adam Brusselback wrote:
   > > > As a user, I am interested in the optimizer changes for sure, and I
   > > > actually had wished they were highlighted more in previous releases.
   > > >
   > > > > I think planner smarts are arguably one of our weakest areas when
   > > > > compared to the big commercial databases. The more we can throw in
   > > > > there about this sort of thing the better.
   > > >
   > > > Completely agree on both fronts. I have run into numerous
   > > > optimizations I had taken for granted when I worked primarily with SQL
   > > > Server and were not present in Postgres.  Work being done to make the
   > > > Postgres optimizer smarter is great, as is highlighting that work in
   > > > the release notes IMO.
   > >
   > > This thread highlights the challenges of having optimizer items in the
   > > release notes:
   > >
   > > *  They often reply to only a small percentage of queries
   > 
   > That can often be true, but as I mentioned that it's quite common that
   > the queries that the changes do effect see massive performance
   > improvements. Some patch that gave us 5% across the board is unlikely
   > to unblock someone from migrating to PostgreSQL, but some query
   > planner smarts that reduce query times by orders of magnitude could
   > unblock someone.
   
   The problem is that it is rare, and hard to explain, so it is almost
   impossible for an average user to have any hope in guessing if it will
   help them.
   
   > > *  They are hard to explain
   > 
   > That can be true, but we generally get there if not the first time
   > then after a few iterations. Authors and committers of the
   > improvements are likely to be able to help find suitable wording.
   
   It is not the text that is hard, but hard to explain the concept in a
   way that relates to anything a normal user is familiar with.
   
   > > I see the argument as wanting vague warm and fuzzy feelings that we are
   > > improving the optimizer, which we are.
   > 
   > Not sure where the warm and fuzzy argument is from. The point I tried
   > to make was that if we're making changes to PostgreSQL that are likely
   > going to be useful to people, then we likely should put them in the
   > release notes. It was my understanding that this was why major version
   > release notes were useful.
   
   It is for generally-common user behavior changes.  If we just repeat the
   commit logs, it will we much less readable.
   
   > > I will see what I can do to get
   > > those ideas into the PG 12 release notes in as concrete a way as
   > > possible.
   > 
   > I think the current process is really good. You take on the hard task
   > of drafting them up, to which everyone is very grateful for as it's a
   > pretty tedious job. Various people that might have been closer to the
   > actual work done for certain items then suggest improvements.
   
   Yes, that has been the plan.
   
   -- 
     Bruce Momjian  <bruce@momjian.us>        http://momjian.us
     EnterpriseDB                             http://enterprisedb.com
   
   + As you are, so once was I.  As I am, so you will be. +
   +                      Ancient Roman grave inscription +
   
   
   
   

8. Re: Optimizer items in the release notes

   David Rowley <david.rowley@2ndquadrant.com> — 2019-04-27T02:47:44Z

   On Sat, 27 Apr 2019 at 14:22, Bruce Momjian <bruce@momjian.us> wrote:
   > > > *  They are hard to explain
   > >
   > > That can be true, but we generally get there if not the first time
   > > then after a few iterations. Authors and committers of the
   > > improvements are likely to be able to help find suitable wording.
   >
   > It is not the text that is hard, but hard to explain the concept in a
   > way that relates to anything a normal user is familiar with.
   
   Yeah, that's no doubt often going to be a struggle, but we can't
   expect every person who reads the release notes to understand
   everything. You could probably say the same for any internal
   implementation change we make though. I don't think the planner is
   unique in this regard, so I don't think it needs any special
   treatment. I also don't think we need to go into great detail. The
   item could be as light on detail as:
   
   * Improve query planner's ability to push LIMIT through Sort nodes.
   
   If you don't know what LIMIT is or what a Sort node is, then you're
   probably not going to care about the change. They can keep reading on
   the next line, but if the reader happens to have suffered some pain on
   that during their migration attempt, then they might be quite happy to
   see those words. If they want more details then they might be savvy
   enough to hunt those down, or perhaps they'll come asking.
   
   -- 
    David Rowley                   http://www.2ndQuadrant.com/
    PostgreSQL Development, 24x7 Support, Training & Services
   
   
   
   

9. Re: Optimizer items in the release notes

   Bruce Momjian <bruce@momjian.us> — 2019-04-27T11:40:49Z

   On Sat, Apr 27, 2019 at 02:47:44PM +1200, David Rowley wrote:
   > On Sat, 27 Apr 2019 at 14:22, Bruce Momjian <bruce@momjian.us> wrote:
   > > > > *  They are hard to explain
   > > >
   > > > That can be true, but we generally get there if not the first time
   > > > then after a few iterations. Authors and committers of the
   > > > improvements are likely to be able to help find suitable wording.
   > >
   > > It is not the text that is hard, but hard to explain the concept in a
   > > way that relates to anything a normal user is familiar with.
   > 
   > Yeah, that's no doubt often going to be a struggle, but we can't
   > expect every person who reads the release notes to understand
   > everything. You could probably say the same for any internal
   > implementation change we make though. I don't think the planner is
   > unique in this regard, so I don't think it needs any special
   
   I do believe the planner is unique in this regard in the sense that the
   changes can make 100x difference in performance, and it is often unclear
   from the user interface exactly what is happening.
   
   > treatment. I also don't think we need to go into great detail. The
   > item could be as light on detail as:
   > 
   > * Improve query planner's ability to push LIMIT through Sort nodes.
   > 
   > If you don't know what LIMIT is or what a Sort node is, then you're
   > probably not going to care about the change. They can keep reading on
   > the next line, but if the reader happens to have suffered some pain on
   > that during their migration attempt, then they might be quite happy to
   > see those words. If they want more details then they might be savvy
   > enough to hunt those down, or perhaps they'll come asking.
   
   Uh, that is not clear to me so I am not sure the average user would know
   about it.  I would probably try to explain that one in a way that can be
   understood, like LIMIT in subqueries affecting the outer query sort
   performance.
   
   -- 
     Bruce Momjian  <bruce@momjian.us>        http://momjian.us
     EnterpriseDB                             http://enterprisedb.com
   
   + As you are, so once was I.  As I am, so you will be. +
   +                      Ancient Roman grave inscription +