[jira] [Comment Edited] (DERBY-6949) Analysis of Derby Cost Estimation

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

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

Harshvardhan Gupta edited comment on DERBY-6949 at 8/3/17 5:36 PM:
-------------------------------------------------------------------

By Cost we generally refer to any number proportional to the execution time, in Derby Cardinality estimates are also clubbed in the bigger cost structure.

Cost modelling for Join Strategies generally 

1) Nested Loop - After finding out the cost of a single probe in the inner resultset, nested loop multiplies the cost with the number of rows in outer resultset. The cost of these resultsets come from cost modelling by Optimizables which also serve as entities in cost estimation. For Example - Two base tables joined via a loop join serve as Optimizables where the outer table reports its scanning cost and the inner table reports the cost of lookup of a single row matching the outer row. Note that even though the cost is multiplied, the cardinality estimates are not simply multiplied for the two base tables (or intermediate results)

2) Hash Join - As opposed to Nested loop joins, there is no fixed cost for each outer row in case of hash joins and the cost for hash joins are only dependent on the base tables / intermediate results (i.e called optimizables). Thus at the join level no further cost changes are made.


was (Author: harshvardhan145):
By Cost we generally refer to any number proportional to the execution time, in Derby Cardinality estimates are also clubbed in the bigger cost structure.

Cost modelling for Join Strategies generally 

1) Nested Loop - After finding out the cost of a single probe in the inner resultset, nested loop multiplies the cost with the number of rows in outer resultset. The cost of these resultsets come from cost modelling by Optimizables which also serve as entities in cost estimation. For Example - Two base tables joined via a loop join serve as Optimizables where the outer table reports its scanning cost and the inner table reports the cost of lookup of a single row matching the outer row. Note that even though the cost is multiplied, the cardinality estimates are not simply multiplied for the two base tables.

2) Hash Join - As opposed to Nested loop joins, there is no fixed cost for each outer row in case of hash joins and the cost for hash joins are only dependent on the base tables / intermediate results (i.e called optimizables). Thus at the join level no further cost changes are made.

> Analysis of Derby Cost Estimation
> ---------------------------------
>
>                 Key: DERBY-6949
>                 URL: https://issues.apache.org/jira/browse/DERBY-6949
>             Project: Derby
>          Issue Type: Sub-task
>          Components: SQL
>            Reporter: Harshvardhan Gupta
>            Assignee: Harshvardhan Gupta
>




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