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.
