Re: How is execution plan cost calculated for index scan

Hi Jeff

Thank you very much.

>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).

your method is so smart!

Jian Gao

2012/11/9 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>

> 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
>