Search Postgresql Archives

Re: Why hash join cost calculation need reduction

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux