Index Scan Vs Sequential scan in Postgres

Question

I am using Postgres database , I am trying to see the difference between Index Scan and Sequential scan on table of 1000000 rows

Describe table

\d grades 

Then explain analyze for rows between 10 and 500000

explain analyze select name from grades where pid between 10 and 500000 ; 

Then explain analyze for rows between 10 and 600000

explain analyze select name from grades where pid between 10 and 600000 ;

The strange for me why it made Index scan on first query and sequential scan in the second although they query by the same column which it contained in the index .

Answer 1

If you need only a single table row, an index scan is much faster than a sequential scan. If you need the whole table, a sequential scan is faster than an index scan.
Somewhere between that is the turning point where PostgreSQL switches between these two access methods.

You can tune random_page_cost to influence the point where a sequential scan is chosen. If you have SSD storage, you should set the parameter to 1.0 or 1.1 to tell PostgreSQL that index scans are cheaper on your hardware.