Optimizer items in the release notes

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:

/messages/by-id/20181010220601.GA7807@momjian.us

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:

/messages/by-id/20181010220601.GA7807@momjian.us

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.

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 +