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 по дате отправления: