PostgreSQL 12: Feature Highlights

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
63 messages Options
1234
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Amit Langote-2
On 2019/05/17 3:26, Bruce Momjian wrote:
> I think the more specific we make the partition description, the more
> limited it will appear to be.  I think almost all partition operations
> will appear to be faster with PG 12, even if users can't articulate
> exactly why.

I agree that the current description captures at a high level the many
changes that made it possible.  Although, a couple of commits listed with
this item don't have much to do with that description, AFAICT.  Especially
959d00e9d [1], which taught the planner to leverage the ordering imposed
on partitions by range partitioning.  With that commit, getting ordered
output from partitioned tables is now much quicker, especially with a
LIMIT clause.  You can tell that it sounds clearly unrelated to the
description we have now, which is "processing thousands of partitions is
quicker when only small numbers of partitions are touched".  Some users of
partitioning may not be interested in the use case described as vastly
improved, but they may be delighted to hear about items such as the
aforementioned commit.  Also, I suspect that the users whose use cases
pushed them to use partitioning in the first place may also be the ones
who do some of their own research about partitioning and eventually know
many optimizations that are possible with it.  So, it's perhaps a good
idea to let them know about such optimizations through release notes if
it's the only place to put them, which I believe is the case with this
particular item.  There are not that many commits to be taken out of the
existing item and described separately, just this one I think.

That said, my intention is only to point out that the commit is being
mixed with an unrelated item.  Whether or not to list it as a separate
item, I can only give my vote in its favor.

Thanks,
Amit

[1] [959d00e9d] Use Append rather than MergeAppend for scanning ordered



Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Evan Macbeth
In reply to this post by Peter Geoghegan-4
Long time lurker, first time poster. :) 

80% - 90% of the items listed in the release notes aren't interesting
to users, but *which* 80% - 90% it is varies. The release notes don't
have to function as a press release, because we'll have an actual
press release instead.
--
Peter Geoghegan

I think Peter's point here is really important. I read the release notes and the press releases, both, from a very different perspective than others, but I find both very valuable. The release notes benefit from completeness and detail, in my observer's opinion.

Back to my cave.

Evan  

--
Evan Macbeth - Director of Support - Crunchy Data
+1 443-421-0343 - [hidden email] 
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Bruce Momjian
In reply to this post by Alvaro Herrera-9
On Thu, May 16, 2019 at 10:34:13PM -0400, Alvaro Herrera wrote:

> On 2019-May-16, Bruce Momjian wrote:
>
> > The release notes are written for the _average_ reader.
>
> I disagree with this assertion, and frankly I cannot understand why you
> think that's the most useful thing to do.  The release notes are not a
> press release, where you have to make things pretty or understandable to
> everyone.  Users can skip items they don't understand or don't care
> about; but would at least be given the option.  If we don't document,
> we're making the decision for them that they must not care.

The press release is not an exhaustive list of all features, so we can't
just fall back on the press release as a way for non-internals readers
to understand all the features in this release.

Frankly, when I am reading a document, if I hit a few items I don't
understand, I stop reading.  This is why I tend to write in a
generally-accessible level of detail.  You can see this in all my
writings, e.g., blogs.  I don't know how to write differently without
feeling I am being inconsiderate to the reader.

Also, when I say I write for the average reader, I write for the average
person who is likely to read the document, not for the average person in
general.

I suggest you look at how Tom Lane writes the minor release notes for an
example that is better or worse than my style.

--
  Bruce Momjian  <[hidden email]>        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 +


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Bruce Momjian
In reply to this post by Michael Paquier-2
On Fri, May 17, 2019 at 12:50:12PM +0900, Michael Paquier wrote:

