You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by ma...@apache.org on 2025/02/07 09:28:34 UTC
(cloudberry) 20/34: [ORCA] Update index cost model to account for INCLUDE columns (#15697)
This is an automated email from the ASF dual-hosted git repository.
maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 4501a6984689b38d01b4f7e7f10ede9589d0f43c
Author: David Kimura <dk...@vmware.com>
AuthorDate: Fri Jun 23 12:01:01 2023 -0700
[ORCA] Update index cost model to account for INCLUDE columns (#15697)
After commit b9f43b93, ORCA supports cover INCLUDE index columns. But
the cost model does not yet account for those columns. An INCLUDE column
is extra payload embedded into the physical index file. That requires
extra I/O to read tuples from the index that is proportional to the
width of the INCLUDE columns. In short, ORCA should always perform
index-only-scan using the index with the narrowest width that satisfies
the query.
For example:
```
CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 int, c5 int);
CREATE INDEX idx_large ON t1 USING btree(c1) INCLUDE (c2, c3, c4, c5);
CREATE INDEX idx_c1 ON t1 USING btree(c1);
EXPLAIN ANALYZE SELECT c1 FROM t1 WHERE c1= 4;
```
Given that both indexes completely cover the predicate and project
columns, it is always better to use the index with the smallest payload.
In this case, that's idx_c1 (idx_large contains columns c2, c3, c4, c5
that are unused in the query whereas idx_c1 contains 0 unused columns).
An additional related change is to update the usage of relallvisible
fraction to cost index-only-scan more correctly. Previously, the value
was multiplied by a constant representing the random I/O cost to
retrieve tuples from the heap that are referenced from the index. That
sort of make sense, because if heap is all visible then no heap accesses
are required which presumably meant no random page accesses. However,
depending on the index implementation, I think there may still be random
page cost in reading the index itself.
For now, remove the previous usage because some ANALYZE'd regress tests
suggest it still needs to be accounted for, although maybe via a custom
constant. Instead, use relallvisible to proportionally calculate the I/O
cost of accessing the whole row from a heap as opposed to just the index
itself (which may be narrower). That should provide a more accurate I/O
cost.
In order to more clearly define the cost formula a new cost parameter
EcpIndexOnlyScanTupCostUnit is added to distinguish between the scan
cost of reading both the index and heap (EcpIndexScanTupCostUnit) as
opposed to just the index. For now they are the same values, but we can
tweak in the future as needed.
Really though, these cost unit parameters are a bit deceiving. The
reason that wider indexes/tables are more expensive is not so much due
to the IO of reading a wider row from the page. Instead, it is likely
due to the subsequent effect of needing to page more blocks from disk in
and out of memory. Unfortunately, these parameters do not really take
that into account.
Intent of this parameter is to be a stop-gap until we create a new cost
model that directly takes pages into account, similar to planner (e.g.
cost_index).
---
.../gporca/data/dxl/minidump/CoveringIndex-1.mdp | 4 +-
.../gporca/data/dxl/minidump/CoveringIndex-2.mdp | 4 +-
.../data/dxl/minidump/CoveringIndex-Cost-1.mdp | 393 +++++++
.../data/dxl/minidump/CoveringIndex-Cost-2.mdp | 394 +++++++
.../gporca/data/dxl/minidump/IndexOnlyScan-CTE.mdp | 4 +-
.../include/gpdbcost/CCostModelParamsGPDB.h | 6 +-
.../gporca/libgpdbcost/src/CCostModelGPDB.cpp | 68 +-
.../libgpdbcost/src/CCostModelParamsGPDB.cpp | 10 +-
src/backend/gporca/server/CMakeLists.txt | 2 +-
src/test/regress/expected/gp_covering_index.out | 19 +-
.../expected/gp_covering_index_optimizer.out | 7 +-
src/test/regress/expected/join_optimizer.out | 17 +-
.../regress/expected/select_parallel_optimizer.out | 1239 ++++++++++++++++++++
.../regress/expected/subselect_gp_optimizer.out | 62 +-
src/test/regress/sql/gp_covering_index.sql | 7 +-
15 files changed, 2163 insertions(+), 73 deletions(-)
diff --git a/src/backend/gporca/data/dxl/minidump/CoveringIndex-1.mdp b/src/backend/gporca/data/dxl/minidump/CoveringIndex-1.mdp
index 6e51bc41b3..4700dba04b 100644
--- a/src/backend/gporca/data/dxl/minidump/CoveringIndex-1.mdp
+++ b/src/backend/gporca/data/dxl/minidump/CoveringIndex-1.mdp
@@ -253,7 +253,7 @@
<dxl:Plan Id="0" SpaceSize="3">
<dxl:GatherMotion InputSegments="0,1,2" OutputSegments="-1">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="0.058531" Rows="284.444444" Width="8"/>
+ <dxl:Cost StartupCost="0" TotalCost="6.043956" Rows="284.444444" Width="8"/>
</dxl:Properties>
<dxl:ProjList>
<dxl:ProjElem ColId="0" Alias="a">
@@ -267,7 +267,7 @@
<dxl:SortingColumnList/>
<dxl:IndexOnlyScan IndexScanDirection="Forward">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="0.048640" Rows="284.444444" Width="8"/>
+ <dxl:Cost StartupCost="0" TotalCost="6.034065" Rows="284.444444" Width="8"/>
</dxl:Properties>
<dxl:ProjList>
<dxl:ProjElem ColId="0" Alias="a">
diff --git a/src/backend/gporca/data/dxl/minidump/CoveringIndex-2.mdp b/src/backend/gporca/data/dxl/minidump/CoveringIndex-2.mdp
index 8e26626c86..1cb1f0af84 100644
--- a/src/backend/gporca/data/dxl/minidump/CoveringIndex-2.mdp
+++ b/src/backend/gporca/data/dxl/minidump/CoveringIndex-2.mdp
@@ -1054,7 +1054,7 @@
<dxl:Plan Id="0" SpaceSize="3">
<dxl:GatherMotion InputSegments="0,1,2" OutputSegments="-1">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="6.207420" Rows="957.000000" Width="8"/>
+ <dxl:Cost StartupCost="0" TotalCost="6.216760" Rows="957.000000" Width="8"/>
</dxl:Properties>
<dxl:ProjList>
<dxl:ProjElem ColId="0" Alias="a">
@@ -1068,7 +1068,7 @@
<dxl:SortingColumnList/>
<dxl:IndexScan IndexScanDirection="Forward">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="6.178889" Rows="957.000000" Width="8"/>
+ <dxl:Cost StartupCost="0" TotalCost="6.188229" Rows="957.000000" Width="8"/>
</dxl:Properties>
<dxl:ProjList>
<dxl:ProjElem ColId="0" Alias="a">
diff --git a/src/backend/gporca/data/dxl/minidump/CoveringIndex-Cost-1.mdp b/src/backend/gporca/data/dxl/minidump/CoveringIndex-Cost-1.mdp
new file mode 100644
index 0000000000..4dcddd05cb
--- /dev/null
+++ b/src/backend/gporca/data/dxl/minidump/CoveringIndex-Cost-1.mdp
@@ -0,0 +1,393 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<dxl:DXLMessage xmlns:dxl="http://greenplum.com/dxl/2010/12/">
+ <dxl:Comment><![CDATA[
+ Objective: Given multple indexes that can all satisfy a query prediate and
+ cover an index-only scan, use the index with the smallest include columns.
+
+ CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int);
+ INSERT INTO t1 SELECT i%5, i%5, i%5, i%5, i%5, i%5, i%5, i%5, i%5, i%5 FROM generate_series(1, 10000)i;
+ VACUUM ANALYZE t1;
+
+ CREATE INDEX idx_9_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3, c4, c5, c6, c7, c8, c9, c10);
+ CREATE INDEX idx_8_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3, c4, c5, c6, c7, c8, c9);
+ CREATE INDEX idx_7_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3, c4, c5, c6, c7, c8);
+ CREATE INDEX idx_6_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3, c4, c5, c6, c7);
+ CREATE INDEX idx_5_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3, c4, c5, c6);
+ CREATE INDEX idx_0_include_cols ON t1 USING btree(c1);
+ CREATE INDEX idx_4_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3, c4, c5);
+ CREATE INDEX idx_3_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3, c4);
+ CREATE INDEX idx_2_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3);
+ CREATE INDEX idx_1_include_cols ON t1 USING btree(c1) INCLUDE (c2);
+
+ EXPLAIN ANALYZE SELECT c1 FROM t1 WHERE c1= 4;
+ ]]>
+ </dxl:Comment>
+ <dxl:Thread Id="0">
+ <dxl:OptimizerConfig>
+ <dxl:EnumeratorConfig Id="0" PlanSamples="0" CostThreshold="0"/>
+ <dxl:StatisticsConfig DampingFactorFilter="0.750000" DampingFactorJoin="0.000000" DampingFactorGroupBy="0.750000" MaxStatsBuckets="100"/>
+ <dxl:CTEConfig CTEInliningCutoff="0"/>
+ <dxl:WindowOids RowNumber="3100" Rank="3101"/>
+ <dxl:CostModelConfig CostModelType="1" SegmentsForCosting="3">
+ <dxl:CostParams>
+ <dxl:CostParam Name="NLJFactor" Value="1024.000000" LowerBound="1023.500000" UpperBound="1024.500000"/>
+ </dxl:CostParams>
+ </dxl:CostModelConfig>
+ <dxl:Hint JoinArityForAssociativityCommutativity="18" ArrayExpansionThreshold="20" JoinOrderDynamicProgThreshold="10" BroadcastThreshold="100000" EnforceConstraintsOnDML="false" PushGroupByBelowSetopThreshold="10" XformBindThreshold="0" SkewFactor="0"/>
+ <dxl:TraceFlags Value="102001,102002,102003,102043,102074,102120,102144,103001,103014,103015,103022,103026,103027,103029,103033,103038,103040,104002,104003,104004,104005,105000,106000"/>
+ </dxl:OptimizerConfig>
+ <dxl:Metadata SystemIds="0.GPDB">
+ <dxl:Index Mdid="7.41017.1.0" Name="idx_2_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41016.1.0" Name="idx_3_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2,3">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41018.1.0" Name="idx_1_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41013.1.0" Name="idx_5_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2,3,4,5">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41012.1.0" Name="idx_6_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2,3,4,5,6">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41015.1.0" Name="idx_4_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2,3,4">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41014.1.0" Name="idx_0_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41009.1.0" Name="idx_9_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2,3,4,5,6,7,8,9">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41011.1.0" Name="idx_7_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2,3,4,5,6,7">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41010.1.0" Name="idx_8_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2,3,4,5,6,7,8">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Type Mdid="0.16.1.0" Name="bool" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="1" PassByValue="true">
+ <dxl:DistrOpfamily Mdid="0.2222.1.0"/>
+ <dxl:LegacyDistrOpfamily Mdid="0.7124.1.0"/>
+ <dxl:PartOpfamily Mdid="0.424.1.0"/>
+ <dxl:EqualityOp Mdid="0.91.1.0"/>
+ <dxl:InequalityOp Mdid="0.85.1.0"/>
+ <dxl:LessThanOp Mdid="0.58.1.0"/>
+ <dxl:LessThanEqualsOp Mdid="0.1694.1.0"/>
+ <dxl:GreaterThanOp Mdid="0.59.1.0"/>
+ <dxl:GreaterThanEqualsOp Mdid="0.1695.1.0"/>
+ <dxl:ComparisonOp Mdid="0.1693.1.0"/>
+ <dxl:ArrayType Mdid="0.1000.1.0"/>
+ <dxl:MinAgg Mdid="0.0.0.0"/>
+ <dxl:MaxAgg Mdid="0.0.0.0"/>
+ <dxl:AvgAgg Mdid="0.0.0.0"/>
+ <dxl:SumAgg Mdid="0.0.0.0"/>
+ <dxl:CountAgg Mdid="0.2147.1.0"/>
+ </dxl:Type>
+ <dxl:Type Mdid="0.23.1.0" Name="int4" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true">
+ <dxl:DistrOpfamily Mdid="0.1977.1.0"/>
+ <dxl:LegacyDistrOpfamily Mdid="0.7100.1.0"/>
+ <dxl:PartOpfamily Mdid="0.1976.1.0"/>
+ <dxl:EqualityOp Mdid="0.96.1.0"/>
+ <dxl:InequalityOp Mdid="0.518.1.0"/>
+ <dxl:LessThanOp Mdid="0.97.1.0"/>
+ <dxl:LessThanEqualsOp Mdid="0.523.1.0"/>
+ <dxl:GreaterThanOp Mdid="0.521.1.0"/>
+ <dxl:GreaterThanEqualsOp Mdid="0.525.1.0"/>
+ <dxl:ComparisonOp Mdid="0.351.1.0"/>
+ <dxl:ArrayType Mdid="0.1007.1.0"/>
+ <dxl:MinAgg Mdid="0.2132.1.0"/>
+ <dxl:MaxAgg Mdid="0.2116.1.0"/>
+ <dxl:AvgAgg Mdid="0.2101.1.0"/>
+ <dxl:SumAgg Mdid="0.2108.1.0"/>
+ <dxl:CountAgg Mdid="0.2147.1.0"/>
+ </dxl:Type>
+ <dxl:RelationStatistics Mdid="2.41006.1.0" Name="t1" Rows="10000.000000" RelPages="22" RelAllVisible="22" EmptyRelation="false"/>
+ <dxl:Type Mdid="0.26.1.0" Name="oid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true">
+ <dxl:DistrOpfamily Mdid="0.1990.1.0"/>
+ <dxl:LegacyDistrOpfamily Mdid="0.7109.1.0"/>
+ <dxl:PartOpfamily Mdid="0.1989.1.0"/>
+ <dxl:EqualityOp Mdid="0.607.1.0"/>
+ <dxl:InequalityOp Mdid="0.608.1.0"/>
+ <dxl:LessThanOp Mdid="0.609.1.0"/>
+ <dxl:LessThanEqualsOp Mdid="0.611.1.0"/>
+ <dxl:GreaterThanOp Mdid="0.610.1.0"/>
+ <dxl:GreaterThanEqualsOp Mdid="0.612.1.0"/>
+ <dxl:ComparisonOp Mdid="0.356.1.0"/>
+ <dxl:ArrayType Mdid="0.1028.1.0"/>
+ <dxl:MinAgg Mdid="0.2134.1.0"/>
+ <dxl:MaxAgg Mdid="0.2118.1.0"/>
+ <dxl:AvgAgg Mdid="0.0.0.0"/>
+ <dxl:SumAgg Mdid="0.0.0.0"/>
+ <dxl:CountAgg Mdid="0.2147.1.0"/>
+ </dxl:Type>
+ <dxl:Type Mdid="0.27.1.0" Name="tid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="6" PassByValue="false">
+ <dxl:DistrOpfamily Mdid="0.2227.1.0"/>
+ <dxl:LegacyDistrOpfamily Mdid="0.7110.1.0"/>
+ <dxl:PartOpfamily Mdid="0.2789.1.0"/>
+ <dxl:EqualityOp Mdid="0.387.1.0"/>
+ <dxl:InequalityOp Mdid="0.402.1.0"/>
+ <dxl:LessThanOp Mdid="0.2799.1.0"/>
+ <dxl:LessThanEqualsOp Mdid="0.2801.1.0"/>
+ <dxl:GreaterThanOp Mdid="0.2800.1.0"/>
+ <dxl:GreaterThanEqualsOp Mdid="0.2802.1.0"/>
+ <dxl:ComparisonOp Mdid="0.2794.1.0"/>
+ <dxl:ArrayType Mdid="0.1010.1.0"/>
+ <dxl:MinAgg Mdid="0.2798.1.0"/>
+ <dxl:MaxAgg Mdid="0.2797.1.0"/>
+ <dxl:AvgAgg Mdid="0.0.0.0"/>
+ <dxl:SumAgg Mdid="0.0.0.0"/>
+ <dxl:CountAgg Mdid="0.2147.1.0"/>
+ </dxl:Type>
+ <dxl:Relation Mdid="6.41006.1.0" Name="t1" IsTemporary="false" StorageType="Heap" DistributionPolicy="Hash" DistributionColumns="0" Keys="16,10">
+ <dxl:Columns>
+ <dxl:Column Name="c1" Attno="1" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c2" Attno="2" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c3" Attno="3" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c4" Attno="4" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c5" Attno="5" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c6" Attno="6" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c7" Attno="7" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c8" Attno="8" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c9" Attno="9" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c10" Attno="10" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="ctid" Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="xmin" Attno="-2" Mdid="0.28.1.0" Nullable="false" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="cmin" Attno="-3" Mdid="0.29.1.0" Nullable="false" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="xmax" Attno="-4" Mdid="0.28.1.0" Nullable="false" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="cmax" Attno="-5" Mdid="0.29.1.0" Nullable="false" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="tableoid" Attno="-6" Mdid="0.26.1.0" Nullable="false" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="gp_segment_id" Attno="-7" Mdid="0.23.1.0" Nullable="false" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ </dxl:Columns>
+ <dxl:IndexInfoList>
+ <dxl:IndexInfo Mdid="7.41009.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41010.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41011.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41012.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41013.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41014.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41015.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41016.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41017.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41018.1.0" IsPartial="false"/>
+ </dxl:IndexInfoList>
+ <dxl:CheckConstraints/>
+ <dxl:DistrOpfamilies>
+ <dxl:DistrOpfamily Mdid="0.1977.1.0"/>
+ </dxl:DistrOpfamilies>
+ </dxl:Relation>
+ <dxl:Type Mdid="0.29.1.0" Name="cid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="false" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true">
+ <dxl:DistrOpfamily Mdid="0.2226.1.0"/>
+ <dxl:EqualityOp Mdid="0.385.1.0"/>
+ <dxl:InequalityOp Mdid="0.0.0.0"/>
+ <dxl:LessThanOp Mdid="0.0.0.0"/>
+ <dxl:LessThanEqualsOp Mdid="0.0.0.0"/>
+ <dxl:GreaterThanOp Mdid="0.0.0.0"/>
+ <dxl:GreaterThanEqualsOp Mdid="0.0.0.0"/>
+ <dxl:ComparisonOp Mdid="0.0.0.0"/>
+ <dxl:ArrayType Mdid="0.1012.1.0"/>
+ <dxl:MinAgg Mdid="0.0.0.0"/>
+ <dxl:MaxAgg Mdid="0.0.0.0"/>
+ <dxl:AvgAgg Mdid="0.0.0.0"/>
+ <dxl:SumAgg Mdid="0.0.0.0"/>
+ <dxl:CountAgg Mdid="0.2147.1.0"/>
+ </dxl:Type>
+ <dxl:Type Mdid="0.28.1.0" Name="xid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="false" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true">
+ <dxl:DistrOpfamily Mdid="0.2225.1.0"/>
+ <dxl:EqualityOp Mdid="0.352.1.0"/>
+ <dxl:InequalityOp Mdid="0.3315.1.0"/>
+ <dxl:LessThanOp Mdid="0.0.0.0"/>
+ <dxl:LessThanEqualsOp Mdid="0.0.0.0"/>
+ <dxl:GreaterThanOp Mdid="0.0.0.0"/>
+ <dxl:GreaterThanEqualsOp Mdid="0.0.0.0"/>
+ <dxl:ComparisonOp Mdid="0.0.0.0"/>
+ <dxl:ArrayType Mdid="0.1011.1.0"/>
+ <dxl:MinAgg Mdid="0.0.0.0"/>
+ <dxl:MaxAgg Mdid="0.0.0.0"/>
+ <dxl:AvgAgg Mdid="0.0.0.0"/>
+ <dxl:SumAgg Mdid="0.0.0.0"/>
+ <dxl:CountAgg Mdid="0.2147.1.0"/>
+ </dxl:Type>
+ <dxl:ColumnStatistics Mdid="1.41006.1.0.0" Name="c1" Width="4.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="false">
+ <dxl:StatsBucket Frequency="0.199998" DistinctValues="1.000000">
+ <dxl:LowerBound Closed="true" TypeMdid="0.23.1.0" Value="0"/>
+ <dxl:UpperBound Closed="true" TypeMdid="0.23.1.0" Value="0"/>
+ </dxl:StatsBucket>
+ <dxl:StatsBucket Frequency="0.199998" DistinctValues="1.000000">
+ <dxl:LowerBound Closed="true" TypeMdid="0.23.1.0" Value="1"/>
+ <dxl:UpperBound Closed="true" TypeMdid="0.23.1.0" Value="1"/>
+ </dxl:StatsBucket>
+ <dxl:StatsBucket Frequency="0.199998" DistinctValues="1.000000">
+ <dxl:LowerBound Closed="true" TypeMdid="0.23.1.0" Value="2"/>
+ <dxl:UpperBound Closed="true" TypeMdid="0.23.1.0" Value="2"/>
+ </dxl:StatsBucket>
+ <dxl:StatsBucket Frequency="0.199998" DistinctValues="1.000000">
+ <dxl:LowerBound Closed="true" TypeMdid="0.23.1.0" Value="3"/>
+ <dxl:UpperBound Closed="true" TypeMdid="0.23.1.0" Value="3"/>
+ </dxl:StatsBucket>
+ <dxl:StatsBucket Frequency="0.199998" DistinctValues="1.000000">
+ <dxl:LowerBound Closed="true" TypeMdid="0.23.1.0" Value="4"/>
+ <dxl:UpperBound Closed="true" TypeMdid="0.23.1.0" Value="4"/>
+ </dxl:StatsBucket>
+ </dxl:ColumnStatistics>
+ <dxl:GPDBScalarOp Mdid="0.96.1.0" Name="=" ComparisonType="Eq" ReturnsNullOnNullInput="true" IsNDVPreserving="false">
+ <dxl:LeftType Mdid="0.23.1.0"/>
+ <dxl:RightType Mdid="0.23.1.0"/>
+ <dxl:ResultType Mdid="0.16.1.0"/>
+ <dxl:OpFunc Mdid="0.65.1.0"/>
+ <dxl:Commutator Mdid="0.96.1.0"/>
+ <dxl:InverseOp Mdid="0.518.1.0"/>
+ <dxl:HashOpfamily Mdid="0.1977.1.0"/>
+ <dxl:LegacyHashOpfamily Mdid="0.7100.1.0"/>
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ <dxl:Opfamily Mdid="0.1977.1.0"/>
+ <dxl:Opfamily Mdid="0.4054.1.0"/>
+ <dxl:Opfamily Mdid="0.7100.1.0"/>
+ <dxl:Opfamily Mdid="0.10009.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:GPDBScalarOp>
+ <dxl:RelationExtendedStatistics Mdid="10.41006.1.0" Name="t1"/>
+ </dxl:Metadata>
+ <dxl:Query>
+ <dxl:OutputColumns>
+ <dxl:Ident ColId="1" ColName="c1" TypeMdid="0.23.1.0"/>
+ </dxl:OutputColumns>
+ <dxl:CTEList/>
+ <dxl:LogicalSelect>
+ <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.96.1.0">
+ <dxl:Ident ColId="1" ColName="c1" TypeMdid="0.23.1.0"/>
+ <dxl:ConstValue TypeMdid="0.23.1.0" Value="4"/>
+ </dxl:Comparison>
+ <dxl:LogicalGet>
+ <dxl:TableDescriptor Mdid="6.41006.1.0" TableName="t1" LockMode="1">
+ <dxl:Columns>
+ <dxl:Column ColId="1" Attno="1" ColName="c1" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="2" Attno="2" ColName="c2" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="3" Attno="3" ColName="c3" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="4" Attno="4" ColName="c4" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="5" Attno="5" ColName="c5" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="6" Attno="6" ColName="c6" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="7" Attno="7" ColName="c7" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="8" Attno="8" ColName="c8" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="9" Attno="9" ColName="c9" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="10" Attno="10" ColName="c10" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="11" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/>
+ <dxl:Column ColId="12" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/>
+ <dxl:Column ColId="13" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/>
+ <dxl:Column ColId="14" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/>
+ <dxl:Column ColId="15" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/>
+ <dxl:Column ColId="16" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/>
+ <dxl:Column ColId="17" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/>
+ </dxl:Columns>
+ </dxl:TableDescriptor>
+ </dxl:LogicalGet>
+ </dxl:LogicalSelect>
+ </dxl:Query>
+ <dxl:Plan Id="0" SpaceSize="8">
+ <dxl:GatherMotion InputSegments="0,1,2" OutputSegments="-1">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="6.144772" Rows="1999.980000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="c1">
+ <dxl:Ident ColId="0" ColName="c1" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:SortingColumnList/>
+ <dxl:IndexOnlyScan IndexScanDirection="Forward">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="6.109999" Rows="1999.980000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="c1">
+ <dxl:Ident ColId="0" ColName="c1" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:IndexCondList>
+ <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.96.1.0">
+ <dxl:Ident ColId="0" ColName="c1" TypeMdid="0.23.1.0"/>
+ <dxl:ConstValue TypeMdid="0.23.1.0" Value="4"/>
+ </dxl:Comparison>
+ </dxl:IndexCondList>
+ <dxl:Partitions/>
+ <dxl:IndexDescriptor Mdid="7.41014.1.0" IndexName="idx_0_include_cols"/>
+ <dxl:TableDescriptor Mdid="6.41006.1.0" TableName="t1" LockMode="1">
+ <dxl:Columns>
+ <dxl:Column ColId="0" Attno="1" ColName="c1" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="10" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/>
+ <dxl:Column ColId="11" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/>
+ <dxl:Column ColId="12" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/>
+ <dxl:Column ColId="13" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/>
+ <dxl:Column ColId="14" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/>
+ <dxl:Column ColId="15" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/>
+ <dxl:Column ColId="16" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/>
+ </dxl:Columns>
+ </dxl:TableDescriptor>
+ </dxl:IndexOnlyScan>
+ </dxl:GatherMotion>
+ <dxl:DirectDispatchInfo>
+ <dxl:KeyValue>
+ <dxl:Datum TypeMdid="0.23.1.0" Value="4"/>
+ </dxl:KeyValue>
+ </dxl:DirectDispatchInfo>
+ </dxl:Plan>
+ </dxl:Thread>
+</dxl:DXLMessage>
diff --git a/src/backend/gporca/data/dxl/minidump/CoveringIndex-Cost-2.mdp b/src/backend/gporca/data/dxl/minidump/CoveringIndex-Cost-2.mdp
new file mode 100644
index 0000000000..f05a9c33a1
--- /dev/null
+++ b/src/backend/gporca/data/dxl/minidump/CoveringIndex-Cost-2.mdp
@@ -0,0 +1,394 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<dxl:DXLMessage xmlns:dxl="http://greenplum.com/dxl/2010/12/">
+ <dxl:Comment><![CDATA[
+ Objective: Given multple indexes that can all satisfy a query prediate and
+ cover an index scan, use the index with the smallest include columns.
+
+ CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int);
+ INSERT INTO t1 SELECT i%5, i%5, i%5, i%5, i%5, i%5, i%5, i%5, i%5, i%5 FROM generate_series(1, 10000)i;
+ VACUUM ANALYZE t1;
+
+ CREATE INDEX idx_9_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3, c4, c5, c6, c7, c8, c9, c10);
+ CREATE INDEX idx_8_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3, c4, c5, c6, c7, c8, c9);
+ CREATE INDEX idx_7_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3, c4, c5, c6, c7, c8);
+ CREATE INDEX idx_6_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3, c4, c5, c6, c7);
+ CREATE INDEX idx_5_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3, c4, c5, c6);
+ CREATE INDEX idx_0_include_cols ON t1 USING btree(c1);
+ CREATE INDEX idx_4_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3, c4, c5);
+ CREATE INDEX idx_3_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3, c4);
+ CREATE INDEX idx_2_include_cols ON t1 USING btree(c1) INCLUDE (c2, c3);
+ CREATE INDEX idx_1_include_cols ON t1 USING btree(c1) INCLUDE (c2);
+
+ SET optimizer_enable_indexonlyscan=off;
+ EXPLAIN ANALYZE SELECT c1 FROM t1 WHERE c1= 4;
+ ]]>
+ </dxl:Comment>
+ <dxl:Thread Id="0">
+ <dxl:OptimizerConfig>
+ <dxl:EnumeratorConfig Id="0" PlanSamples="0" CostThreshold="0"/>
+ <dxl:StatisticsConfig DampingFactorFilter="0.750000" DampingFactorJoin="0.000000" DampingFactorGroupBy="0.750000" MaxStatsBuckets="100"/>
+ <dxl:CTEConfig CTEInliningCutoff="0"/>
+ <dxl:WindowOids RowNumber="3100" Rank="3101"/>
+ <dxl:CostModelConfig CostModelType="1" SegmentsForCosting="3">
+ <dxl:CostParams>
+ <dxl:CostParam Name="NLJFactor" Value="1024.000000" LowerBound="1023.500000" UpperBound="1024.500000"/>
+ </dxl:CostParams>
+ </dxl:CostModelConfig>
+ <dxl:Hint JoinArityForAssociativityCommutativity="18" ArrayExpansionThreshold="20" JoinOrderDynamicProgThreshold="10" BroadcastThreshold="100000" EnforceConstraintsOnDML="false" PushGroupByBelowSetopThreshold="10" XformBindThreshold="0" SkewFactor="0"/>
+ <dxl:TraceFlags Value="102001,102002,102003,102043,102074,102120,102144,102152,103001,103014,103015,103022,103026,103027,103029,103033,103038,103040,104002,104003,104004,104005,105000,106000"/>
+ </dxl:OptimizerConfig>
+ <dxl:Metadata SystemIds="0.GPDB">
+ <dxl:Type Mdid="0.16.1.0" Name="bool" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="1" PassByValue="true">
+ <dxl:DistrOpfamily Mdid="0.2222.1.0"/>
+ <dxl:LegacyDistrOpfamily Mdid="0.7124.1.0"/>
+ <dxl:PartOpfamily Mdid="0.424.1.0"/>
+ <dxl:EqualityOp Mdid="0.91.1.0"/>
+ <dxl:InequalityOp Mdid="0.85.1.0"/>
+ <dxl:LessThanOp Mdid="0.58.1.0"/>
+ <dxl:LessThanEqualsOp Mdid="0.1694.1.0"/>
+ <dxl:GreaterThanOp Mdid="0.59.1.0"/>
+ <dxl:GreaterThanEqualsOp Mdid="0.1695.1.0"/>
+ <dxl:ComparisonOp Mdid="0.1693.1.0"/>
+ <dxl:ArrayType Mdid="0.1000.1.0"/>
+ <dxl:MinAgg Mdid="0.0.0.0"/>
+ <dxl:MaxAgg Mdid="0.0.0.0"/>
+ <dxl:AvgAgg Mdid="0.0.0.0"/>
+ <dxl:SumAgg Mdid="0.0.0.0"/>
+ <dxl:CountAgg Mdid="0.2147.1.0"/>
+ </dxl:Type>
+ <dxl:Type Mdid="0.23.1.0" Name="int4" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true">
+ <dxl:DistrOpfamily Mdid="0.1977.1.0"/>
+ <dxl:LegacyDistrOpfamily Mdid="0.7100.1.0"/>
+ <dxl:PartOpfamily Mdid="0.1976.1.0"/>
+ <dxl:EqualityOp Mdid="0.96.1.0"/>
+ <dxl:InequalityOp Mdid="0.518.1.0"/>
+ <dxl:LessThanOp Mdid="0.97.1.0"/>
+ <dxl:LessThanEqualsOp Mdid="0.523.1.0"/>
+ <dxl:GreaterThanOp Mdid="0.521.1.0"/>
+ <dxl:GreaterThanEqualsOp Mdid="0.525.1.0"/>
+ <dxl:ComparisonOp Mdid="0.351.1.0"/>
+ <dxl:ArrayType Mdid="0.1007.1.0"/>
+ <dxl:MinAgg Mdid="0.2132.1.0"/>
+ <dxl:MaxAgg Mdid="0.2116.1.0"/>
+ <dxl:AvgAgg Mdid="0.2101.1.0"/>
+ <dxl:SumAgg Mdid="0.2108.1.0"/>
+ <dxl:CountAgg Mdid="0.2147.1.0"/>
+ </dxl:Type>
+ <dxl:RelationExtendedStatistics Mdid="10.41037.1.0" Name="t1"/>
+ <dxl:Type Mdid="0.26.1.0" Name="oid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true">
+ <dxl:DistrOpfamily Mdid="0.1990.1.0"/>
+ <dxl:LegacyDistrOpfamily Mdid="0.7109.1.0"/>
+ <dxl:PartOpfamily Mdid="0.1989.1.0"/>
+ <dxl:EqualityOp Mdid="0.607.1.0"/>
+ <dxl:InequalityOp Mdid="0.608.1.0"/>
+ <dxl:LessThanOp Mdid="0.609.1.0"/>
+ <dxl:LessThanEqualsOp Mdid="0.611.1.0"/>
+ <dxl:GreaterThanOp Mdid="0.610.1.0"/>
+ <dxl:GreaterThanEqualsOp Mdid="0.612.1.0"/>
+ <dxl:ComparisonOp Mdid="0.356.1.0"/>
+ <dxl:ArrayType Mdid="0.1028.1.0"/>
+ <dxl:MinAgg Mdid="0.2134.1.0"/>
+ <dxl:MaxAgg Mdid="0.2118.1.0"/>
+ <dxl:AvgAgg Mdid="0.0.0.0"/>
+ <dxl:SumAgg Mdid="0.0.0.0"/>
+ <dxl:CountAgg Mdid="0.2147.1.0"/>
+ </dxl:Type>
+ <dxl:Type Mdid="0.27.1.0" Name="tid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="true" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="6" PassByValue="false">
+ <dxl:DistrOpfamily Mdid="0.2227.1.0"/>
+ <dxl:LegacyDistrOpfamily Mdid="0.7110.1.0"/>
+ <dxl:PartOpfamily Mdid="0.2789.1.0"/>
+ <dxl:EqualityOp Mdid="0.387.1.0"/>
+ <dxl:InequalityOp Mdid="0.402.1.0"/>
+ <dxl:LessThanOp Mdid="0.2799.1.0"/>
+ <dxl:LessThanEqualsOp Mdid="0.2801.1.0"/>
+ <dxl:GreaterThanOp Mdid="0.2800.1.0"/>
+ <dxl:GreaterThanEqualsOp Mdid="0.2802.1.0"/>
+ <dxl:ComparisonOp Mdid="0.2794.1.0"/>
+ <dxl:ArrayType Mdid="0.1010.1.0"/>
+ <dxl:MinAgg Mdid="0.2798.1.0"/>
+ <dxl:MaxAgg Mdid="0.2797.1.0"/>
+ <dxl:AvgAgg Mdid="0.0.0.0"/>
+ <dxl:SumAgg Mdid="0.0.0.0"/>
+ <dxl:CountAgg Mdid="0.2147.1.0"/>
+ </dxl:Type>
+ <dxl:Type Mdid="0.29.1.0" Name="cid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="false" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true">
+ <dxl:DistrOpfamily Mdid="0.2226.1.0"/>
+ <dxl:EqualityOp Mdid="0.385.1.0"/>
+ <dxl:InequalityOp Mdid="0.0.0.0"/>
+ <dxl:LessThanOp Mdid="0.0.0.0"/>
+ <dxl:LessThanEqualsOp Mdid="0.0.0.0"/>
+ <dxl:GreaterThanOp Mdid="0.0.0.0"/>
+ <dxl:GreaterThanEqualsOp Mdid="0.0.0.0"/>
+ <dxl:ComparisonOp Mdid="0.0.0.0"/>
+ <dxl:ArrayType Mdid="0.1012.1.0"/>
+ <dxl:MinAgg Mdid="0.0.0.0"/>
+ <dxl:MaxAgg Mdid="0.0.0.0"/>
+ <dxl:AvgAgg Mdid="0.0.0.0"/>
+ <dxl:SumAgg Mdid="0.0.0.0"/>
+ <dxl:CountAgg Mdid="0.2147.1.0"/>
+ </dxl:Type>
+ <dxl:Type Mdid="0.28.1.0" Name="xid" IsRedistributable="true" IsHashable="true" IsMergeJoinable="false" IsComposite="false" IsTextRelated="false" IsFixedLength="true" Length="4" PassByValue="true">
+ <dxl:DistrOpfamily Mdid="0.2225.1.0"/>
+ <dxl:EqualityOp Mdid="0.352.1.0"/>
+ <dxl:InequalityOp Mdid="0.3315.1.0"/>
+ <dxl:LessThanOp Mdid="0.0.0.0"/>
+ <dxl:LessThanEqualsOp Mdid="0.0.0.0"/>
+ <dxl:GreaterThanOp Mdid="0.0.0.0"/>
+ <dxl:GreaterThanEqualsOp Mdid="0.0.0.0"/>
+ <dxl:ComparisonOp Mdid="0.0.0.0"/>
+ <dxl:ArrayType Mdid="0.1011.1.0"/>
+ <dxl:MinAgg Mdid="0.0.0.0"/>
+ <dxl:MaxAgg Mdid="0.0.0.0"/>
+ <dxl:AvgAgg Mdid="0.0.0.0"/>
+ <dxl:SumAgg Mdid="0.0.0.0"/>
+ <dxl:CountAgg Mdid="0.2147.1.0"/>
+ </dxl:Type>
+ <dxl:ColumnStatistics Mdid="1.41037.1.0.0" Name="c1" Width="4.000000" NullFreq="0.000000" NdvRemain="0.000000" FreqRemain="0.000000" ColStatsMissing="false">
+ <dxl:StatsBucket Frequency="0.199998" DistinctValues="1.000000">
+ <dxl:LowerBound Closed="true" TypeMdid="0.23.1.0" Value="0"/>
+ <dxl:UpperBound Closed="true" TypeMdid="0.23.1.0" Value="0"/>
+ </dxl:StatsBucket>
+ <dxl:StatsBucket Frequency="0.199998" DistinctValues="1.000000">
+ <dxl:LowerBound Closed="true" TypeMdid="0.23.1.0" Value="1"/>
+ <dxl:UpperBound Closed="true" TypeMdid="0.23.1.0" Value="1"/>
+ </dxl:StatsBucket>
+ <dxl:StatsBucket Frequency="0.199998" DistinctValues="1.000000">
+ <dxl:LowerBound Closed="true" TypeMdid="0.23.1.0" Value="2"/>
+ <dxl:UpperBound Closed="true" TypeMdid="0.23.1.0" Value="2"/>
+ </dxl:StatsBucket>
+ <dxl:StatsBucket Frequency="0.199998" DistinctValues="1.000000">
+ <dxl:LowerBound Closed="true" TypeMdid="0.23.1.0" Value="3"/>
+ <dxl:UpperBound Closed="true" TypeMdid="0.23.1.0" Value="3"/>
+ </dxl:StatsBucket>
+ <dxl:StatsBucket Frequency="0.199998" DistinctValues="1.000000">
+ <dxl:LowerBound Closed="true" TypeMdid="0.23.1.0" Value="4"/>
+ <dxl:UpperBound Closed="true" TypeMdid="0.23.1.0" Value="4"/>
+ </dxl:StatsBucket>
+ </dxl:ColumnStatistics>
+ <dxl:GPDBScalarOp Mdid="0.96.1.0" Name="=" ComparisonType="Eq" ReturnsNullOnNullInput="true" IsNDVPreserving="false">
+ <dxl:LeftType Mdid="0.23.1.0"/>
+ <dxl:RightType Mdid="0.23.1.0"/>
+ <dxl:ResultType Mdid="0.16.1.0"/>
+ <dxl:OpFunc Mdid="0.65.1.0"/>
+ <dxl:Commutator Mdid="0.96.1.0"/>
+ <dxl:InverseOp Mdid="0.518.1.0"/>
+ <dxl:HashOpfamily Mdid="0.1977.1.0"/>
+ <dxl:LegacyHashOpfamily Mdid="0.7100.1.0"/>
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ <dxl:Opfamily Mdid="0.1977.1.0"/>
+ <dxl:Opfamily Mdid="0.4054.1.0"/>
+ <dxl:Opfamily Mdid="0.7100.1.0"/>
+ <dxl:Opfamily Mdid="0.10009.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:GPDBScalarOp>
+ <dxl:Index Mdid="7.41049.1.0" Name="idx_1_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41048.1.0" Name="idx_2_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41045.1.0" Name="idx_0_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41044.1.0" Name="idx_5_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2,3,4,5">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41047.1.0" Name="idx_3_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2,3">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41046.1.0" Name="idx_4_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2,3,4">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41041.1.0" Name="idx_8_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2,3,4,5,6,7,8">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41040.1.0" Name="idx_9_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2,3,4,5,6,7,8,9">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41043.1.0" Name="idx_6_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2,3,4,5,6">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:Index Mdid="7.41042.1.0" Name="idx_7_include_cols" IsClustered="false" IndexType="B-tree" IndexItemType="0.2283.1.0" KeyColumns="0" IncludedColumns="1,2,3,4,5,6,7">
+ <dxl:Opfamilies>
+ <dxl:Opfamily Mdid="0.1976.1.0"/>
+ </dxl:Opfamilies>
+ </dxl:Index>
+ <dxl:RelationStatistics Mdid="2.41037.1.0" Name="t1" Rows="10000.000000" RelPages="22" RelAllVisible="22" EmptyRelation="false"/>
+ <dxl:Relation Mdid="6.41037.1.0" Name="t1" IsTemporary="false" StorageType="Heap" DistributionPolicy="Hash" DistributionColumns="0" Keys="16,10">
+ <dxl:Columns>
+ <dxl:Column Name="c1" Attno="1" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c2" Attno="2" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c3" Attno="3" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c4" Attno="4" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c5" Attno="5" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c6" Attno="6" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c7" Attno="7" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c8" Attno="8" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c9" Attno="9" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="c10" Attno="10" Mdid="0.23.1.0" Nullable="true" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="ctid" Attno="-1" Mdid="0.27.1.0" Nullable="false" ColWidth="6">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="xmin" Attno="-2" Mdid="0.28.1.0" Nullable="false" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="cmin" Attno="-3" Mdid="0.29.1.0" Nullable="false" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="xmax" Attno="-4" Mdid="0.28.1.0" Nullable="false" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="cmax" Attno="-5" Mdid="0.29.1.0" Nullable="false" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="tableoid" Attno="-6" Mdid="0.26.1.0" Nullable="false" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ <dxl:Column Name="gp_segment_id" Attno="-7" Mdid="0.23.1.0" Nullable="false" ColWidth="4">
+ <dxl:DefaultValue/>
+ </dxl:Column>
+ </dxl:Columns>
+ <dxl:IndexInfoList>
+ <dxl:IndexInfo Mdid="7.41040.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41041.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41042.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41043.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41044.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41045.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41046.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41047.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41048.1.0" IsPartial="false"/>
+ <dxl:IndexInfo Mdid="7.41049.1.0" IsPartial="false"/>
+ </dxl:IndexInfoList>
+ <dxl:CheckConstraints/>
+ <dxl:DistrOpfamilies>
+ <dxl:DistrOpfamily Mdid="0.1977.1.0"/>
+ </dxl:DistrOpfamilies>
+ </dxl:Relation>
+ </dxl:Metadata>
+ <dxl:Query>
+ <dxl:OutputColumns>
+ <dxl:Ident ColId="1" ColName="c1" TypeMdid="0.23.1.0"/>
+ </dxl:OutputColumns>
+ <dxl:CTEList/>
+ <dxl:LogicalSelect>
+ <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.96.1.0">
+ <dxl:Ident ColId="1" ColName="c1" TypeMdid="0.23.1.0"/>
+ <dxl:ConstValue TypeMdid="0.23.1.0" Value="4"/>
+ </dxl:Comparison>
+ <dxl:LogicalGet>
+ <dxl:TableDescriptor Mdid="6.41037.1.0" TableName="t1" LockMode="1">
+ <dxl:Columns>
+ <dxl:Column ColId="1" Attno="1" ColName="c1" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="2" Attno="2" ColName="c2" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="3" Attno="3" ColName="c3" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="4" Attno="4" ColName="c4" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="5" Attno="5" ColName="c5" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="6" Attno="6" ColName="c6" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="7" Attno="7" ColName="c7" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="8" Attno="8" ColName="c8" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="9" Attno="9" ColName="c9" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="10" Attno="10" ColName="c10" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="11" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/>
+ <dxl:Column ColId="12" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/>
+ <dxl:Column ColId="13" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/>
+ <dxl:Column ColId="14" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/>
+ <dxl:Column ColId="15" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/>
+ <dxl:Column ColId="16" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/>
+ <dxl:Column ColId="17" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/>
+ </dxl:Columns>
+ </dxl:TableDescriptor>
+ </dxl:LogicalGet>
+ </dxl:LogicalSelect>
+ </dxl:Query>
+ <dxl:Plan Id="0" SpaceSize="7">
+ <dxl:GatherMotion InputSegments="0,1,2" OutputSegments="-1">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="6.315570" Rows="1999.980000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="c1">
+ <dxl:Ident ColId="0" ColName="c1" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:SortingColumnList/>
+ <dxl:IndexScan IndexScanDirection="Forward">
+ <dxl:Properties>
+ <dxl:Cost StartupCost="0" TotalCost="6.280797" Rows="1999.980000" Width="4"/>
+ </dxl:Properties>
+ <dxl:ProjList>
+ <dxl:ProjElem ColId="0" Alias="c1">
+ <dxl:Ident ColId="0" ColName="c1" TypeMdid="0.23.1.0"/>
+ </dxl:ProjElem>
+ </dxl:ProjList>
+ <dxl:Filter/>
+ <dxl:IndexCondList>
+ <dxl:Comparison ComparisonOperator="=" OperatorMdid="0.96.1.0">
+ <dxl:Ident ColId="0" ColName="c1" TypeMdid="0.23.1.0"/>
+ <dxl:ConstValue TypeMdid="0.23.1.0" Value="4"/>
+ </dxl:Comparison>
+ </dxl:IndexCondList>
+ <dxl:Partitions/>
+ <dxl:IndexDescriptor Mdid="7.41045.1.0" IndexName="idx_0_include_cols"/>
+ <dxl:TableDescriptor Mdid="6.41037.1.0" TableName="t1" LockMode="1">
+ <dxl:Columns>
+ <dxl:Column ColId="0" Attno="1" ColName="c1" TypeMdid="0.23.1.0" ColWidth="4"/>
+ <dxl:Column ColId="10" Attno="-1" ColName="ctid" TypeMdid="0.27.1.0" ColWidth="6"/>
+ <dxl:Column ColId="11" Attno="-2" ColName="xmin" TypeMdid="0.28.1.0" ColWidth="4"/>
+ <dxl:Column ColId="12" Attno="-3" ColName="cmin" TypeMdid="0.29.1.0" ColWidth="4"/>
+ <dxl:Column ColId="13" Attno="-4" ColName="xmax" TypeMdid="0.28.1.0" ColWidth="4"/>
+ <dxl:Column ColId="14" Attno="-5" ColName="cmax" TypeMdid="0.29.1.0" ColWidth="4"/>
+ <dxl:Column ColId="15" Attno="-6" ColName="tableoid" TypeMdid="0.26.1.0" ColWidth="4"/>
+ <dxl:Column ColId="16" Attno="-7" ColName="gp_segment_id" TypeMdid="0.23.1.0" ColWidth="4"/>
+ </dxl:Columns>
+ </dxl:TableDescriptor>
+ </dxl:IndexScan>
+ </dxl:GatherMotion>
+ <dxl:DirectDispatchInfo>
+ <dxl:KeyValue>
+ <dxl:Datum TypeMdid="0.23.1.0" Value="4"/>
+ </dxl:KeyValue>
+ </dxl:DirectDispatchInfo>
+ </dxl:Plan>
+ </dxl:Thread>
+</dxl:DXLMessage>
diff --git a/src/backend/gporca/data/dxl/minidump/IndexOnlyScan-CTE.mdp b/src/backend/gporca/data/dxl/minidump/IndexOnlyScan-CTE.mdp
index 31299a7dec..71f1fb8eb4 100644
--- a/src/backend/gporca/data/dxl/minidump/IndexOnlyScan-CTE.mdp
+++ b/src/backend/gporca/data/dxl/minidump/IndexOnlyScan-CTE.mdp
@@ -264,7 +264,7 @@
<dxl:Plan Id="0" SpaceSize="3">
<dxl:GatherMotion InputSegments="0,1,2" OutputSegments="-1">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="0.000119" Rows="1.000000" Width="4"/>
+ <dxl:Cost StartupCost="0" TotalCost="6.000072" Rows="1.000000" Width="4"/>
</dxl:Properties>
<dxl:ProjList>
<dxl:ProjElem ColId="9" Alias="a">
@@ -275,7 +275,7 @@
<dxl:SortingColumnList/>
<dxl:IndexOnlyScan IndexScanDirection="Forward">
<dxl:Properties>
- <dxl:Cost StartupCost="0" TotalCost="0.000101" Rows="1.000000" Width="4"/>
+ <dxl:Cost StartupCost="0" TotalCost="6.000055" Rows="1.000000" Width="4"/>
</dxl:Properties>
<dxl:ProjList>
<dxl:ProjElem ColId="9" Alias="a">
diff --git a/src/backend/gporca/libgpdbcost/include/gpdbcost/CCostModelParamsGPDB.h b/src/backend/gporca/libgpdbcost/include/gpdbcost/CCostModelParamsGPDB.h
index 06d5214086..ed17a8a0e6 100644
--- a/src/backend/gporca/libgpdbcost/include/gpdbcost/CCostModelParamsGPDB.h
+++ b/src/backend/gporca/libgpdbcost/include/gpdbcost/CCostModelParamsGPDB.h
@@ -104,7 +104,8 @@ public:
EcpBitmapScanRebindCost, // cost of rebind operation in a bitmap scan
EcpPenalizeHJSkewUpperLimit, // upper limit for penalizing a skewed hashjoin operator
- EcpScalarFuncCost, // cost of scalar func
+ EcpScalarFuncCost, // cost of scalar func
+ EcpIndexOnlyScanTupCostUnit, // index only scan cost per tuple retrieving
EcpSentinel
};
@@ -147,6 +148,9 @@ private:
// default value of index scan cost unit per tuple per unit width
static const CDouble DIndexScanTupCostUnitVal;
+ // default value of index only scan cost unit per tuple per unit width
+ static const CDouble DIndexOnlyScanTupCostUnitVal;
+
// default value of index scan random IO cost unit per tuple
static const CDouble DIndexScanTupRandomFactorVal;
diff --git a/src/backend/gporca/libgpdbcost/src/CCostModelGPDB.cpp b/src/backend/gporca/libgpdbcost/src/CCostModelGPDB.cpp
index 32fd5fc989..3715949818 100644
--- a/src/backend/gporca/libgpdbcost/src/CCostModelGPDB.cpp
+++ b/src/backend/gporca/libgpdbcost/src/CCostModelGPDB.cpp
@@ -1578,6 +1578,10 @@ CCostModelGPDB::CostIndexScan(CMemoryPool *, // mp
pcmgpdb->GetCostModelParams()
->PcpLookup(CCostModelParamsGPDB::EcpIndexScanTupCostUnit)
->Get();
+ const CDouble dIndexOnlyScanTupCostUnit =
+ pcmgpdb->GetCostModelParams()
+ ->PcpLookup(CCostModelParamsGPDB::EcpIndexOnlyScanTupCostUnit)
+ ->Get();
const CDouble dIndexScanTupRandomFactor =
pcmgpdb->GetCostModelParams()
->PcpLookup(CCostModelParamsGPDB::EcpIndexScanTupRandomFactor)
@@ -1588,15 +1592,29 @@ CCostModelGPDB::CostIndexScan(CMemoryPool *, // mp
CDouble dRowsIndex = pci->Rows();
+ // Index's INCLUDE columns adds to the width of the index and thus adds I/O
+ // cost per index row. Account for that cost in dCostPerIndexRow.
+ CColumnDescriptorArray *indexIncludedArray = nullptr;
ULONG ulIndexKeys = 1;
if (COperator::EopPhysicalIndexScan == op_id)
{
ulIndexKeys = CPhysicalIndexScan::PopConvert(pop)->Pindexdesc()->Keys();
+ indexIncludedArray = CPhysicalIndexScan::PopConvert(pop)
+ ->Pindexdesc()
+ ->PdrgpcoldescIncluded();
}
else
{
ulIndexKeys =
CPhysicalDynamicIndexScan::PopConvert(pop)->Pindexdesc()->Keys();
+ indexIncludedArray = CPhysicalDynamicIndexScan::PopConvert(pop)
+ ->Pindexdesc()
+ ->PdrgpcoldescIncluded();
+ }
+ ULONG ulIncludedColWidth = 0;
+ for (ULONG ul = 0; ul < indexIncludedArray->Size(); ul++)
+ {
+ ulIncludedColWidth += (*indexIncludedArray)[ul]->Width();
}
// TODO: 2014-02-01
@@ -1605,12 +1623,14 @@ CCostModelGPDB::CostIndexScan(CMemoryPool *, // mp
// index scan cost contains two parts: index-column lookup and output tuple cost.
// 1. index-column lookup: correlated with index lookup rows, the number of index columns used in lookup,
- // table width and a randomIOFactor.
+ // table width and a randomIOFactor. also accounts for included columns which adds to the payload of index leaf
+ // pages that leads to bigger a btree which subsequently leads to more random IO during index lookup.
// 2. output tuple cost: this is handled by the Filter on top of IndexScan, if no Filter exists, we add output cost
// when we sum-up children cost
CDouble dCostPerIndexRow = ulIndexKeys * dIndexFilterCostUnit +
- dTableWidth * dIndexScanTupCostUnit;
+ dTableWidth * dIndexScanTupCostUnit +
+ ulIncludedColWidth * dIndexOnlyScanTupCostUnit;
return CCost(pci->NumRebinds() *
(dRowsIndex * dCostPerIndexRow + dIndexScanTupRandomFactor));
}
@@ -1640,6 +1660,10 @@ CCostModelGPDB::CostIndexOnlyScan(CMemoryPool *mp GPOS_UNUSED, // mp
pcmgpdb->GetCostModelParams()
->PcpLookup(CCostModelParamsGPDB::EcpIndexScanTupCostUnit)
->Get();
+ const CDouble dIndexOnlyScanTupCostUnit =
+ pcmgpdb->GetCostModelParams()
+ ->PcpLookup(CCostModelParamsGPDB::EcpIndexOnlyScanTupCostUnit)
+ ->Get();
const CDouble dIndexScanTupRandomFactor =
pcmgpdb->GetCostModelParams()
->PcpLookup(CCostModelParamsGPDB::EcpIndexScanTupRandomFactor)
@@ -1655,22 +1679,46 @@ CCostModelGPDB::CostIndexOnlyScan(CMemoryPool *mp GPOS_UNUSED, // mp
IStatistics *stats =
CPhysicalIndexOnlyScan::PopConvert(pop)->PstatsBaseTable();
- // Calculating cost of index-only-scan is identical to index-scan with the
- // addition of dPartialVisFrac which indicates the percentage of pages not
- // currently marked as all-visible. Planner has similar logic inside
- // `cost_index()` to calculate pages fetched from index-only-scan.
+ // Index's INCLUDE columns adds to the width of the index and thus adds I/O
+ // cost per index row. Account for that cost in dCostPerIndexRow.
+ CColumnDescriptorArray *indexIncludedArray =
+ CPhysicalIndexOnlyScan::PopConvert(pop)
+ ->Pindexdesc()
+ ->PdrgpcoldescIncluded();
+ ULONG ulIncludedColWidth = 0;
+ for (ULONG ul = 0; ul < indexIncludedArray->Size(); ul++)
+ {
+ ulIncludedColWidth += (*indexIncludedArray)[ul]->Width();
+ }
+
+ // The cost of index-only-scan is similar to index-scan with the additional
+ // dimension of variable size I/O. More specifically, index-scan I/O is
+ // bound to the fixed width of the relation times the number of output
+ // rows. However, index-only-scan may be able to sometimes avoid the cost
+ // of the full width of the relation (when the page is all visible) and
+ // instead directly retrieve the row from a narrow index.
+ //
+ // The percent of rows that can avoid I/O on full table width is
+ // approximately equal to the precent of tuples in all-visible blocks
+ // compared to total blocks. It is approximate because there is no
+ // guarantee that blocks are equally filled with live tuples.
- CDouble dCostPerIndexRow = ulIndexKeys * dIndexFilterCostUnit +
- dTableWidth * dIndexScanTupCostUnit;
CDouble dPartialVisFrac(1);
if (stats->RelPages() != 0)
{
dPartialVisFrac =
1 - (CDouble(stats->RelAllVisible()) / CDouble(stats->RelPages()));
}
+
+ CDouble dCostPerIndexRow =
+ ulIndexKeys * dIndexFilterCostUnit +
+ // partial visibile read from table
+ dTableWidth * dIndexScanTupCostUnit * dPartialVisFrac +
+ // always read from index (partial and full visible)
+ ulIncludedColWidth * dIndexOnlyScanTupCostUnit;
+
return CCost(pci->NumRebinds() *
- (dRowsIndex * dCostPerIndexRow +
- dIndexScanTupRandomFactor * dPartialVisFrac));
+ (dRowsIndex * dCostPerIndexRow + dIndexScanTupRandomFactor));
}
CCost
diff --git a/src/backend/gporca/libgpdbcost/src/CCostModelParamsGPDB.cpp b/src/backend/gporca/libgpdbcost/src/CCostModelParamsGPDB.cpp
index 57bb325f50..274b2939ec 100644
--- a/src/backend/gporca/libgpdbcost/src/CCostModelParamsGPDB.cpp
+++ b/src/backend/gporca/libgpdbcost/src/CCostModelParamsGPDB.cpp
@@ -45,9 +45,14 @@ const CDouble CCostModelParamsGPDB::DIndexBlockCostUnitVal = 1.27e-06;
// index filtering cost unit
const CDouble CCostModelParamsGPDB::DIndexFilterCostUnitVal = 1.65e-04;
-// index scan cost unit per tuple per width
+// index scan cost unit per tuple per width. includes the cost to read from the
+// index and the heap.
const CDouble CCostModelParamsGPDB::DIndexScanTupCostUnitVal = 3.66e-06;
+// index only scan cost unit per tuple per width. includes only the cost to
+// read from the index, _not_ the heap.
+const CDouble CCostModelParamsGPDB::DIndexOnlyScanTupCostUnitVal = 3.66e-06;
+
// index scan random IO factor
const CDouble CCostModelParamsGPDB::DIndexScanTupRandomFactorVal = 6.0;
@@ -292,6 +297,9 @@ CCostModelParamsGPDB::CCostModelParamsGPDB(CMemoryPool *mp) : m_mp(mp)
m_rgpcp[EcpIndexScanTupCostUnit] = GPOS_NEW(mp) SCostParam(
EcpIndexScanTupCostUnit, DIndexScanTupCostUnitVal,
DIndexScanTupCostUnitVal - 1.0, DIndexScanTupCostUnitVal + 1.0);
+ m_rgpcp[EcpIndexOnlyScanTupCostUnit] = GPOS_NEW(mp) SCostParam(
+ EcpIndexOnlyScanTupCostUnit, DIndexOnlyScanTupCostUnitVal,
+ DIndexOnlyScanTupCostUnitVal - 1.0, DIndexOnlyScanTupCostUnitVal + 1.0);
m_rgpcp[EcpIndexScanTupRandomFactor] = GPOS_NEW(mp) SCostParam(
EcpIndexScanTupRandomFactor, DIndexScanTupRandomFactorVal,
DIndexScanTupRandomFactorVal - 1.0, DIndexScanTupRandomFactorVal + 1.0);
diff --git a/src/backend/gporca/server/CMakeLists.txt b/src/backend/gporca/server/CMakeLists.txt
index 46bf11db0a..abcaf3d970 100644
--- a/src/backend/gporca/server/CMakeLists.txt
+++ b/src/backend/gporca/server/CMakeLists.txt
@@ -388,7 +388,7 @@ OrderedAgg_array_fraction OrderedAgg_multiple_samecol_difforderespec OrderedAgg_
OrderedAgg_skewed_data;
CoverintIndexTest:
-CoveringIndex-1 CoveringIndex-2 CoveringIndex-3;
+CoveringIndex-1 CoveringIndex-2 CoveringIndex-3 CoveringIndex-Cost-1 CoveringIndex-Cost-2;
CForeignPartTest:
ForeignPartUniform PartForeignMixed PartForeignDifferentServer PartForeignDifferentExecLocation PartForeignMixedDPE PartForeignMixedSPE PartForeignUniformSPE ForeignScanExecLocAnySimpleScan ForeignScanExecLocAnyJoin ForeignPartOneTimeFilterDPE
diff --git a/src/test/regress/expected/gp_covering_index.out b/src/test/regress/expected/gp_covering_index.out
index 99d1971961..d07090bf6b 100644
--- a/src/test/regress/expected/gp_covering_index.out
+++ b/src/test/regress/expected/gp_covering_index.out
@@ -301,12 +301,13 @@ SELECT a, b, c FROM test_ao WHERE c>42;
CREATE TABLE test_select_best_cover(a int, b int, c int);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
-CREATE INDEX i_test_select_best_cover_a ON test_select_best_cover(a);
+CREATE INDEX i_test_select_best_cover_a_bc ON test_select_best_cover(a) INCLUDE (b, c);
CREATE INDEX i_test_select_best_cover_a_b ON test_select_best_cover(a) INCLUDE (b);
+CREATE INDEX i_test_select_best_cover_a ON test_select_best_cover(a);
CREATE INDEX i_test_select_best_cover_ab ON test_select_best_cover(a, b);
+CREATE INDEX i_test_select_best_cover_b_ac ON test_select_best_cover(b) INCLUDE (a, c);
+CREATE INDEX i_test_select_best_cover_b_a ON test_select_best_cover(b) INCLUDE (a);
CREATE INDEX i_test_select_best_cover_b ON test_select_best_cover(b);
-CREATE INDEX i_test_select_best_cover_b_a ON test_select_best_cover(a) INCLUDE (b);
-CREATE INDEX i_test_select_best_cover_a_bc ON test_select_best_cover(a) INCLUDE (b, c);
INSERT INTO test_select_best_cover SELECT i, i+i, i*i FROM generate_series(1, 100)i;
VACUUM ANALYZE test_select_best_cover;
-- KEYS: [a] INCLUDED: []
@@ -317,10 +318,10 @@ VACUUM ANALYZE test_select_best_cover;
-- KEYS: [a] INCLUDED: [b, c]
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT a FROM test_select_best_cover WHERE a>42;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=58 loops=1)
- -> Index Only Scan using i_test_select_best_cover_a_bc on test_select_best_cover (actual rows=25 loops=1)
+ -> Index Only Scan using i_test_select_best_cover_ab on test_select_best_cover (actual rows=25 loops=1)
Index Cond: (a > 42)
Heap Fetches: 0
Optimizer: Postgres query optimizer
@@ -352,10 +353,10 @@ SELECT b FROM test_select_best_cover WHERE b>42;
-- ORCA_FEATURE_NOT_SUPPORTED: use i_test_select_best_cover_ab
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT b FROM test_select_best_cover WHERE a>42 AND b>42;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=58 loops=1)
- -> Index Only Scan using i_test_select_best_cover_a_bc on test_select_best_cover (actual rows=25 loops=1)
+ -> Index Only Scan using i_test_select_best_cover_a_b on test_select_best_cover (actual rows=25 loops=1)
Index Cond: (a > 42)
Filter: (b > 42)
Heap Fetches: 0
diff --git a/src/test/regress/expected/gp_covering_index_optimizer.out b/src/test/regress/expected/gp_covering_index_optimizer.out
index afea41fd87..64ae84b08d 100644
--- a/src/test/regress/expected/gp_covering_index_optimizer.out
+++ b/src/test/regress/expected/gp_covering_index_optimizer.out
@@ -295,12 +295,13 @@ SELECT a, b, c FROM test_ao WHERE c>42;
CREATE TABLE test_select_best_cover(a int, b int, c int);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
-CREATE INDEX i_test_select_best_cover_a ON test_select_best_cover(a);
+CREATE INDEX i_test_select_best_cover_a_bc ON test_select_best_cover(a) INCLUDE (b, c);
CREATE INDEX i_test_select_best_cover_a_b ON test_select_best_cover(a) INCLUDE (b);
+CREATE INDEX i_test_select_best_cover_a ON test_select_best_cover(a);
CREATE INDEX i_test_select_best_cover_ab ON test_select_best_cover(a, b);
+CREATE INDEX i_test_select_best_cover_b_ac ON test_select_best_cover(b) INCLUDE (a, c);
+CREATE INDEX i_test_select_best_cover_b_a ON test_select_best_cover(b) INCLUDE (a);
CREATE INDEX i_test_select_best_cover_b ON test_select_best_cover(b);
-CREATE INDEX i_test_select_best_cover_b_a ON test_select_best_cover(a) INCLUDE (b);
-CREATE INDEX i_test_select_best_cover_a_bc ON test_select_best_cover(a) INCLUDE (b, c);
INSERT INTO test_select_best_cover SELECT i, i+i, i*i FROM generate_series(1, 100)i;
VACUUM ANALYZE test_select_best_cover;
-- KEYS: [a] INCLUDED: []
diff --git a/src/test/regress/expected/join_optimizer.out b/src/test/regress/expected/join_optimizer.out
index fa62644cda..18243adddb 100644
--- a/src/test/regress/expected/join_optimizer.out
+++ b/src/test/regress/expected/join_optimizer.out
@@ -2635,15 +2635,18 @@ select count(*) from tenk1 a, tenk1 b
Finalize Aggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial Aggregate
- -> Nested Loop
- Join Filter: true
- -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Hash Join
+ Hash Cond: (tenk1.hundred = tenk1_1.thousand)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: tenk1.hundred
-> Seq Scan on tenk1
- Filter: ((fivethous % 10) < 10)
- -> Index Only Scan using tenk1_hundred on tenk1 tenk1_1
- Index Cond: (hundred = tenk1.thousand)
+ -> Hash
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Hash Key: tenk1_1.thousand
+ -> Seq Scan on tenk1 tenk1_1
+ Filter: ((fivethous % 10) < 10)
Optimizer: Pivotal Optimizer (GPORCA)
-(11 rows)
+(14 rows)
select count(*) from tenk1 a, tenk1 b
where a.hundred = b.thousand and (b.fivethous % 10) < 10;
diff --git a/src/test/regress/expected/select_parallel_optimizer.out b/src/test/regress/expected/select_parallel_optimizer.out
new file mode 100644
index 0000000000..7f67011023
--- /dev/null
+++ b/src/test/regress/expected/select_parallel_optimizer.out
@@ -0,0 +1,1239 @@
+--
+-- PARALLEL
+--
+-- GPDB_96_MERGE_FIXME: We don't support parallel query. These tests won't actually
+-- generate any parallel plans. Should we pay attention to the parallel restrictions
+-- when creating MPP plans? For example, should we force parallel restricted functions
+-- to run in the QD?
+create function sp_parallel_restricted(int) returns int as
+ $$begin return $1; end$$ language plpgsql parallel restricted;
+-- Serializable isolation would disable parallel query, so explicitly use an
+-- arbitrary other level.
+begin isolation level repeatable read;
+-- encourage use of parallel plans
+set parallel_setup_cost=0;
+set parallel_tuple_cost=0;
+set min_parallel_table_scan_size=0;
+set max_parallel_workers_per_gather=4;
+-- Parallel Append with partial-subplans
+explain (costs off)
+ select round(avg(aa)), sum(aa) from a_star;
+ QUERY PLAN
+------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Append
+ -> Seq Scan on a_star
+ -> Seq Scan on b_star
+ -> Seq Scan on c_star
+ -> Seq Scan on d_star
+ -> Seq Scan on e_star
+ -> Seq Scan on f_star
+ Optimizer: Postgres query optimizer
+(11 rows)
+
+select round(avg(aa)), sum(aa) from a_star a1;
+ round | sum
+-------+-----
+ 14 | 355
+(1 row)
+
+-- Parallel Append with both partial and non-partial subplans
+alter table c_star set (parallel_workers = 0);
+alter table d_star set (parallel_workers = 0);
+explain (costs off)
+ select round(avg(aa)), sum(aa) from a_star;
+ QUERY PLAN
+------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Append
+ -> Seq Scan on a_star
+ -> Seq Scan on b_star
+ -> Seq Scan on c_star
+ -> Seq Scan on d_star
+ -> Seq Scan on e_star
+ -> Seq Scan on f_star
+ Optimizer: Postgres query optimizer
+(11 rows)
+
+select round(avg(aa)), sum(aa) from a_star a2;
+ round | sum
+-------+-----
+ 14 | 355
+(1 row)
+
+-- Parallel Append with only non-partial subplans
+alter table a_star set (parallel_workers = 0);
+alter table b_star set (parallel_workers = 0);
+alter table e_star set (parallel_workers = 0);
+alter table f_star set (parallel_workers = 0);
+explain (costs off)
+ select round(avg(aa)), sum(aa) from a_star;
+ QUERY PLAN
+------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Append
+ -> Seq Scan on a_star
+ -> Seq Scan on b_star
+ -> Seq Scan on c_star
+ -> Seq Scan on d_star
+ -> Seq Scan on e_star
+ -> Seq Scan on f_star
+ Optimizer: Postgres query optimizer
+(11 rows)
+
+select round(avg(aa)), sum(aa) from a_star a3;
+ round | sum
+-------+-----
+ 14 | 355
+(1 row)
+
+-- Disable Parallel Append
+alter table a_star reset (parallel_workers);
+alter table b_star reset (parallel_workers);
+alter table c_star reset (parallel_workers);
+alter table d_star reset (parallel_workers);
+alter table e_star reset (parallel_workers);
+alter table f_star reset (parallel_workers);
+set enable_parallel_append to off;
+explain (costs off)
+ select round(avg(aa)), sum(aa) from a_star;
+ QUERY PLAN
+-----------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Append
+ -> Seq Scan on a_star
+ -> Seq Scan on b_star
+ -> Seq Scan on c_star
+ -> Seq Scan on d_star
+ -> Seq Scan on e_star
+ -> Seq Scan on f_star
+ Optimizer: Postgres query optimizer
+(11 rows)
+
+select round(avg(aa)), sum(aa) from a_star a4;
+ round | sum
+-------+-----
+ 14 | 355
+(1 row)
+
+reset enable_parallel_append;
+-- Parallel Append that runs serially
+create function sp_test_func() returns setof text as
+$$ select 'foo'::varchar union all select 'bar'::varchar $$
+language sql stable;
+select sp_test_func() order by 1;
+ sp_test_func
+--------------
+ bar
+ foo
+(2 rows)
+
+-- Parallel Append is not to be used when the subpath depends on the outer param
+create table part_pa_test(a int, b int) partition by range(a);
+create table part_pa_test_p1 partition of part_pa_test for values from (minvalue) to (0);
+create table part_pa_test_p2 partition of part_pa_test for values from (0) to (maxvalue);
+explain (costs off)
+ select (select max((select pa1.b from part_pa_test pa1 where pa1.a = pa2.a)))
+ from part_pa_test pa2;
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Append
+ -> Seq Scan on part_pa_test_p1 pa2
+ -> Seq Scan on part_pa_test_p2 pa2_1
+ SubPlan 1
+ -> Result
+ Filter: (pa1.a = pa2.a)
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Append
+ -> Seq Scan on part_pa_test_p1 pa1
+ -> Seq Scan on part_pa_test_p2 pa1_1
+ SubPlan 2
+ -> Result
+ Optimizer: Postgres query optimizer
+(17 rows)
+
+drop table part_pa_test;
+-- test with leader participation disabled
+set parallel_leader_participation = off;
+explain (costs off)
+ select count(*) from tenk1 where stringu1 = 'GRAAAA';
+ QUERY PLAN
+---------------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Seq Scan on tenk1
+ Filter: (stringu1 = 'GRAAAA'::name)
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+select count(*) from tenk1 where stringu1 = 'GRAAAA';
+ count
+-------
+ 15
+(1 row)
+
+-- test with leader participation disabled, but no workers available (so
+-- the leader will have to run the plan despite the setting)
+set max_parallel_workers = 0;
+explain (costs off)
+ select count(*) from tenk1 where stringu1 = 'GRAAAA';
+ QUERY PLAN
+---------------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Seq Scan on tenk1
+ Filter: (stringu1 = 'GRAAAA'::name)
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+select count(*) from tenk1 where stringu1 = 'GRAAAA';
+ count
+-------
+ 15
+(1 row)
+
+reset max_parallel_workers;
+reset parallel_leader_participation;
+-- test that parallel_restricted function doesn't run in worker
+alter table tenk1 set (parallel_workers = 4);
+explain (verbose, costs off)
+select sp_parallel_restricted(unique1) from tenk1
+ where stringu1 = 'GRAAAA' order by 1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (sp_parallel_restricted(unique1))
+ Merge Key: (sp_parallel_restricted(unique1))
+ -> Sort
+ Output: (sp_parallel_restricted(unique1))
+ Sort Key: (sp_parallel_restricted(tenk1.unique1))
+ -> Seq Scan on public.tenk1
+ Output: sp_parallel_restricted(unique1)
+ Filter: (tenk1.stringu1 = 'GRAAAA'::name)
+ Optimizer: Pivotal Optimizer (GPORCA) version 3.83.0
+ Settings: optimizer=on, parallel_setup_cost=0, parallel_tuple_cost=0
+(11 rows)
+
+-- test parallel plan when group by expression is in target list.
+explain (costs off)
+ select length(stringu1) from tenk1 group by length(stringu1);
+ QUERY PLAN
+------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> HashAggregate
+ Group Key: (length((stringu1)::text))
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: (length((stringu1)::text))
+ -> HashAggregate
+ Group Key: length((stringu1)::text)
+ -> Seq Scan on tenk1
+ Optimizer: Postgres query optimizer
+(9 rows)
+
+select length(stringu1) from tenk1 group by length(stringu1);
+ length
+--------
+ 6
+(1 row)
+
+explain (costs off)
+ select stringu1, count(*) from tenk1 group by stringu1 order by stringu1;
+ QUERY PLAN
+------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: stringu1
+ -> Finalize GroupAggregate
+ Group Key: stringu1
+ -> Sort
+ Sort Key: stringu1
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: stringu1
+ -> Streaming Partial HashAggregate
+ Group Key: stringu1
+ -> Seq Scan on tenk1
+ Optimizer: Pivotal Optimizer (GPORCA) version 3.83.0
+(12 rows)
+
+-- test that parallel plan for aggregates is not selected when
+-- target list contains parallel restricted clause.
+explain (costs off)
+ select sum(sp_parallel_restricted(unique1)) from tenk1
+ group by(sp_parallel_restricted(unique1));
+ QUERY PLAN
+------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Finalize HashAggregate
+ Group Key: (sp_parallel_restricted(unique1))
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: (sp_parallel_restricted(unique1))
+ -> Streaming Partial HashAggregate
+ Group Key: sp_parallel_restricted(unique1)
+ -> Seq Scan on tenk1
+ Optimizer: Pivotal Optimizer (GPORCA)
+(9 rows)
+
+-- test prepared statement
+prepare tenk1_count(integer) As select count((unique1)) from tenk1 where hundred > $1;
+explain (costs off) execute tenk1_count(1);
+ QUERY PLAN
+-----------------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Index Scan using tenk1_hundred on tenk1
+ Index Cond: (hundred > 1)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(6 rows)
+
+execute tenk1_count(1);
+ count
+-------
+ 9800
+(1 row)
+
+deallocate tenk1_count;
+-- test parallel plans for queries containing un-correlated subplans.
+alter table tenk2 set (parallel_workers = 0);
+explain (costs off)
+ select count(*) from tenk1 where (two, four) not in
+ (select hundred, thousand from tenk2 where thousand > 100);
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
+ Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Anti Semi (Not-In) Join
+ Join Filter: ((tenk1.two = tenk2.hundred) AND (tenk1.four = tenk2.thousand))
+ -> Seq Scan on tenk1
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on tenk2
+ Filter: (thousand > 100)
+ Optimizer: Postgres query optimizer
+(10 rows)
+
+select count(*) from tenk1 where (two, four) not in
+ (select hundred, thousand from tenk2 where thousand > 100);
+ count
+-------
+ 10000
+(1 row)
+
+-- this is not parallel-safe due to use of random() within SubLink's testexpr:
+explain (costs off)
+ select * from tenk1 where (unique1 + random())::integer not in
+ (select ten from tenk2);
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Left Anti Semi (Not-In) Join
+ Hash Cond: (int4(((tenk1.unique1)::double precision + random())) = tenk2.ten)
+ -> Seq Scan on tenk1
+ -> Hash
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on tenk2
+ Optimizer: Pivotal Optimizer (GPORCA)
+(8 rows)
+
+alter table tenk2 reset (parallel_workers);
+-- test parallel plan for a query containing initplan.
+set enable_indexscan = off;
+set enable_indexonlyscan = off;
+set enable_bitmapscan = off;
+alter table tenk2 set (parallel_workers = 2);
+explain (costs off)
+ select count(*) from tenk1
+ where tenk1.unique1 = (Select max(tenk2.unique1) from tenk2);
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Nested Loop
+ Join Filter: true
+ -> Broadcast Motion 1:3 (slice2)
+ -> Finalize Aggregate
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ -> Partial Aggregate
+ -> Seq Scan on tenk2
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = (max(tenk2.unique1)))
+ Optimizer: Pivotal Optimizer (GPORCA)
+(13 rows)
+
+select count(*) from tenk1
+ where tenk1.unique1 = (Select max(tenk2.unique1) from tenk2);
+ count
+-------
+ 1
+(1 row)
+
+reset enable_indexscan;
+reset enable_indexonlyscan;
+reset enable_bitmapscan;
+alter table tenk2 reset (parallel_workers);
+-- test parallel index scans.
+set enable_seqscan to off;
+set enable_bitmapscan to off;
+explain (costs off)
+ select count((unique1)) from tenk1 where hundred > 1;
+ QUERY PLAN
+-----------------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Index Scan using tenk1_hundred on tenk1
+ Index Cond: (hundred > 1)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(6 rows)
+
+select count((unique1)) from tenk1 where hundred > 1;
+ count
+-------
+ 9800
+(1 row)
+
+-- test parallel index-only scans.
+explain (costs off)
+ select count(*) from tenk1 where thousand > 95;
+ QUERY PLAN
+------------------------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1
+ Index Cond: (thousand > 95)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(6 rows)
+
+select count(*) from tenk1 where thousand > 95;
+ count
+-------
+ 9040
+(1 row)
+
+-- test rescan cases too
+set enable_material = false;
+explain (costs off)
+select * from
+ (select count(unique1) from tenk1 where hundred > 10) ss
+ right join (values (1),(2),(3)) v(x) on true;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Nested Loop Left Join
+ Join Filter: true
+ -> Values Scan on "Values"
+ -> Materialize
+ -> Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Index Scan using tenk1_hundred on tenk1
+ Index Cond: (hundred > 10)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(10 rows)
+
+select * from
+ (select count(unique1) from tenk1 where hundred > 10) ss
+ right join (values (1),(2),(3)) v(x) on true;
+ count | x
+-------+---
+ 8900 | 1
+ 8900 | 2
+ 8900 | 3
+(3 rows)
+
+explain (costs off)
+select * from
+ (select count(*) from tenk1 where thousand > 99) ss
+ right join (values (1),(2),(3)) v(x) on true;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Nested Loop Left Join
+ Join Filter: true
+ -> Values Scan on "Values"
+ -> Materialize
+ -> Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1
+ Index Cond: (thousand > 99)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(10 rows)
+
+select * from
+ (select count(*) from tenk1 where thousand > 99) ss
+ right join (values (1),(2),(3)) v(x) on true;
+ count | x
+-------+---
+ 9000 | 1
+ 9000 | 2
+ 9000 | 3
+(3 rows)
+
+-- test rescans for a Limit node with a parallel node beneath it.
+reset enable_seqscan;
+set enable_indexonlyscan to off;
+set enable_indexscan to off;
+alter table tenk1 set (parallel_workers = 0);
+alter table tenk2 set (parallel_workers = 1);
+explain (costs off)
+select count(*) from tenk1
+ left join (select tenk2.unique1 from tenk2 order by 1 limit 1000) ss
+ on tenk1.unique1 < ss.unique1 + 1
+ where tenk1.unique1 < 2;
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Nested Loop Left Join
+ Join Filter: (tenk1.unique1 < (tenk2.unique1 + 1))
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 < 2)
+ -> Materialize
+ -> Broadcast Motion 1:3 (slice2)
+ -> Limit
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ Merge Key: tenk2.unique1
+ -> Limit
+ -> Sort
+ Sort Key: tenk2.unique1
+ -> Seq Scan on tenk2
+ Optimizer: Pivotal Optimizer (GPORCA)
+(17 rows)
+
+select count(*) from tenk1
+ left join (select tenk2.unique1 from tenk2 order by 1 limit 1000) ss
+ on tenk1.unique1 < ss.unique1 + 1
+ where tenk1.unique1 < 2;
+ count
+-------
+ 1999
+(1 row)
+
+--reset the value of workers for each table as it was before this test.
+alter table tenk1 set (parallel_workers = 4);
+alter table tenk2 reset (parallel_workers);
+reset enable_material;
+reset enable_bitmapscan;
+reset enable_indexonlyscan;
+reset enable_indexscan;
+-- test parallel bitmap heap scan.
+set enable_seqscan to off;
+set enable_indexscan to off;
+set enable_hashjoin to off;
+set enable_mergejoin to off;
+set enable_material to off;
+-- test prefetching, if the platform allows it
+DO $$
+BEGIN
+ SET effective_io_concurrency = 50;
+EXCEPTION WHEN invalid_parameter_value THEN
+END $$;
+set work_mem='64kB'; --set small work mem to force lossy pages
+WARNING: "work_mem": setting is deprecated, and may be removed in a future release.
+explain (costs off)
+ select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;
+ QUERY PLAN
+---------------------------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Nested Loop
+ Join Filter: true
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on tenk2
+ Filter: (thousand = 0)
+ -> Index Only Scan using tenk1_hundred on tenk1
+ Index Cond: (hundred > 1)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(11 rows)
+
+select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;
+ count
+-------
+ 98000
+(1 row)
+
+create table bmscantest (a int, t text);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
+insert into bmscantest select r, 'fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' FROM generate_series(1,100000) r;
+create index i_bmtest ON bmscantest(a);
+select count(*) from bmscantest where a>1;
+ count
+-------
+ 99999
+(1 row)
+
+-- test accumulation of stats for parallel nodes
+reset enable_seqscan;
+alter table tenk2 set (parallel_workers = 0);
+explain (analyze, timing off, summary off, costs off)
+ select count(*) from tenk1, tenk2 where tenk1.hundred > 1
+ and tenk2.thousand=0;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Finalize Aggregate (actual rows=1 loops=1)
+ -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=3 loops=1)
+ -> Partial Aggregate (actual rows=1 loops=1)
+ -> Nested Loop (actual rows=33200 loops=1)
+ Join Filter: true
+ -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=10 loops=1)
+ -> Seq Scan on tenk2 (actual rows=5 loops=1)
+ Filter: (thousand = 0)
+ Rows Removed by Filter: 3363
+ -> Index Only Scan using tenk1_hundred on tenk1 (actual rows=3018 loops=11)
+ Index Cond: (hundred > 1)
+ Heap Fetches: 0
+ Optimizer: Pivotal Optimizer (GPORCA)
+(13 rows)
+
+alter table tenk2 reset (parallel_workers);
+reset work_mem;
+WARNING: "work_mem": setting is deprecated, and may be removed in a future release.
+create function explain_parallel_sort_stats() returns setof text
+language plpgsql as
+$$
+declare ln text;
+begin
+ for ln in
+ explain (analyze, timing off, summary off, costs off)
+ select * from
+ (select ten from tenk1 where ten < 100 order by ten) ss
+ right join (values (1),(2),(3)) v(x) on true
+ loop
+ ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx', 'g');
+ return next ln;
+ end loop;
+end;
+$$;
+select * from explain_parallel_sort_stats();
+ explain_parallel_sort_stats
+----------------------------------------------------------------------------------
+ Nested Loop Left Join (actual rows=30000 loops=1)
+ Join Filter: true
+ -> Values Scan on "Values" (actual rows=3 loops=1)
+ -> Materialize (actual rows=7500 loops=4)
+ -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=10000 loops=1)
+ -> Seq Scan on tenk1 (actual rows=3386 loops=1)
+ Filter: (ten < 100)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(8 rows)
+
+reset enable_indexscan;
+reset enable_hashjoin;
+reset enable_mergejoin;
+reset enable_material;
+reset effective_io_concurrency;
+drop table bmscantest;
+drop function explain_parallel_sort_stats();
+-- test parallel merge join path.
+analyze tenk2;
+set enable_hashjoin to off;
+set enable_nestloop to off;
+explain (costs off)
+ select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1;
+ QUERY PLAN
+----------------------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Hash Join
+ Hash Cond: (tenk1.unique1 = tenk2.unique1)
+ -> Seq Scan on tenk1
+ -> Hash
+ -> Seq Scan on tenk2
+ Optimizer: Pivotal Optimizer (GPORCA)
+(9 rows)
+
+select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1;
+ count
+-------
+ 10000
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+-- test gather merge
+set enable_hashagg = false;
+explain (costs off)
+ select count(*) from tenk1 group by twenty;
+ QUERY PLAN
+------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Finalize HashAggregate
+ Group Key: twenty
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: twenty
+ -> Streaming Partial HashAggregate
+ Group Key: twenty
+ -> Seq Scan on tenk1
+ Optimizer: Pivotal Optimizer (GPORCA)
+(9 rows)
+
+select count(*) from tenk1 group by twenty;
+ count
+-------
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+(20 rows)
+
+--test expressions in targetlist are pushed down for gather merge
+create function sp_simple_func(var1 integer) returns integer
+as $$
+begin
+ return var1 + 10;
+end;
+$$ language plpgsql PARALLEL SAFE;
+explain (costs off, verbose)
+ select ten, sp_simple_func(ten) from tenk1 where ten < 100 order by ten;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: ten, (sp_simple_func(ten))
+ Merge Key: ten
+ -> Result
+ Output: ten, sp_simple_func(ten)
+ -> Sort
+ Output: ten
+ Sort Key: tenk1.ten
+ -> Seq Scan on public.tenk1
+ Output: ten
+ Filter: (tenk1.ten < 100)
+ Optimizer: Pivotal Optimizer (GPORCA)
+ Settings: enable_hashagg=off, min_parallel_table_scan_size=0, parallel_setup_cost=0, parallel_tuple_cost=0
+(13 rows)
+
+drop function sp_simple_func(integer);
+-- test handling of SRFs in targetlist (bug in 10.0)
+explain (costs off)
+ select count(*), generate_series(1,2) from tenk1 group by twenty;
+ QUERY PLAN
+------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> ProjectSet
+ -> Finalize HashAggregate
+ Group Key: twenty
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: twenty
+ -> Streaming Partial HashAggregate
+ Group Key: twenty
+ -> Seq Scan on tenk1
+ Optimizer: Pivotal Optimizer (GPORCA)
+(10 rows)
+
+select count(*), generate_series(1,2) from tenk1 group by twenty;
+ count | generate_series
+-------+-----------------
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+ 500 | 1
+ 500 | 2
+(40 rows)
+
+-- test gather merge with parallel leader participation disabled
+set parallel_leader_participation = off;
+explain (costs off)
+ select count(*) from tenk1 group by twenty;
+ QUERY PLAN
+------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Finalize HashAggregate
+ Group Key: twenty
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: twenty
+ -> Streaming Partial HashAggregate
+ Group Key: twenty
+ -> Seq Scan on tenk1
+ Optimizer: Pivotal Optimizer (GPORCA)
+(9 rows)
+
+select count(*) from tenk1 group by twenty;
+ count
+-------
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+ 500
+(20 rows)
+
+reset parallel_leader_participation;
+--test rescan behavior of gather merge
+set enable_material = false;
+explain (costs off)
+select * from
+ (select string4, count(unique2)
+ from tenk1 group by string4 order by string4) ss
+ right join (values (1),(2),(3)) v(x) on true;
+ QUERY PLAN
+------------------------------------------------------------------------
+ Nested Loop Left Join
+ Join Filter: true
+ -> Values Scan on "Values"
+ -> Materialize
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Finalize HashAggregate
+ Group Key: tenk1.string4
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: tenk1.string4
+ -> Streaming Partial HashAggregate
+ Group Key: tenk1.string4
+ -> Seq Scan on tenk1
+ Optimizer: Pivotal Optimizer (GPORCA)
+(13 rows)
+
+select * from
+ (select string4, count(unique2)
+ from tenk1 group by string4 order by string4) ss
+ right join (values (1),(2),(3)) v(x) on true;
+ string4 | count | x
+---------+-------+---
+ AAAAxx | 2500 | 1
+ HHHHxx | 2500 | 1
+ OOOOxx | 2500 | 1
+ VVVVxx | 2500 | 1
+ AAAAxx | 2500 | 2
+ HHHHxx | 2500 | 2
+ OOOOxx | 2500 | 2
+ VVVVxx | 2500 | 2
+ AAAAxx | 2500 | 3
+ HHHHxx | 2500 | 3
+ OOOOxx | 2500 | 3
+ VVVVxx | 2500 | 3
+(12 rows)
+
+reset enable_material;
+reset enable_hashagg;
+-- check parallelized int8 aggregate (bug #14897)
+explain (costs off)
+select avg(unique1::int8) from tenk1;
+ QUERY PLAN
+------------------------------------------------
+ Finalize Aggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial Aggregate
+ -> Seq Scan on tenk1
+ Optimizer: Pivotal Optimizer (GPORCA)
+(5 rows)
+
+select avg(unique1::int8) from tenk1;
+ avg
+-----------------------
+ 4999.5000000000000000
+(1 row)
+
+-- gather merge test with a LIMIT
+explain (costs off)
+ select fivethous from tenk1 order by fivethous limit 4;
+ QUERY PLAN
+------------------------------------------------
+ Limit
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: fivethous
+ -> Limit
+ -> Sort
+ Sort Key: fivethous
+ -> Seq Scan on tenk1
+ Optimizer: Pivotal Optimizer (GPORCA)
+(8 rows)
+
+select fivethous from tenk1 order by fivethous limit 4;
+ fivethous
+-----------
+ 0
+ 0
+ 1
+ 1
+(4 rows)
+
+-- gather merge test with 0 worker
+set max_parallel_workers = 0;
+explain (costs off)
+ select string4 from tenk1 order by string4 limit 5;
+ QUERY PLAN
+------------------------------------------------
+ Limit
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: string4
+ -> Limit
+ -> Sort
+ Sort Key: string4
+ -> Seq Scan on tenk1
+ Optimizer: Pivotal Optimizer (GPORCA)
+(8 rows)
+
+select string4 from tenk1 order by string4 limit 5;
+ string4
+---------
+ AAAAxx
+ AAAAxx
+ AAAAxx
+ AAAAxx
+ AAAAxx
+(5 rows)
+
+-- gather merge test with 0 workers, with parallel leader
+-- participation disabled (the leader will have to run the plan
+-- despite the setting)
+set parallel_leader_participation = off;
+explain (costs off)
+ select string4 from tenk1 order by string4 limit 5;
+ QUERY PLAN
+------------------------------------------------
+ Limit
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: string4
+ -> Limit
+ -> Sort
+ Sort Key: string4
+ -> Seq Scan on tenk1
+ Optimizer: Pivotal Optimizer (GPORCA)
+(8 rows)
+
+select string4 from tenk1 order by string4 limit 5;
+ string4
+---------
+ AAAAxx
+ AAAAxx
+ AAAAxx
+ AAAAxx
+ AAAAxx
+(5 rows)
+
+reset parallel_leader_participation;
+reset max_parallel_workers;
+SAVEPOINT settings;
+SET LOCAL force_parallel_mode = 1;
+explain (costs off)
+ select stringu1::int2 from tenk1 where unique1 = 1;
+ QUERY PLAN
+-----------------------------------------------
+ Gather Motion 1:1 (slice1; segments: 1)
+ -> Index Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = 1)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(4 rows)
+
+ROLLBACK TO SAVEPOINT settings;
+-- exercise record typmod remapping between backends
+CREATE FUNCTION make_record(n int)
+ RETURNS RECORD LANGUAGE plpgsql PARALLEL SAFE AS
+$$
+BEGIN
+ RETURN CASE n
+ WHEN 1 THEN ROW(1)
+ WHEN 2 THEN ROW(1, 2)
+ WHEN 3 THEN ROW(1, 2, 3)
+ WHEN 4 THEN ROW(1, 2, 3, 4)
+ ELSE ROW(1, 2, 3, 4, 5)
+ END;
+END;
+$$;
+SAVEPOINT settings;
+SET LOCAL force_parallel_mode = 1;
+SELECT make_record(x) FROM (SELECT generate_series(1, 5) x) ss ORDER BY x;
+ make_record
+-------------
+ (1)
+ (1,2)
+ (1,2,3)
+ (1,2,3,4)
+ (1,2,3,4,5)
+(5 rows)
+
+ROLLBACK TO SAVEPOINT settings;
+DROP function make_record(n int);
+-- test the sanity of parallel query after the active role is dropped.
+drop role if exists regress_parallel_worker;
+create role regress_parallel_worker;
+set role regress_parallel_worker;
+reset session authorization;
+drop role regress_parallel_worker;
+set force_parallel_mode = 1;
+select count(*) from tenk1;
+ count
+-------
+ 10000
+(1 row)
+
+reset force_parallel_mode;
+reset role;
+-- Window function calculation can't be pushed to workers.
+explain (costs off, verbose)
+ select count(*) from tenk1 a where (unique1, two) in
+ (select unique1, row_number() over() from tenk1 b);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: count(*)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: (PARTIAL count(*))
+ -> Partial Aggregate
+ Output: PARTIAL count(*)
+ -> Hash Semi Join
+ Hash Cond: ((a.unique1 = b.unique1) AND (a.two = (row_number() OVER (?))))
+ -> Seq Scan on public.tenk1 a
+ Output: a.unique1, a.unique2, a.two, a.four, a.ten, a.twenty, a.hundred, a.thousand, a.twothousand, a.fivethous, a.tenthous, a.odd, a.even, a.stringu1, a.stringu2, a.string4
+ -> Hash
+ Output: b.unique1, (row_number() OVER (?))
+ -> Redistribute Motion 1:3 (slice2; segments: 1)
+ Output: b.unique1, (row_number() OVER (?))
+ Hash Key: b.unique1
+ -> WindowAgg
+ Output: b.unique1, row_number() OVER (?)
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ Output: b.unique1
+ -> Seq Scan on public.tenk1 b
+ Output: b.unique1
+ Optimizer: Postgres query optimizer
+ Settings: min_parallel_table_scan_size=0, parallel_setup_cost=0, parallel_tuple_cost=0
+(23 rows)
+
+-- LIMIT/OFFSET within sub-selects can't be pushed to workers.
+explain (costs off)
+ select * from tenk1 a where two in
+ (select two from tenk1 b where stringu1 like '%AAAA' limit 3);
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Hash Join
+ Hash Cond: (tenk1.two = tenk1_1.two)
+ -> Seq Scan on tenk1
+ -> Hash
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> GroupAggregate
+ Group Key: tenk1_1.two
+ -> Sort
+ Sort Key: tenk1_1.two
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Hash Key: tenk1_1.two
+ -> GroupAggregate
+ Group Key: tenk1_1.two
+ -> Sort
+ Sort Key: tenk1_1.two
+ -> Redistribute Motion 1:3 (slice4)
+ -> Limit
+ -> Gather Motion 3:1 (slice5; segments: 3)
+ -> Limit
+ -> Seq Scan on tenk1 tenk1_1
+ Filter: (stringu1 ~~ '%AAAA'::text)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(23 rows)
+
+-- to increase the parallel query test coverage
+SAVEPOINT settings;
+SET LOCAL force_parallel_mode = 1;
+EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1;
+ QUERY PLAN
+----------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (actual rows=10000 loops=1)
+ -> Seq Scan on tenk1 (actual rows=3386 loops=1)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(3 rows)
+
+ROLLBACK TO SAVEPOINT settings;
+-- provoke error in worker
+-- (make the error message long enough to require multiple bufferloads)
+SAVEPOINT settings;
+SET LOCAL force_parallel_mode = 1;
+select (stringu1 || repeat('abcd', 5000))::int2 from tenk1 where unique1 = 1;
+ERROR: invalid input syntax for type smallint: "BAAAAAabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcda [...]
+ROLLBACK TO SAVEPOINT settings;
+-- test interaction with set-returning functions
+SAVEPOINT settings;
+-- multiple subqueries under a single Gather node
+-- must set parallel_setup_cost > 0 to discourage multiple Gather nodes
+SET LOCAL parallel_setup_cost = 10;
+EXPLAIN (COSTS OFF)
+SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1
+UNION ALL
+SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1;
+ QUERY PLAN
+----------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Append
+ -> Seq Scan on tenk1
+ Filter: (fivethous = (tenthous + 1))
+ -> Seq Scan on tenk1 tenk1_1
+ Filter: (fivethous = (tenthous + 1))
+ Optimizer: Pivotal Optimizer (GPORCA)
+(7 rows)
+
+ROLLBACK TO SAVEPOINT settings;
+-- can't use multiple subqueries under a single Gather node due to initPlans
+EXPLAIN (COSTS OFF)
+SELECT unique1 FROM tenk1 WHERE fivethous =
+ (SELECT unique1 FROM tenk1 WHERE fivethous = 1 LIMIT 1)
+UNION ALL
+SELECT unique1 FROM tenk1 WHERE fivethous =
+ (SELECT unique2 FROM tenk1 WHERE fivethous = 1 LIMIT 1)
+ORDER BY 1;
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: tenk1.unique1
+ -> Sort
+ Sort Key: tenk1.unique1
+ -> Append
+ -> Hash Join
+ Hash Cond: (tenk1.fivethous = tenk1_1.unique1)
+ -> Seq Scan on tenk1
+ -> Hash
+ -> Broadcast Motion 1:3 (slice2)
+ -> Limit
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ -> Seq Scan on tenk1 tenk1_1
+ Filter: (fivethous = 1)
+ -> Hash Join
+ Hash Cond: (tenk1_2.fivethous = tenk1_3.unique2)
+ -> Seq Scan on tenk1 tenk1_2
+ -> Hash
+ -> Broadcast Motion 1:3 (slice4)
+ -> Limit
+ -> Gather Motion 3:1 (slice5; segments: 3)
+ -> Limit
+ -> Seq Scan on tenk1 tenk1_3
+ Filter: (fivethous = 1)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(25 rows)
+
+-- test interaction with SRFs
+SELECT * FROM information_schema.foreign_data_wrapper_options
+ORDER BY 1, 2, 3;
+ foreign_data_wrapper_catalog | foreign_data_wrapper_name | option_name | option_value
+------------------------------+---------------------------+-------------+--------------
+(0 rows)
+
+-- test passing expanded-value representations to workers
+CREATE FUNCTION make_some_array(int,int) returns int[] as
+$$declare x int[];
+ begin
+ x[1] := $1;
+ x[2] := $2;
+ return x;
+ end$$ language plpgsql parallel safe;
+CREATE TABLE fooarr(f1 text, f2 int[], f3 text);
+INSERT INTO fooarr VALUES('1', ARRAY[1,2], 'one');
+PREPARE pstmt(text, int[]) AS SELECT * FROM fooarr WHERE f1 = $1 AND f2 = $2;
+EXPLAIN (COSTS OFF) EXECUTE pstmt('1', make_some_array(1,2));
+ QUERY PLAN
+------------------------------------------------------------------
+ Gather Motion 1:1 (slice1; segments: 1)
+ -> Seq Scan on fooarr
+ Filter: ((f1 = '1'::text) AND (f2 = '{1,2}'::integer[]))
+ Optimizer: Pivotal Optimizer (GPORCA)
+(4 rows)
+
+EXECUTE pstmt('1', make_some_array(1,2));
+ f1 | f2 | f3
+----+-------+-----
+ 1 | {1,2} | one
+(1 row)
+
+DEALLOCATE pstmt;
+-- test interaction between subquery and partial_paths
+CREATE VIEW tenk1_vw_sec WITH (security_barrier) AS SELECT * FROM tenk1;
+EXPLAIN (COSTS OFF)
+SELECT 1 FROM tenk1_vw_sec
+ WHERE (SELECT sum(f1) FROM int4_tbl WHERE f1 < unique1) < 100;
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Subquery Scan on tenk1_vw_sec
+ Filter: ((SubPlan 1) < 100)
+ -> Seq Scan on tenk1
+ SubPlan 1
+ -> Aggregate
+ -> Result
+ Filter: (int4_tbl.f1 < tenk1_vw_sec.unique1)
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Seq Scan on int4_tbl
+ Optimizer: Postgres query optimizer
+(12 rows)
+
+rollback;
diff --git a/src/test/regress/expected/subselect_gp_optimizer.out b/src/test/regress/expected/subselect_gp_optimizer.out
index f6e8ea53bd..6484fc977c 100644
--- a/src/test/regress/expected/subselect_gp_optimizer.out
+++ b/src/test/regress/expected/subselect_gp_optimizer.out
@@ -1723,24 +1723,23 @@ EXPLAIN select count(*) from
where unique1 IN (select hundred from tenk1 b)) ss;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
- Finalize Aggregate (cost=0.00..431.98 rows=1 width=8)
- -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.98 rows=1 width=8)
- -> Partial Aggregate (cost=0.00..431.98 rows=1 width=8)
- -> Nested Loop (cost=0.00..431.98 rows=34 width=1)
- Join Filter: true
- -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.94 rows=100 width=4)
+ Finalize Aggregate (cost=0.00..864.06 rows=1 width=8)
+ -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..864.06 rows=1 width=8)
+ -> Partial Aggregate (cost=0.00..864.06 rows=1 width=8)
+ -> Hash Join (cost=0.00..864.06 rows=34 width=1)
+ Hash Cond: (tenk1.unique1 = tenk1_1.hundred)
+ -> Seq Scan on tenk1 (cost=0.00..431.50 rows=3334 width=4)
+ -> Hash (cost=431.94..431.94 rows=34 width=4)
-> GroupAggregate (cost=0.00..431.94 rows=34 width=4)
- Group Key: tenk1.hundred
+ Group Key: tenk1_1.hundred
-> Sort (cost=0.00..431.94 rows=34 width=4)
- Sort Key: tenk1.hundred
- -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..431.94 rows=34 width=4)
- Hash Key: tenk1.hundred
- -> Streaming HashAggregate (cost=0.00..431.94 rows=34 width=4)
- Group Key: tenk1.hundred
- -> Seq Scan on tenk1 (cost=0.00..431.51 rows=3334 width=4)
- -> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1 (cost=0.00..0.04 rows=1 width=1)
- Index Cond: (unique1 = tenk1.hundred)
- Optimizer: Pivotal Optimizer (GPORCA) version 2.75.0
+ Sort Key: tenk1_1.hundred
+ -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..431.93 rows=34 width=4)
+ Hash Key: tenk1_1.hundred
+ -> Streaming HashAggregate (cost=0.00..431.93 rows=34 width=4)
+ Group Key: tenk1_1.hundred
+ -> Seq Scan on tenk1 tenk1_1 (cost=0.00..431.50 rows=3334 width=4)
+ Optimizer: Pivotal Optimizer (GPORCA)
(17 rows)
EXPLAIN select count(distinct ss.ten) from
@@ -1774,25 +1773,24 @@ EXPLAIN select count(*) from
where unique1 IN (select distinct hundred from tenk1 b)) ss;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
- Finalize Aggregate (cost=0.00..431.99 rows=1 width=8)
- -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.99 rows=1 width=8)
- -> Partial Aggregate (cost=0.00..431.99 rows=1 width=8)
- -> Nested Loop (cost=0.00..431.99 rows=34 width=1)
- Join Filter: true
- -> Broadcast Motion 3:3 (slice2; segments: 3) (cost=0.00..431.95 rows=100 width=4)
+ Finalize Aggregate (cost=0.00..864.06 rows=1 width=8)
+ -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..864.06 rows=1 width=8)
+ -> Partial Aggregate (cost=0.00..864.06 rows=1 width=8)
+ -> Hash Semi Join (cost=0.00..864.06 rows=34 width=1)
+ Hash Cond: (tenk1.unique1 = tenk1_1.hundred)
+ -> Seq Scan on tenk1 (cost=0.00..431.50 rows=3334 width=4)
+ -> Hash (cost=431.94..431.94 rows=34 width=4)
-> GroupAggregate (cost=0.00..431.94 rows=34 width=4)
- Group Key: tenk1.hundred
+ Group Key: tenk1_1.hundred
-> Sort (cost=0.00..431.94 rows=34 width=4)
- Sort Key: tenk1.hundred
- -> Redistribute Motion 3:3 (slice3; segments: 3) (cost=0.00..431.94 rows=34 width=4)
- Hash Key: tenk1.hundred
- -> Streaming HashAggregate (cost=0.00..431.94 rows=34 width=4)
- Group Key: tenk1.hundred
- -> Seq Scan on tenk1 (cost=0.00..431.51 rows=3334 width=4)
- -> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1 (cost=0.00..0.04 rows=1 width=1)
- Index Cond: (unique1 = tenk1.hundred)
+ Sort Key: tenk1_1.hundred
+ -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..431.93 rows=34 width=4)
+ Hash Key: tenk1_1.hundred
+ -> Streaming HashAggregate (cost=0.00..431.93 rows=34 width=4)
+ Group Key: tenk1_1.hundred
+ -> Seq Scan on tenk1 tenk1_1 (cost=0.00..431.50 rows=3334 width=4)
Optimizer: Pivotal Optimizer (GPORCA)
-(18 rows)
+(17 rows)
EXPLAIN select count(distinct ss.ten) from
(select ten from tenk1 a
diff --git a/src/test/regress/sql/gp_covering_index.sql b/src/test/regress/sql/gp_covering_index.sql
index c6e7f37a92..7f142c738e 100644
--- a/src/test/regress/sql/gp_covering_index.sql
+++ b/src/test/regress/sql/gp_covering_index.sql
@@ -163,12 +163,13 @@ SELECT a, b, c FROM test_ao WHERE c>42;
-- Check that the best cover index is chosen for a plan when multiple cover
-- indexes are available.
CREATE TABLE test_select_best_cover(a int, b int, c int);
-CREATE INDEX i_test_select_best_cover_a ON test_select_best_cover(a);
+CREATE INDEX i_test_select_best_cover_a_bc ON test_select_best_cover(a) INCLUDE (b, c);
CREATE INDEX i_test_select_best_cover_a_b ON test_select_best_cover(a) INCLUDE (b);
+CREATE INDEX i_test_select_best_cover_a ON test_select_best_cover(a);
CREATE INDEX i_test_select_best_cover_ab ON test_select_best_cover(a, b);
+CREATE INDEX i_test_select_best_cover_b_ac ON test_select_best_cover(b) INCLUDE (a, c);
+CREATE INDEX i_test_select_best_cover_b_a ON test_select_best_cover(b) INCLUDE (a);
CREATE INDEX i_test_select_best_cover_b ON test_select_best_cover(b);
-CREATE INDEX i_test_select_best_cover_b_a ON test_select_best_cover(a) INCLUDE (b);
-CREATE INDEX i_test_select_best_cover_a_bc ON test_select_best_cover(a) INCLUDE (b, c);
INSERT INTO test_select_best_cover SELECT i, i+i, i*i FROM generate_series(1, 100)i;
VACUUM ANALYZE test_select_best_cover;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org