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 .

Laurenz Albe · Accepted Answer · 2021-03-26 16:28:11Z

33

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.

answered Mar 26, 2021 at 16:28

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yuri Over a year ago

Setting the random_page_cost=1.1 worked for me - the planner switched from a 300+ms sequential scan to a <1ms index scan.

jjanes · Accepted Answer · 2021-03-26 16:51:29Z

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.

eshirvana · Accepted Answer · 2021-03-26 16:24:29Z

1

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

answered Mar 26, 2021 at 16:24

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

1 Comment

Elsayed Over a year ago

Please @eshirvana at first query retrieved approximately 50% not 10% from the rows and explained me index scan

Collectives™ on Stack Overflow

Index Scan Vs Sequential scan in Postgres

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related