Repository navigation
Sampled partition stats have greatly underestimated NDVs for some columns #50
Description
Incorporated from: prestodb/presto#11959 (discussion there)
tpch_sf100. orders (partitioned):
presto> show stats for hive.tpch_sf100_orc_part.orders;
column_name | data_size | distinct_values_count | nulls_fraction | row_count | low_value | high_value
---------------+---------------------+-----------------------+----------------+----------------+------------+------------
orderkey | NULL | 62881.0 | 0.0 | NULL | 162 | 599999942
custkey | NULL | 62881.0 | 0.0 | NULL | 2 | 14999998
orderstatus | 1.4999663244E8 | 3.0 | 0.0 | NULL | NULL | NULL
totalprice | NULL | 62881.0 | 0.0 | NULL | 851.17 | 571417.48
orderpriority | 1.25999070546E9 | 5.0 | 0.0 | NULL | NULL | NULL
clerk | 2.2499494866E9 | 49276.0 | 0.0 | NULL | NULL | NULL
shippriority | NULL | 1.0 | 0.0 | NULL | 0 | 0
comment | 7.275173681759999E9 | 62881.0 | 0.0 | NULL | NULL | NULL
orderdate | NULL | 2406.0 | 0.0 | NULL | 1992-01-01 | 1998-08-02
NULL | NULL | NULL | NULL | 1.4999663244E8 | NULL | NULL
(10 rows)
presto> show stats for hive.tpch_sf100_orc.orders;
column_name | data_size | distinct_values_count | nulls_fraction | row_count | low_value | high_value
---------------+----------------------+-----------------------+----------------+-----------+------------+------------
orderkey | NULL | 1.5E8 | 0.0 | NULL | 1 | 600000000
custkey | NULL | 1.0165457E7 | 0.0 | NULL | 1 | 14999999
orderstatus | 1.5E8 | 3.0 | 0.0 | NULL | NULL | NULL
totalprice | NULL | 3.4038282E7 | 0.0 | NULL | 811.73 | 591036.15
orderdate | NULL | 2449.0 | 0.0 | NULL | 1992-01-01 | 1998-08-02
orderpriority | 1.2600248040000002E9 | 5.0 | 0.0 | NULL | NULL | NULL
clerk | 2.25E9 | 100816.0 | 0.0 | NULL | NULL | NULL
shippriority | NULL | 1.0 | 0.0 | NULL | 0 | 0
comment | 7.275038709E9 | 1.12463191E8 | 0.0 | NULL | NULL | NULL
NULL | NULL | NULL | NULL | 1.5E8 | NULL | NULL
Should we add extrapolate NDVs instead? It seems that partitions might often be different chunks of data so that NDVs don't overlap.
Alternatively we could store HLL state per column as an auxiliary partition property and calculate extrapolation based on merged HLLs
Reacted by Piotr Findeisen
Activity
arhimondr commentedon Jan 25, 2019
Currently we chose the maximum NDV. If we decided to sum NDV's then we would need to extrapolate.
It depends for what column, and what type of partitions. For daily rolling partitions NDV will be likely overlapping.
I think that's the only options we have. In order to do so - we need to store HLL states per columns in the partition properties, as the Mestastore API doesn't allow you to store arbitrary statistics.
arhimondr commentedon Jan 25, 2019
@findepi I'm not sure if we should call it a "bug". The decision of taking MAX NDV was a thoughtful decision. Let's change the label to "enhancment".
sopel39 commentedon Jan 25, 2019
We could store HLL in table/partition properties
sopel39 commentedon Jan 25, 2019
Yup. Discussion from original issue: let's say you partition by date and you have two data columns:
userIdeventIdThen for 1) you should rather take max (users come from the same id pool), even though userId NDV could be approximate the same as partition row count
For 2) you should sum because each eventId is unique.
[MINOR] Cleanup HudiSplitFactory to extend cache support (trinodb#50)