MIME-Version: 1.0
References: 
 <CAKJS1f-pCFYGq1-go+ZyAR3GisQo-nTD=7E8ykk7Zqm9w+-OkA@mail.gmail.com>
 <e19c41f2-0c6a-dd0b-6289-4319005861b6@lab.ntt.co.jp>
 <20190516182628.kbkiajfnlooocgyg@momjian.us>
 <0cf10a27-c6a0-de4a-cd20-ab7493ea7422@lab.ntt.co.jp>
 <20190517153653.pmolnulev6imwsn3@momjian.us>
 <CAKJS1f_c8tngWJZjNcQY0KXHWVs4JZxyzoX_cGBEHbaWKUAC=w@mail.gmail.com>
 <20190518023017.nkucoc5xgoebtvfw@momjian.us>
 <CAKJS1f8R8riwBXw==7ijV=UZNuhP+3qXgDBKSiM+=_cTf4mXXw@mail.gmail.com>
 <20190518132031.zjyxgvys7unan53f@momjian.us>
 <CAKJS1f9TVMvGQdwxMemci34viPZuiLTFOJa4rc1Bfhm57WycFA@mail.gmail.com>
 <20190521145509.s6bme6pgww3glmku@momjian.us>
In-Reply-To: <20190521145509.s6bme6pgww3glmku@momjian.us>
From: David Rowley <david.rowley@2ndquadrant.com>
Date: Wed, 22 May 2019 12:33:10 +1200
Message-ID: 
 <CAKJS1f9FL6oKd52PLddKsbjkX26JHp5nMRxVA-W9Uf_1a9TwgA@mail.gmail.com>
Subject: Re: PostgreSQL 12: Feature Highlights
To: Bruce Momjian <bruce@momjian.us>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
	"Jonathan S. Katz" <jkatz@postgresql.org>,
 pgsql-advocacy@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Precedence: bulk

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