[
https://issues.apache.org/jira/browse/DERBY-6942?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16071403#comment-16071403
]
Bryan Pendleton commented on DERBY-6942:
----------------------------------------
Let me see if I understand your conclusions so far: comparing z12.txt, and
z13.txt, when the optimizer is considering the conglomerateNumber 1168, which
is your base table ("abcd"), the optimizer has the qualifier "id is null" to
consider. It reports that as an "extra qualifier", and applies a selectivity
estimate to make a guess as to how many rows from the base table will match
that predicate.
Without your new statistics, we have z12.txt, where the optimizer reports:
Number of extra qualifiers is : 1, extra qualifier selectivity is : 0.1
With your new statistics, we have z13.txt, where the optimizer reports:
Number of extra qualifiers is : 1, extra qualifier selectivity is : 0.5
Your point is that, in the first case, the 0.1 is a hard-coded selectivity
estimate which may or may not be accurate at all, while in the second case,
where we have 0.5, this comes from a real observation of the data which counted
the number of rows which had a null value for "id" compared to the total number
of rows in the table.
Am I understanding correctly? That's neat!
> Utilise additional statistics for selectivity estimates.
> --------------------------------------------------------
>
> Key: DERBY-6942
> URL: https://issues.apache.org/jira/browse/DERBY-6942
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Reporter: Harshvardhan Gupta
> Assignee: Harshvardhan Gupta
> Priority: Minor
> Attachments: DERBY-6942.diff, z12.txt, z13.txt
>
>
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
