[jira] [Updated] (DERBY-6942) Utilise additional statistics for selectivity estimates.

["Harshvardhan Gupta (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-6942?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Harshvardhan Gupta updated DERBY-6942:
--------------------------------------
    Attachment: z12.txt

Please find attached the optimizer trace of a specimen query.

Table DDL -

create table abcd(id integer);
create index idx on abcd(id);

Query issued - 

select * from ABCD where id is NULL

As the optimizer trace shows - There are two possible access paths - one via the table scan and one via the conglomerate backing the index. Each of the access paths can be queried via two lookup (or join strategies in case of joins), in our case a loop or a hash lookup.

During the index scan, the predicate (ID is NULL) acts as a valid start and stop predicate and store makes accurate representation regarding cardinality estimates from index lookup. In case of table scan, the predicate acts as a qualifier (a predicate that will restrict or filter the result set obtained via scan but won't help to locate the position of starting or ending point as opposed to index lookups). As evident in the trace output, a hard wired selectivity of 0.1 is being used here in this case of table scan for cardinality estimate. We have a opportunity to use statistics in such cases to remove our dependancy on hardwired estimates.

> 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: z12.txt
>
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)