Use order by clause, got index scan involved

Поиск
Список
Период
Сортировка
От 高健
Тема Use order by clause, got index scan involved
Дата
Msg-id CAL454F3xtQm8dKNqwJ8CoNvdpKYVy5KqXMsFmjwkF1Dys-FJ5A@mail.gmail.com
обсуждение исходный текст
Ответы Re: Use order by clause, got index scan involved  (Jeff Janes <jeff.janes@gmail.com>)
Список pgsql-general

Hi all:

 

What confused me is that:  When I select data using order by  clause, I got the following execution plan:

 

postgres=# set session enable_indexscan=true;                                                                                              

SET                                                                                                                                        

postgres=# explain SELECT * FROM pg_proc ORDER BY oid;                                                                                         

                                       QUERY PLAN                                                                                                                              

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

 Index Scan using pg_proc_oid_index on pg_proc  (cost=0.00..321.60 rows=2490 width=552)                                                                                                                                      

(1 row)                                                                                                                                

                                                                                                                                               

postgres=#

 

My Question is :

 If I want to find record using the where clause which hold the id column,  the index scan might be used.

But  I just want to get all the  records on sorted output format,  Why index scan can be used here?

 

I can’t imagine  that:

Step 1 Index is read into memory, then for each tuple in it,

Step 2 Then we got  the address of  related data block, and then access the data block .

 

Step 2 will be repeated for many times. I think it is not efficient.

 

But comparing with sort , I got that  even index scan with all the entry , the cost is still lower than sort operation:

 

postgres=# set session enable_indexscan=false;

SET

postgres=# explain SELECT * FROM pg_proc ORDER BY oid;

                            QUERY PLAN                            

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

 Sort  (cost=843.36..849.59 rows=2490 width=552)

   Sort Key: oid

   ->  Seq Scan on pg_proc  (cost=0.00..86.90 rows=2490 width=552)

(3 rows)

postgres=#

 

That is to say: cost of seq scan + sort   > cost of  index scan for every index entry  + cost of access for every related data ?

 

Maybe the database system is clever enough to  accumulate data access for same physical page, and  reduce the times of physical page acess ?

 

And can somebody kindly give  some more detailed information which help to know the execution plan calculation process?

 

Thanks in advance.

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

Предыдущее
От: 高健
Дата:
Сообщение: How is execution plan cost calculated for index scan
Следующее
От: pantelis vlachos
Дата:
Сообщение: find a substring on a text (data type) column