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.