> On Thu, May 16, 2019 at 10:21:35PM -0400, Bruce Momjian wrote:
> > I think we expect people to read them fully because how would they know
> > what changed and what new features were added.  I do think allowing more
> > detail by showing the commit messages would be helpful for people who
> > want to drill down on an item.
>
> This point is interesting.  The release notes include in the sgml
> sources references to the related commits.  Could it make sense to
> have links to the main commits of a feature which can be clicked on
> directly from the html docs or such?  I understand that producing the
> notes is a daunting task already, and Bruce does an awesome job.  Just
> wondering if we could make that automated in some way if that helps
> with the user experience...  Anyway, this is unrelated to the topic of
> this thread.  My apologies for the digression.

Yes, I am sure it can be done.  The commits are always in a the same
style as output by src/tools/git_changelog, and always to the far left.
This has been discussed before, but no one felt it was urgent enough.

The good news is that once we do this, all release notes, even the
previous ones, will have this feature.

--
  Bruce Momjian  <[hidden email]>        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 +


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Bruce Momjian
In reply to this post by Evan Macbeth
On Fri, May 17, 2019 at 08:14:33AM -0400, Evan Macbeth wrote:

> Long time lurker, first time poster. :) 
>
>
>     80% - 90% of the items listed in the release notes aren't interesting
>     to users, but *which* 80% - 90% it is varies. The release notes don't
>     have to function as a press release, because we'll have an actual
>     press release instead.
>     --
>     Peter Geoghegan
>
>
> I think Peter's point here is really important. I read the release notes and
> the press releases, both, from a very different perspective than others, but I
> find both very valuable. The release notes benefit from completeness and
> detail, in my observer's opinion.

As I just stated, if the press release was exhaustive, we could have the
release notes be more detailed, but this is not the case.  I don't think
we want to get into a case where the items listed on the preess release
get a different level of detail from the items not listed.


--
  Bruce Momjian  <[hidden email]>        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 +


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Jonathan S. Katz-3
In reply to this post by Amit Langote-2
On 5/14/19 10:31 PM, Amit Langote wrote:

> Hi Jonathan,
>
> Thanks for the updated draft.
>
> On 2019/05/15 11:03, Jonathan S. Katz wrote:
>> Without further ado:
>>
>> # Feature Highlights
>>
>> 1. Indexing
>>
>> - Improvements overall performance to standard (B-tree) indexes with
>> writes as well as with bloat
>> - REINDEX CONCURRENTLY
>> - GiST indexes now support covering indexes (INCLUDE clause)
>> - SP-GiST indexes now support K-NN queries
>> - WAL overhead reduced on GiST, GIN, & SP-GiST index creation
>>
>> 2. Partitioning Improvements
>>
>> - Improved partition pruning, which improves performance on queries over
>> tables with thousands of partitions that only need to use a few partitions
>> - Improvements to COPY performance and ATTACH PARTITION
>> - Allow foreign keys to reference partitioned tables
>
> About the 1st item in "Partitioning Improvements", it's not just partition
> pruning that's gotten better.  How about writing as:
>
>  - Improved performance of processing tables with thousands of partitions
>    for operations that only need to touch a small number of partitions
Thanks, I will use some wording like this:

- Improved performance of processing tables with thousands of partitions
for operations that only need to use a small number of partitions

>
> Per discussion upthread, that covers improvements to both partition
> pruning and tuple routing.
>
> Also, could the title "2. Partitioning Improvements" be trimmed down to
> "2. Partitioning", to look like "1. Indexing" for consistency?

These are just my rough notes and do not reflect how it will read in the
PR itself; however I will keep that in mind.

Thanks,

Jonathan


signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Jonathan S. Katz-3
In reply to this post by Bruce Momjian
On 5/17/19 10:19 AM, Bruce Momjian wrote:

> On Fri, May 17, 2019 at 08:14:33AM -0400, Evan Macbeth wrote:
>> Long time lurker, first time poster. :) 
>>
>>
>>     80% - 90% of the items listed in the release notes aren't interesting
>>     to users, but *which* 80% - 90% it is varies. The release notes don't
>>     have to function as a press release, because we'll have an actual
>>     press release instead.
>>     --
>>     Peter Geoghegan
>>
>>
>> I think Peter's point here is really important. I read the release notes and
>> the press releases, both, from a very different perspective than others, but I
>> find both very valuable. The release notes benefit from completeness and
>> detail, in my observer's opinion.
>
> As I just stated, if the press release was exhaustive, we could have the
> release notes be more detailed, but this is not the case.  I don't think
> we want to get into a case where the items listed on the preess release
> get a different level of detail from the items not listed.
To step back, it's important to understand the intended goals of the
press release.

