Re: How is execution plan cost calculated for index scan

On Wed, Nov 7, 2012 at 11:17 PM, 高健 <luckyjackgao(at)gmail(dot)com> wrote:
> Hi all:
>
>
>
> I want to see the explain plan for a simple query. My question is : How
> is the cost calculated?
>
>
>
> The cost parameter is:
>
>
>
> random_page_cost = 4
>
> seq_page_cost = 1
>
> cpu_tuple_cost =0.01
>
> cpu_operator_cost =0.0025

The cost is estimates as 2*random_page_cost + cpu_tuple_cost +
cpu_index_tuple_cost + 100* cpu_operator_cost.

I determined this by changing each cost parameter and running explain,
to see how much each one changed the cost estimate (after verifying
the overall plan did not change).

I was surprised the multiplier for cpu_operator_cost was that high.

The two random_page_costs are one for the index leaf page and one for
the table page. Higher pages in the index are assumed to be cached
and thus not charged for IO.

...

> Firstly, database need to search for 9 index pages by sequential to find
> the index entry. For each index page in memory, every “index tuple” need
> to be scanned.

That is not how indexes are traversed.

Cheers,

Jeff