Sequential scan vs index scan in PostgreSQL - Quick Revision & Key Differences

Recall & Review

beginner

What is a sequential scan in PostgreSQL?

A sequential scan reads every row in a table one by one to find matching rows. It is simple but can be slow for large tables.

beginner

What is an index scan in PostgreSQL?

An index scan uses an index to quickly find rows that match a condition, without reading the whole table. It is faster for selective queries.

intermediate

When does PostgreSQL prefer a sequential scan over an index scan?

PostgreSQL prefers a sequential scan when a large portion of the table needs to be read, because reading the whole table can be faster than using the index.

beginner

How does an index scan improve query performance?

An index scan improves performance by quickly locating only the rows that match the query condition, reducing the number of rows read from the table.

intermediate

What is a downside of using an index scan?

Index scans can be slower if the query matches many rows because it may require many random reads, which are slower than reading sequentially.

What does a sequential scan do in PostgreSQL?

A Uses an index to find rows quickly

B Reads every row in the table one by one

C Only reads the first 10 rows

D Reads rows randomly

A sequential scan reads every row in the table one by one to find matching rows.

When is an index scan usually faster than a sequential scan?

A When the query matches very few rows

B When the table has no indexes

C When the table is empty

D When the query matches many rows

Index scans are faster when the query matches very few rows because it can quickly locate them using the index.

Why might PostgreSQL choose a sequential scan over an index scan?

A Because the query is very simple

B Because the table is very small

C Because the index is corrupted

D Because the query matches a large portion of the table

PostgreSQL chooses a sequential scan when the query matches a large portion of the table, making it faster to read all rows sequentially.

What is a disadvantage of index scans?

A They can be slower if many rows match the query

B They always read the whole table

C They do not use indexes

D They only work on small tables

Index scans can be slower if many rows match because they may cause many random disk reads.

Which scan type reads rows in the order they are stored on disk?

A Index scan

B Bitmap scan

C Sequential scan

D Hash scan

Sequential scans read rows in the order they are stored on disk, one after another.

Explain the difference between a sequential scan and an index scan in PostgreSQL.

Think about how each method reads data and when each is faster.

Describe when PostgreSQL might choose a sequential scan instead of an index scan.

Consider the cost of reading many rows randomly vs sequentially.