Hi :
Sorry for disturbing. I don't know if it is ok to put this question here.
I want to learn more about hash join's cost calculation.
And I found the following function of PostgreSQL9.2.1. The hash join cost is calculated.
But what confused me is a reuction calculation:
qp_qual_cost.per_tuple -= hash_qual_cost.per_tuple;
I can understand that
when cost is evaluated, Adding and Multipling is needed.
My question is:
Why the reduction is needed here for cost calculation?
In fact , For my sql statement:
<select * from sales s inner join customers c on s.cust_id = c.cust_id;>
When I set cpu_operator_cost to 0.0025,
qp_qual_cost.per_tuple and hash_qual_cost.per_tuple are all 0.0025.
So after reduction, qp_qual_cost.per_tuple is set to 0.
When I set cpu_operator_cost to 0.0020,
qp_qual_cost.per_tuple and hash_qual_cost.per_tuple are all 0.0020.
So after reduction, qp_qual_cost.per_tuple is set to 0.
I think that per_tuple cost can not be omitted here.
The following is the source part related to qp_qual_cost.per_tuple .
---------------------------------------------------------------------------------------------------
void
final_cost_hashjoin(PlannerInfo *root, HashPath *path,
JoinCostWorkspace *workspace,
SpecialJoinInfo *sjinfo,
SemiAntiJoinFactors *semifactors)
{
…
Cost cpu_per_tuple;
QualCost hash_qual_cost;
QualCost qp_qual_cost;
…
/*
* Compute cost of the hashquals and qpquals (other restriction clauses)
* separately.
*/
cost_qual_eval(&hash_qual_cost, hashclauses, root);
cost_qual_eval(&qp_qual_cost, path->jpath.joinrestrictinfo, root);
…
qp_qual_cost.startup -= hash_qual_cost.startup;
qp_qual_cost.per_tuple -= hash_qual_cost.per_tuple;
…
/*
* For each tuple that gets through the hashjoin proper, we charge
* cpu_tuple_cost plus the cost of evaluating additional restriction
* clauses that are to be applied at the join. (This is pessimistic since
* not all of the quals may get evaluated at each tuple.)
*/
startup_cost += qp_qual_cost.startup;
cpu_per_tuple = cpu_tuple_cost + qp_qual_cost.per_tuple;
…
run_cost += cpu_per_tuple * hashjointuples;
path->jpath.path.startup_cost = startup_cost;
path->jpath.path.total_cost = startup_cost + run_cost;
…
}
---------------------------------------------------------------------------------------------------
Thanks !
