[
https://issues.apache.org/jira/browse/HIVE-29334?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18107455#comment-18107455
]
Thomas Rebele commented on HIVE-29334:
--------------------------------------
Some background info: the problem seems to be KLL only. I could not reproduce
it with the t-digest sketch (based on the [issue that I had
created|https://github.com/apache/datasketches-java/issues/693#issue-3652383497]):
{code:java}
public class ExperimentDeterministicMergeTdigest {
@Test
public void test() throws NoSuchAlgorithmException {
Random rnd = new Random();
TDigestDouble t1 = new TDigestDouble();
for(int i=0; i<20000; i++) {
t1.update(rnd.nextFloat());
}
byte[] tb1 = t1.toByteArray();
TDigestDouble t2 = new TDigestDouble();
for(int i=0; i<20000; i++) {
t2.update(rnd.nextFloat());
}
byte[] tb2 = t2.toByteArray();
HashSet<BigInteger> digests = new HashSet<>();
for(int i=0; i<300; i++) {
TDigestDouble start = new TDigestDouble();
byte[] h1 = Arrays.copyOf(tb1, tb2.length);
byte[] h2 = Arrays.copyOf(tb2, tb2.length);
TDigestDouble kll1 = TDigestDouble.heapify(MemorySegment.ofArray(h1));
start.merge(kll1);
TDigestDouble kll2 = TDigestDouble.heapify(MemorySegment.ofArray(h2));
start.merge(kll2);
MessageDigest md5 = MessageDigest.getInstance("MD5");
BigInteger digest = new BigInteger(md5.digest(start.toByteArray()));
digests.add(digest);
System.out.println(digest);
}
assertEquals(1, digests.size());
}
}{code}
The hashes of the 300 merge results are the same.
> Selectivity estimates from histograms are unstable for columns in large tables
> ------------------------------------------------------------------------------
>
> Key: HIVE-29334
> URL: https://issues.apache.org/jira/browse/HIVE-29334
> Project: Hive
> Issue Type: Bug
> Affects Versions: 4.2.0
> Reporter: Thomas Rebele
> Assignee: Thomas Rebele
> Priority: Major
>
> Executing a query as simple as
> {code:java}
> explain cbo joincost select count(*) from catalog_returns where
> cr_return_amount > 100;
> {code}
> on TPC-DS 30TB with histograms showed an unstable rowcount estimation for the
> HiveFilter:
> {code:java}
> 0: jdbc:hive2://localhost:10002> explain cbo joincost select count(*) from
> catalog_returns where cr_return_amount > 100;
> +----------------------------------------------------+
> | Explain |
> +----------------------------------------------------+
> | CBO PLAN: |
> | HiveProject(_c0=[$0]): rowcount = 1.0, cumulative cost = \{0.0 rows, 0.0
> cpu, 0.0 io}, id = 90 |
> | HiveAggregate(group=[{}], agg#0=[count()]): rowcount = 1.0, cumulative
> cost = \{0.0 rows, 0.0 cpu, 0.0 io}, id = 88 |
> | HiveFilter(condition=[>($17, 100:DECIMAL(3, 0))]): rowcount =
> 3.363572998E9, cumulative cost = \{0.0 rows, 0.0 cpu, 0.0 io}, id = 87 |
> | HiveTableScan(table=[[default, catalog_returns]],
> table:alias=[catalog_returns]): rowcount = 4.320980099E9, cumulative cost =
> \{0}, id = 43 |
> +----------------------------------------------------+
> 0: jdbc:hive2://localhost:10002> explain cbo joincost select count(*) from
> catalog_returns where cr_return_amount > 100;
> +----------------------------------------------------+
> | Explain |
> +----------------------------------------------------+
> | CBO PLAN: |
> | HiveProject(_c0=[$0]): rowcount = 1.0, cumulative cost = \{0.0 rows, 0.0
> cpu, 0.0 io}, id = 181 |
> | HiveAggregate(group=[{}], agg#0=[count()]): rowcount = 1.0, cumulative
> cost = \{0.0 rows, 0.0 cpu, 0.0 io}, id = 179 |
> | HiveFilter(condition=[>($17, 100:DECIMAL(3, 0))]): rowcount =
> 3.365670118E9, cumulative cost = \{0.0 rows, 0.0 cpu, 0.0 io}, id = 178 |
> | HiveTableScan(table=[[default, catalog_returns]],
> table:alias=[catalog_returns]): rowcount = 4.320980099E9, cumulative cost =
> \{0}, id = 134 |
> +----------------------------------------------------+
> 0: jdbc:hive2://localhost:10002> explain cbo joincost select count(*) from
> catalog_returns where cr_return_amount > 100;
> +----------------------------------------------------+
> | Explain |
> +----------------------------------------------------+
> | CBO PLAN: |
> | HiveProject(_c0=[$0]): rowcount = 1.0, cumulative cost = \{0.0 rows, 0.0
> cpu, 0.0 io}, id = 272 |
> | HiveAggregate(group=[{}], agg#0=[count()]): rowcount = 1.0, cumulative
> cost = \{0.0 rows, 0.0 cpu, 0.0 io}, id = 270 |
> | HiveFilter(condition=[>($17, 100:DECIMAL(3, 0))]): rowcount =
> 3.353988358E9, cumulative cost = \{0.0 rows, 0.0 cpu, 0.0 io}, id = 269 |
> | HiveTableScan(table=[[default, catalog_returns]],
> table:alias=[catalog_returns]): rowcount = 4.320980099E9, cumulative cost =
> \{0}, id = 225 |
> | |
> +----------------------------------------------------+
> {code}
> I've debugged a bit and followed the route of the rowcount estimate:
> * RelMdUtil#estimateFilteredRows(RelNode, RexNode, RelMetadataQuery)
> * HiveRelMdSelectivity#getSelectivity(HiveTableScan, RelMetadataQuery,
> RexNode)
> * FilterSelectivityEstimator#computeRangePredicateSelectivity
> * ColumnStatsAggregator#mergeHistograms
> The problem is reproducible with a simpler unit test. I could reduce it using
> just classes of Apache DataSketches.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