I view the primary goal of the press release for a major version to be a
springboard to further dive into what is in the release. It helps to
give some global context to new features / changes and is an effective
"getting started" point. It needs just enough details for people to be
interested, and if they want more info, they can go to the release notes
+ docs.

The release notes then can provide additional details on what said
features (effectively a readable "diff" between versions) are, and, if
needed, the documentation can provide even greater details.

(The "diff" point is important: multiple times in recent weeks I've had
to refer back to the PG10 notes to highlight how the behavior for set
returning functions changed in the SELECT lists. So having that there
was certainly helpful.)

In my more heavy practitioner days, I needed both to help analyze and
make decisions on say, when to upgrade to a major version, or

Bringing it full circle, I view the primary goal of the Beta 1 press
release to be a springboard to _testing_ the release: we need people to
test to ensure the GA release is as stable as possible. It should be a
bit more exhaustive than the final GA press release - ideally I want
people to think of uses cases they can test the new features in -- but
sure, it should not be too much more exhaustive.

Thanks,

Jonathan


signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Bruce Momjian
In reply to this post by Amit Langote-2
On Fri, May 17, 2019 at 07:56:55PM +0900, Amit Langote wrote:

> On 2019/05/17 3:26, Bruce Momjian wrote:
> > I think the more specific we make the partition description, the more
> > limited it will appear to be.  I think almost all partition operations
> > will appear to be faster with PG 12, even if users can't articulate
> > exactly why.
>
> I agree that the current description captures at a high level the many
> changes that made it possible.  Although, a couple of commits listed with
> this item don't have much to do with that description, AFAICT.  Especially
> 959d00e9d [1], which taught the planner to leverage the ordering imposed
> on partitions by range partitioning.  With that commit, getting ordered
> output from partitioned tables is now much quicker, especially with a
> LIMIT clause.  You can tell that it sounds clearly unrelated to the
> description we have now, which is "processing thousands of partitions is

Yes, it does.  I added this text and moved the commit item:

        Avoid sorting when partitions are already being scanned in the
        necessary order (David Rowley)

I certainly strugged to understand the maze of commits related to
partitioning.

> quicker when only small numbers of partitions are touched".  Some users of
> partitioning may not be interested in the use case described as vastly
> improved, but they may be delighted to hear about items such as the
> aforementioned commit.  Also, I suspect that the users whose use cases
> pushed them to use partitioning in the first place may also be the ones
> who do some of their own research about partitioning and eventually know
> many optimizations that are possible with it.  So, it's perhaps a good
> idea to let them know about such optimizations through release notes if
> it's the only place to put them, which I believe is the case with this
> particular item.  There are not that many commits to be taken out of the
> existing item and described separately, just this one I think.

Yes.

--
  Bruce Momjian  <[hidden email]>        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 +


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

David Rowley-3
On Sat, 18 May 2019 at 03:36, Bruce Momjian <[hidden email]> wrote:
> Yes, it does.  I added this text and moved the commit item:
>
>         Avoid sorting when partitions are already being scanned in the
>         necessary order (David Rowley)

Thank you for moving that out.

> I certainly strugged to understand the maze of commits related to
> partitioning.

With the utmost respect, because I certainly do appreciate the work
you did on the release note, I think if that's the case, then it
should only make you more willing to listen to the advice from the
people who are closer to those commits.  However I understand that
consistency is also important, so listening to the heckling of
individuals sometimes won't lead to a good overall outcome.  That
being said, I don't think that's what happened here, as most of the
people who had a gripe about it were pretty close to the work that was
done.

