How is execution plan cost calculated for index scan

Поиск
Список
Период
Сортировка
От 高健
Тема How is execution plan cost calculated for index scan
Дата
Msg-id CAL454F3=9xxD=VreOt_yLySEv1oP0ooMZUe3AZqbfZKrXst9hw@mail.gmail.com
обсуждение исходный текст
Ответы Re: How is execution plan cost calculated for index scan  (Jeff Janes <jeff.janes@gmail.com>)
Re: How is execution plan cost calculated for index scan  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general

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                                   

 

And the table and its index physical  situation are as following:

 

postgres=# select relpages, reltuples  from pg_class where relname = 'pg_proc';                                                                                                                                              

 relpages | reltuples                                                                                                      

----------+-----------                                                                                                         

       62 |      2490                                                                                                                 

postgres=# select relpages, reltuples  from pg_class where relname = 'pg_proc_oid_index';                                                                                                                                      

 relpages | reltuples                                                                                                      

----------+-----------                                                                                                         

        9 |      2490                                                                                                                  

 

The explain plan is:

postgres=# explain SELECT * FROM pg_proc where oid=1;                                                                                          

                                    QUERY PLAN                                                                                                                                 

-----------------------------------------------------------------------------------                                                                                     

 Index Scan using pg_proc_oid_index on pg_proc  (cost=0.00..8.27 rows=1 width=548)                                                                                                                                   

   Index Cond: (oid = 1::oid)                                                                                         

(2 rows)                                                                                                                              

 

I think in the worst situation ,

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.

Then , using the key entry, it need to make a random page read  for the real data into memory, then scan the data tuple is scanned until the reall one is found

(or just directly locate to the data block after read the data page into memory )

 

So at least the evaluated max cost should be bigger  than  9 index pages *   seq_page_cost  , so it should be bigger than  9. Here I haven't added the random page read cost for data.

But what I got is max is 8.27. How is  the result of  8.27 be calculated?  

 

Furthermore, I tried to find the logic in source code, I think it might be  costsize.c  in  src/backend/optimizer/,  by debugging it, I found  that:

 

When I use [ explain SELECT * FROM pg_proc where oid=1;] , I can found that  cost_index function is called.

The result returned for  path->path.total_cost  is    86698968.    And 86698968/1024/1024 = 82.68258 . If devided by 10 , is near 8.27. but this is still a little odd.

 In the above case,    can I say that  the cost formula for index scan is in-- the cost_index function ?


Thanks in advance 

В списке pgsql-general по дате отправления:

Предыдущее
От: Pavel Stehule
Дата:
Сообщение: Re: Does PostgreSQL have complete functional test cases?
Следующее
От: 高健
Дата:
Сообщение: Use order by clause, got index scan involved