Index Scan Vs Sequential scan in Postgres

user_4569713 0
Viewed 6210

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

enter image description here

Then explain analyze for rows between 10 and 500000

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

enter image description here

Then explain analyze for rows between 10 and 600000

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

enter image description here

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 .

indexing query-optimization postgresql sql database
3 Answers

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.

user_64643080

PostgreSQL uses a cost based optimizer, not a rule based optimizer. If you take the estimated cost of the index scan, 18693, and scale it up linearly by the ratio of the expected rows between the two plans (which is not exactly what the planner does, but should be a good enough first approximation) you get 22330. That is higher than the expected cost of the seq scan, 21372, so it chooses the seq scan.

If you scale the index-scan actual time up the same way, you get 89ms, which is slightly faster than the seq scan actually was. So maybe the planner made a very slight error here, but it is certainly nothing to worry about in practice.

If the difference in run times were a factor of 10, rather than 10%, that might be worth investigating further.

user_17212390

its because If the SELECT returns more than approximately 5-10% of all rows in the table, a sequential scan is much faster than an index scan. and your second query hit that threshold; because you are fetching more rows

user_13674540
Related