FWIW, I do think the release notes should be meant as a source of
information which give a brief view on changes made that have a
reasonable possibility of affecting people (either negative or
positively) who are upgrading. Leaving out important details because
they might confuse a small group of people seems wrong-headed, as it
means the people who are not in that group are left to look at the
commit history to determine what's changed, or worse, they might just
assume that the thing has not changed which could either cause them to
1) Skip the upgrade because it's not interesting to them, or; 2)
having something break because we didn't list some incompatibility.
I know you know better than most people that extracting a useful
summary from the commit history is a pretty mammoth task, so I doubt
we should put that upon the masses.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Peter Geoghegan-4
On Fri, May 17, 2019 at 6:39 PM David Rowley
<[hidden email]> wrote:
> FWIW, I do think the release notes should be meant as a source of
> information which give a brief view on changes made that have a
> reasonable possibility of affecting people (either negative or
> positively) who are upgrading. Leaving out important details because
> they might confuse a small group of people seems wrong-headed

If the only thing that comes out of the developer meeting discussion
item on release notes is that we make the commits behind each listing
*discoverable* from the web, then that will still be a big
improvement. I would prefer it if we went further, but that seems like
it solves a lot of problems without creating new ones.

--
Peter Geoghegan


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Bruce Momjian
In reply to this post by David Rowley-3
On Sat, May 18, 2019 at 01:38:49PM +1200, David Rowley wrote:

> On Sat, 18 May 2019 at 03:36, Bruce Momjian <[hidden email]> wrote:
> > Yes, it does.  I added this text and moved the commit item:
> >
> >         Avoid sorting when partitions are already being scanned in the
> >         necessary order (David Rowley)
>
> Thank you for moving that out.
>
> > I certainly strugged to understand the maze of commits related to
> > partitioning.
>
> With the utmost respect, because I certainly do appreciate the work
> you did on the release note, I think if that's the case, then it
> should only make you more willing to listen to the advice from the
> people who are closer to those commits.  However I understand that
> consistency is also important, so listening to the heckling of
> individuals sometimes won't lead to a good overall outcome.  That
> being said, I don't think that's what happened here, as most of the
> people who had a gripe about it were pretty close to the work that was
> done.
>
> FWIW, I do think the release notes should be meant as a source of
> information which give a brief view on changes made that have a
> reasonable possibility of affecting people (either negative or
> positively) who are upgrading. Leaving out important details because
> they might confuse a small group of people seems wrong-headed, as it
> means the people who are not in that group are left to look at the
> commit history to determine what's changed, or worse, they might just
> assume that the thing has not changed which could either cause them to
> 1) Skip the upgrade because it's not interesting to them, or; 2)
> having something break because we didn't list some incompatibility.
> I know you know better than most people that extracting a useful
> summary from the commit history is a pretty mammoth task, so I doubt
> we should put that upon the masses.

No one has suggested new wording, so I don't know what you are
complaining about.  In fact, the wording we have now is by Amit Langote.

--
  Bruce Momjian  <[hidden email]>        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 +


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Magnus Hagander-2
In reply to this post by Jonathan S. Katz-3
On Fri, May 17, 2019 at 4:31 PM Jonathan S. Katz <[hidden email]> wrote:
On 5/17/19 10:19 AM, Bruce Momjian wrote:
> On Fri, May 17, 2019 at 08:14:33AM -0400, Evan Macbeth wrote:
>> Long time lurker, first time poster. :) 
>>
>>
>>     80% - 90% of the items listed in the release notes aren't interesting
>>     to users, but *which* 80% - 90% it is varies. The release notes don't
>>     have to function as a press release, because we'll have an actual
>>     press release instead.
>>     --
>>     Peter Geoghegan
>>
>>
>> I think Peter's point here is really important. I read the release notes and
>> the press releases, both, from a very different perspective than others, but I
>> find both very valuable. The release notes benefit from completeness and
>> detail, in my observer's opinion.
>
> As I just stated, if the press release was exhaustive, we could have the
> release notes be more detailed, but this is not the case.  I don't think
> we want to get into a case where the items listed on the preess release
> get a different level of detail from the items not listed.

