Re: PostgreSQL 12: Feature Highlights - Mailing list pgsql-advocacy

On Wed, 22 May 2019 at 02:55, Bruce Momjian <bruce@momjian.us> wrote:
>
> On Sun, May 19, 2019 at 02:26:48AM +1200, David Rowley wrote:
> > On Sun, 19 May 2019 at 01:20, Bruce Momjian <bruce@momjian.us> 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