Greetings, * 高健 (luckyjackgao@xxxxxxxxx) wrote: > 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; > > My question is: > > Why the reduction is needed here for cost calculation? cost_qual_eval(&hash_qual_cost, hashclauses, root); returns the costs for *just the quals which can be used for the hashjoin*, while cost_qual_eval(&qp_qual_cost, path->jpath.joinrestrictinfo, root); returns the costs for *ALL the quals* qp_qual_cost.startup -= hash_qual_cost.startup; and qp_qual_cost.per_tuple -= hash_qual_cost.per_tuple; extract the cost attributed to the quals used in the hashjoin from the cost of the other quals in the overall expression. The reason that we do this is because we're going to use a hashjoin-specific costing for the qual costs later on in final_cost_hashjoin: startup_cost += hash_qual_cost.startup; run_cost += hash_qual_cost.per_tuple * outer_path_rows * clamp_row_est(inner_path_rows * innerbucketsize) * 0.5; if we didn't do that, we'd end up double-counting those costs. > 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. Yes, because ALL the quals involved in your statement are quals being used for the hashjoin- and those costs are calculated later on, as I illustrated above. > I think that per_tuple cost can not be omitted here. The per-tuple cost isn't omitted, it's added in later based on the expected costs for doing those per-tuple operations for building and using the hash table. Thanks, Stephen
Attachment:
signature.asc
Description: Digital signature