To step back, it's important to understand the intended goals of the
press release.

I view the primary goal of the press release for a major version to be a
springboard to further dive into what is in the release. It helps to
give some global context to new features / changes and is an effective
"getting started" point. It needs just enough details for people to be
interested, and if they want more info, they can go to the release notes
+ docs.

The release notes then can provide additional details on what said
features (effectively a readable "diff" between versions) are, and, if
needed, the documentation can provide even greater details.

There is in fact a third layer to consider as well, which is the release announcement. This can (and sometimes are) different from the press release.

So you have the press release. Then more details can be added to the release announcement. Then even more details can be added to the release notes.

--
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

David Rowley-3
In reply to this post by Bruce Momjian
On Sat, 18 May 2019 at 14:30, Bruce Momjian <[hidden email]> wrote:
> No one has suggested new wording, so I don't know what you are
> complaining about.  In fact, the wording we have now is by Amit Langote.

https://www.postgresql.org/message-id/CAKJS1f8R6DC45bauzeGF-QMaQ90B_NFSJB9mvVOuhKVDkajehg@...

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Bruce Momjian
On Sat, May 18, 2019 at 11:34:34PM +1200, David Rowley wrote:
> On Sat, 18 May 2019 at 14:30, Bruce Momjian <[hidden email]> wrote:
> > No one has suggested new wording, so I don't know what you are
> > complaining about.  In fact, the wording we have now is by Amit Langote.
>
> https://www.postgresql.org/message-id/CAKJS1f8R6DC45bauzeGF-QMaQ90B_NFSJB9mvVOuhKVDkajehg@...

The second item I added yesterday at the prompting of Amit:

        commit 05685897f0
        Author: Bruce Momjian <[hidden email]>
        Date:   Fri May 17 11:31:49 2019 -0400
       
            docs:  split out sort-skip partition item in PG 12 release notes
       
            Discussion:
        https://postgr.es/m/0cf10a27-c6a0-de4a-cd20-ab7493ea7422@...

        > Avoid sorting when partitions are already being scanned in the
        > necessary order (David Rowley)

The first one needs to reference visible effects, so I would add:

        Consider additional optimizations for queries referencing
        partitioned tables that only affect a single partition

Does that work?

It seems you never got a reply to that email, perhaps because I thought
Amit's next email was covering the same issue.  I am sorry for that.

--
  Bruce Momjian  <[hidden email]>        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 +


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Bruce Momjian
In reply to this post by Bruce Momjian
On Tue, May 14, 2019 at 09:19:45AM -0400, Bruce Momjian wrote:

> On Tue, May 14, 2019 at 06:25:43PM +0900, Amit Langote wrote:
> > Considering the quoted discussion here, maybe it's a good idea to note
> > that only the operations that need to touch a small number of partitions
> > are now processed efficiently, which covers both SELECT/UPDATE/DELETE that
> > benefit from improved pruning efficiency and INSERT that benefit from
> > improved tuple routing efficiency.  So, maybe:
> >
> > Tables with thousands of child partitions can now be processed
> > efficiently by operations that only need to touch a small number
> >         of partitions.
> >
> > That is, as I mentioned above, as opposed to queries that need to process
> > all partitions (such as, select count(*) from partitioned_table), which
> > don't perform any faster in v12 than in v11.  The percentage of users who
> > run such workloads on PostgreSQL may be much smaller today, but perhaps
> > it's not a good idea to mislead them into thinking that *everything* with
> > partitioned tables is now faster even with thousands of partitions.
>
> Agreed, I changed it to your wording.

I tightened up the wording on this item, and removed 'touch' since that
could suggest 'write':

        Allow tables with thousands of child partitions to be processed
        efficiently by operations that only affect a small number of
        partitions.

--
  Bruce Momjian  <[hidden email]>        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 +


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

