Thread: Optimizer items in the release notes
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 +
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,
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
As a user, I am interested in the optimizer changes for sure, and I actually had wished they were highlighted more in previous releases.
> there about this sort of thing the better.
> 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.
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 +
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
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 +
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
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 +