pgsql: Charge cpu_tuple_cost * 0.5 for Append and MergeAppend nodes.

Charge cpu_tuple_cost * 0.5 for Append and MergeAppend nodes.

Previously, Append didn't charge anything at all, and MergeAppend
charged only cpu_operator_cost, about half the value used here. This
change might make MergeAppend plans slightly more likely to be chosen
than before, since this commit increases the assumed cost for Append
-- with default values -- by 0.005 per tuple but MergeAppend by only
0.0025 per tuple. Since the comparisons required by MergeAppend are
costed separately, it's not clear why MergeAppend needs to be
otherwise more expensive than Append, so hopefully this is OK.

Prior to partition-wise join, it didn't really matter whether or not
an Append node had any cost of its own, because every plan had to use
the same number of Append or MergeAppend nodes and in the same places.
Only the relative cost of Append vs. MergeAppend made a difference.
Now, however, it is possible to avoid some of the Append nodes using a
partition-wise join, so it's worth making an effort. Pending patches
for partition-wise aggregate care too, because an Append of Aggregate
nodes will incur the Append overhead fewer times than an Aggregate
over an Append. Although in most cases this change will favor the use
of partition-wise techniques, it does the opposite when the join
cardinality is greater than the sum of the input cardinalities. Since
this situation arises in an existing regression test, I [rhaas]
adjusted it to keep the overall plan shape approximately the same.

Jeevan Chalke, per a suggestion from David Rowley. Reviewed by
Ashutosh Bapat. Some changes by me. The larger patch series of which
this patch is a part was also reviewed and tested by Antonin Houska,
Rajkumar Raghuwanshi, David Rowley, Dilip Kumar, Konstantin Knizhnik,
Pascal Legrand, Rafia Sabih, and me.

Discussion: http://postgr.es/m/CAKJS1f9UXdk6ZYyqbJnjFO9a9hyHKGW7B=ZRh-rxy9qxfPA5Gw@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/7d8ac9814bc9bb6df2d845dbabed38d7284c7c2c

Modified Files
--------------
src/backend/optimizer/path/costsize.c | 26 +++--
src/test/regress/expected/partition_join.out | 136 +++++++++++++--------------
src/test/regress/expected/subselect.out | 4 +-
src/test/regress/sql/partition_join.sql | 8 +-
4 files changed, 91 insertions(+), 83 deletions(-)

Robert Haas <rhaas(at)postgresql(dot)org> writes:
> Charge cpu_tuple_cost * 0.5 for Append and MergeAppend nodes.

This seems to have produced some plan instability in the buildfarm.

regards, tom lane

On Thu, Feb 22, 2018 at 12:35 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <rhaas(at)postgresql(dot)org> writes:
>> Charge cpu_tuple_cost * 0.5 for Append and MergeAppend nodes.
>
> This seems to have produced some plan instability in the buildfarm.

I was worried about that. Looking at it now. I wish we had some way
to figure out whether a given plan was likely to be unstable on the
buildfarm other than committing it and seeing what happens. It's
surprisingly difficult to write non-trivial tests that produce
consistent EXPLAIN output, and annoyingly time-consuming to try to
figure out why they don't.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, Feb 22, 2018 at 8:31 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Feb 22, 2018 at 12:35 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <rhaas(at)postgresql(dot)org> writes:
>>> Charge cpu_tuple_cost * 0.5 for Append and MergeAppend nodes.
>>
>> This seems to have produced some plan instability in the buildfarm.
>
> I was worried about that. Looking at it now. I wish we had some way
> to figure out whether a given plan was likely to be unstable on the
> buildfarm other than committing it and seeing what happens. It's
> surprisingly difficult to write non-trivial tests that produce
> consistent EXPLAIN output, and annoyingly time-consuming to try to
> figure out why they don't.

I pushed a commit to try to fix this by making the three tables being
joined not all identical in terms of row count. That seems like a
clear improvement, but there's every possibility it won't solve the
problem entirely. I'll keep an eye on the buildfarm and see what
happens.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company