David Rowley-3
In reply to this post by Bruce Momjian
On Sun, 19 May 2019 at 01:20, Bruce Momjian <[hidden email]> wrote:
> The first one needs to reference visible effects, so I would add:
>
>         Consider additional optimizations for queries referencing
>         partitioned tables that only affect a single partition
>
> Does that work?

Thanks, typing that up.  I think it lacks a bit detail about what's
actually changed.  The change is fairly evident and people can see
when it takes effect when they look at EXPLAIN and see that the
Append/MergeAppend node is missing. Also, an Append/MergeAppend may
exist for inheritance tables and an Append can exist for a simple
UNION ALL. Both of those cases can have subplans removed to leave only
a single subplan, to which the additional parallel paths will be
considered.

I think something like:

* Make the optimizer only include an Append/MergeAppend node when
there is more than one subplan to append.

This reduces execution overheads and allows additional plan shapes
that were previously not possible.

or a little more brief:

* Allow the optimizer to consider more plan types by eliminating
single subplan Append and MergeAppends.

I understand that you might be trying to avoid details of plan node
types, but really anyone who's got inheritance or partitioned tables
and has looked at an EXPLAIN should have an idea.

Also, I think Tom should be the main author of this as he rewrote my
version of the patch to such an extent that there was next to nothing
of it left. I just tagged a few extra things on before he committed
it.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Amit Langote-2
In reply to this post by Bruce Momjian
On 2019/05/18 0:36, Bruce Momjian wrote:

> On Fri, May 17, 2019 at 07:56:55PM +0900, Amit Langote wrote:
>> I agree that the current description captures at a high level the many
>> changes that made it possible.  Although, a couple of commits listed with
>> this item don't have much to do with that description, AFAICT.  Especially
>> 959d00e9d [1], which taught the planner to leverage the ordering imposed
>> on partitions by range partitioning.  With that commit, getting ordered
>> output from partitioned tables is now much quicker, especially with a
>> LIMIT clause.  You can tell that it sounds clearly unrelated to the
>> description we have now, which is "processing thousands of partitions is
>
> Yes, it does.  I added this text and moved the commit item:
>
>         Avoid sorting when partitions are already being scanned in the
>         necessary order (David Rowley)

Thank you Bruce.

Regards,
Amit



Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Bruce Momjian
In reply to this post by David Rowley-3
On Sun, May 19, 2019 at 02:26:48AM +1200, David Rowley wrote:

> On Sun, 19 May 2019 at 01:20, Bruce Momjian <[hidden email]> wrote:
> > The first one needs to reference visible effects, so I would add:
> >
> >         Consider additional optimizations for queries referencing
> >         partitioned tables that only affect a single partition
> >
> > Does that work?
>
> Thanks, typing that up.  I think it lacks a bit detail about what's
> actually changed.  The change is fairly evident and people can see
> when it takes effect when they look at EXPLAIN and see that the
> Append/MergeAppend node is missing. Also, an Append/MergeAppend may
> exist for inheritance tables and an Append can exist for a simple
> UNION ALL. Both of those cases can have subplans removed to leave only
> a single subplan, to which the additional parallel paths will be
> considered.

This brings up a few points.  First, it seems the change affects
partitioned tables and UNION ALL, which means it probably needs to be
listed in two sections.   Second, is it only parallelism paths that are
added?  I am not sure if people care about a node being removed,
especially when the might not even know we do that step, but they do
care if there are new optimization possibilities.

> I think something like:
>
> * Make the optimizer only include an Append/MergeAppend node when
> there is more than one subplan to append.
>
> This reduces execution overheads and allows additional plan shapes
> that were previously not possible.
>
> or a little more brief:
>
> * Allow the optimizer to consider more plan types by eliminating
> single subplan Append and MergeAppends.
>
> I understand that you might be trying to avoid details of plan node
> types, but really anyone who's got inheritance or partitioned tables
> and has looked at an EXPLAIN should have an idea.

I would like to have something that people who don't study EXPLAIN will
still be excited about.

> Also, I think Tom should be the main author of this as he rewrote my
> version of the patch to such an extent that there was next to nothing
> of it left. I just tagged a few extra things on before he committed
> it.

