[jira] [Comment Edited] (TRAFODION-1618) Row count estimation method used in Update stats can be improved

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-1618?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15036910#comment-15036910 ] 

David Wayne Birdsall edited comment on TRAFODION-1618 at 12/3/15 12:03 AM:
---------------------------------------------------------------------------

I did an experiment. I created a table like the one mentioned here, then used a TRANSPOSE query to populate it with 2,000,000 rows. I then exited and re-entered sqlci. I did an "explain options 'f' select * from t", and the cardinality estimate came back as 2.25E+06, that is, 2,250,000 rows. Not bad actually; 12.5% off. I began debugging to learn where the cardinality esimate comes from. I went into and out of sqlci a few times. Then on the next test, "explain options 'f' select * from t" showed a cardinality estimate of 2.00E+06 (in the debugger, I saw 2,004,630, which is well within 1%). Note that I did not do an "update statistics" in the meantime. So, something is happening -- perhaps at the HBase or HDFS level -- that causes an improved estimate over time. Perhaps compactions. This might be a hint that the problem is below Trafodion.

In this experiment, I populated only the key values. I left the remaining values to default. Most of them default to the empty string or to the null value. My point being that in my test, the rows were perhaps very short compared to what might have been true in the original problem.


was (Author: davebirdsall):
I did an experiment. I created a table like the one mentioned here, then used a TRANSPOSE query to populate it with 2,000,000 rows. I then exited and re-entered sqlci. I did an "explain options 'f' select * from t", and the cardinality estimate came back as 2.25E+06, that is, 2,250,000 rows. Not bad actually; 12.5% off. I began debugging to learn where the cardinality esimate comes from. I went into and out of sqlci a few times. Then on the next test, "explain options 'f' select * from t" showed a cardinality estimate of 2.00E+06 (in the debugger, I saw 2,004,630, which is well within 1%). Note that I did not do an "update statistics" in the meantime. So, something is happening -- perhaps at the HBase or HDFS level -- that causes an improved estimate over time. Perhaps compactions. This might be a hint that the problem is below Trafodion.

> Row count estimation method used in Update stats can be improved
> ----------------------------------------------------------------
>
>                 Key: TRAFODION-1618
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-1618
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>            Reporter: Qifan Chen
>            Assignee: David Wayne Birdsall
>
> For a customer table, the row count is estimated as 2343750004, while the actual row account is 25055150. 



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)