Does Postgres Scan Every Row?
Learn how Postgres performs both index and sequential scans. We will also go over the benefits of each and how to use EXPLAIN ANALYZE to see the difference.
Table of Contents 📖
Postgres Row Retrieval
When querying a table in Postgres, Postgres uses different methods to retrieve the rows. Two common methods are sequential scans and index scans.
- Sequential Scan - Postgres reads all rows of a table, one by one.
- Index Scan - Postgres uses an index to locate specific rows based on the query condition.
Of the two methods, sequential scans are better for small tables or queries that retrieve most of the rows. Index scans have a quicker row lookup.
EXPLAIN ANALYZE
A great way to visualize these methods is to use the EXPLAIN ANALYZE Postgres command. The EXPLAIN command shows the execution plan for a query, detailing how the database intends to retrieve the data. Adding ANALYZE executes the query and provides real execution statistics, making it invaluable for performance tuning. Check out the outputs below:
EXPLAIN ANALYZE SELECT * FROM app_user WHERE name = 'Michael Jackson';
Seq Scan on app_user (cost=0.00..26.51 rows=1 width=823) (actual time=0.105..0.105 rows=0 loops=1)
EXPLAIN ANALYZE SELECT * FROM app_user WHERE user_guid = '1feabe03-8745-4a87-b10f-a26e6fd71a66';
Index Scan using app_user_user_guid_key on app_user (cost=0.27..8.29 rows=1 width=823) (actual time=0.073..0.074 rows=1 loops=1)
The reason there is an index scan on the user_guid column is because there is a UNIQUE constraint on the user_guid column. On the other hand, the name column is not unique, so a sequential scan is used. Keep this in mind when designing your database schema.