Re: Use order by clause, got index scan involved

On Wed, Nov 7, 2012 at 11:41 PM, 高健 <luckyjackgao@gmail.com> wrote:

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)                                                 

You should probably use sample cases much larger than this when trying to understand the planner.  With queries this small, it almost doesn't matter what plan is chosen.

 

                                                                                     

(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 step 2 will repeatedly find the block it is visiting to already be in memory, so it is efficient.

 

 

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

There is a bitmap scan which does that, but such a scan can't be used to fulfill a sort, because it doesn't return the rows in index order.  What reduces the cost here is the various levels of caching implemented by the file system, the memory system, and the CPU.  PG uses "effective_cache_size" to try to account for these effects, although I admit I don't quite understand what exactly it is doing in this case.  I thought that setting effective_cache_size to absurdly low values would make the index scan cost estimate go up a lot, but it only made it go up a little.

 

Cheers,

Jeff