Uh, Tom listed you as author, but I don't have to follow that if you
feel it is inaccurate.

--
  Bruce Momjian  <[hidden email]>        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 +


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

Amit Langote-2
On 2019/05/21 23:55, Bruce Momjian wrote:

> On Sun, May 19, 2019 at 02:26:48AM +1200, David Rowley wrote:
>> Thanks, typing that up.  I think it lacks a bit detail about what's
>> actually changed.  The change is fairly evident and people can see
>> when it takes effect when they look at EXPLAIN and see that the
>> Append/MergeAppend node is missing. Also, an Append/MergeAppend may
>> exist for inheritance tables and an Append can exist for a simple
>> UNION ALL. Both of those cases can have subplans removed to leave only
>> a single subplan, to which the additional parallel paths will be
>> considered.
>
> This brings up a few points.  First, it seems the change affects
> partitioned tables and UNION ALL, which means it probably needs to be
> listed in two sections.

How about putting this only in E.1.3.1.3. Optimizer?

Thanks,
Amit



Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL 12: Feature Highlights

David Rowley-3
In reply to this post by Bruce Momjian
On Wed, 22 May 2019 at 02:55, Bruce Momjian <[hidden email]> wrote:

>
> On Sun, May 19, 2019 at 02:26:48AM +1200, David Rowley wrote:
> > On Sun, 19 May 2019 at 01:20, Bruce Momjian <[hidden email]> wrote:
> > > The first one needs to reference visible effects, so I would add:
> > >
> > >         Consider additional optimizations for queries referencing
> > >         partitioned tables that only affect a single partition
> > >
> > > Does that work?
> >
> > Thanks, typing that up.  I think it lacks a bit detail about what's
> > actually changed.  The change is fairly evident and people can see
> > when it takes effect when they look at EXPLAIN and see that the
> > Append/MergeAppend node is missing. Also, an Append/MergeAppend may
> > exist for inheritance tables and an Append can exist for a simple
> > UNION ALL. Both of those cases can have subplans removed to leave only
> > a single subplan, to which the additional parallel paths will be
> > considered.
>
> This brings up a few points.  First, it seems the change affects
> partitioned tables and UNION ALL, which means it probably needs to be
> listed in two sections.   Second, is it only parallelism paths that are
> added?  I am not sure if people care about a node being removed,
> especially when the might not even know we do that step, but they do
> care if there are new optimization possibilities.

Like Amit, I think the optimizer section is fine.  Another thing that
is affected is that you may no longer get a Materialize node in the
plan.  Previously you might have gotten something like Merge Join ->
Materialize -> Append -> Seq Scan, now you might just get Merge Join
-> Seq Scan.  This is because Append / MergeAppend don't support mark
and restore. Removing them would allow the materialize node to be
skipped in cases where the single subpath of the Append does support
mark and restore.

> > I think something like:
> >
> > * Make the optimizer only include an Append/MergeAppend node when
> > there is more than one subplan to append.
> >
> > This reduces execution overheads and allows additional plan shapes
> > that were previously not possible.
> >
> > or a little more brief:
> >
> > * Allow the optimizer to consider more plan types by eliminating
> > single subplan Append and MergeAppends.
> >
> > I understand that you might be trying to avoid details of plan node
> > types, but really anyone who's got inheritance or partitioned tables
> > and has looked at an EXPLAIN should have an idea.
>
> I would like to have something that people who don't study EXPLAIN will
> still be excited about.

hmm. I guess it's a pretty hard balance between writing something
where there's so little detail that nobody really knows what it means
vs adding some detail that some people might not understand.

> > Also, I think Tom should be the main author of this as he rewrote my
> > version of the patch to such an extent that there was next to nothing
> > of it left. I just tagged a few extra things on before he committed
> > it.
>
> Uh, Tom listed you as author, but I don't have to follow that if you
> feel it is inaccurate.

I'd say he was being very generous. I'm happy to come 2nd on this one.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


1234