INF: Understanding Page Estimates

MORE INFORMATION

Cost comparisons are done between the table scan cost and the estimated cost of using the index. The cpages value contributes to this estimated cost, and is affected by the number of levels in the index btree, which in turn depends on the width and type of index, and number of rows and pages in the table.

It also depends on row estimates (crows). Crows is an estimate of the number of rows expected to satisfy the search or join clause. Crows is equal to the total rows in the table if there are no search clauses on the table.

This information can be seen under q_score_join portion of the trace(302) output under the scoring clause for index section.

Entering q_score_join() for table 't1' (varno 0). Scoring clause for index x Estimate: indid x, selectivity y, rows a pages b Cheapest index is index X, costing B pages and generating A rows per scan. Note that the same cpages value (B pages) can be found under the j_optimize section of the trace(310) output under the jplan for the varno and indexid representing the table in q_score_join.

   JPLAN (0x28cf49c) varno=0 indexid=x
   .... cpages=b....
				

Cpages is also used to calculate logical page reads (lp in trace flag 310 output).