anomaly regarding first entry in explain and explain join

[Former Member]

Hello there,

I have TPC-H benchmark database and I ran the following query with and without index on the column p_retailprice

select
	s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment
from
	dbo.part,dbo.supplier,dbo.partsupp,dbo.nation,dbo.region
where
	p_partkey = ps_partkey and p_retailprice <= 1.0

with index

first entry in Expalin is of PART table , strategy is :range condition for index" and pagecount is 3627

first entry in explain join has table name PART and accumalated cost is 2

without index

both the explains have the value 17239 for pagecount and accumalated cost for the same entry

My question is what is the relation between first entry in both explains? The pagecount and accumalated costs(for the first entry) for all queries when no index is applied comes to be the same but with index the value differs.

[lbreddemann · Accepted solution]

Hi again.

would you mind to show us the results of 'explain' and 'explain join'?

Be aware that in the normal 'explain'-output the "costs" that are printed in each line are not the assumed costs of that specific execution step.

Instead what you see there are the statistic information for the total object size (e.g. table or indexsize in pages).

regards,

Lars