What is Sequential Scan in PostgreSQL: Explanation and Example
sequential scan is a method where the database reads every row in a table one by one to find matching data. It is the simplest way to search a table and is used when no suitable index exists or when scanning the entire table is efficient.How It Works
A sequential scan in PostgreSQL means the database looks at each row in a table from start to finish. Imagine reading a book page by page to find a specific word instead of using the index at the back. PostgreSQL does this when it thinks scanning all rows is faster or when no index can help.
This method is straightforward but can be slow for very large tables because it checks every row. However, for small tables or queries that need most rows, it can be the best choice. The database decides automatically based on the data and query.
Example
This example shows a sequential scan on a table named employees when searching for employees in a specific department without an index.
EXPLAIN SELECT * FROM employees WHERE department = 'Sales';
When to Use
Sequential scans are useful when the table is small or when you need to read most of the rows anyway. For example, if you want to get all employees or a large portion of them, scanning the whole table is efficient.
Also, if there is no index on the column you are filtering by, PostgreSQL will use a sequential scan. It is a safe fallback method that always works but might be slower on big tables.
Key Points
- A sequential scan reads every row in the table one by one.
- It is used when no index is available or when scanning most rows is faster.
- Good for small tables or queries returning many rows.
- PostgreSQL chooses this automatically based on query and data.