When creating indexes it's important to understand how they work. In my previous post, I've explained the basics of indexes and the EXPLAIN command. Now we can go a step further and look at the different types of reads a database can perform.
Understanding these access patterns is crucial because it helps us evaluate the trade-offs of creating (or not creating) an indexes. It also gives us insight into what happens under the hood when a query is executed.
At a high level, databases retrieve data in two main ways:
- Scanning the table directly (reading rows sequentially);
- Using an index to locate rows more efficiently;
The query planner decides which strategy to use based on statistics, table size, and how selective the query conditions are.
Since different databases use slightly different terminology, I've included the names used by each database engine for reference.
1. Full table scan (FTS)
Oracle DB: Full table scan
SQL Server: Table Scan
MySQL: Table Scan
PostgreSQL: Sequential scan (Seq Scan)
This is generally the least efficient scan, with O(n) complexity. The database reads the entire table sequentially, checking each row to see if it matches the query conditions.
When is it used?
- For small tables, the query optimizer may decide that a full table scan is actually more efficient than using an index;
- For queries that need most of the rows in the table;
Sequential scans are not always bad. Modern databases can read tables very efficiently using sequential disk access, which can sometimes outperform index lookups when a large percentage of rows must be returned.
Full table scans can lead to high I/O, increased CPU usage, and slower response times, especially on large datasets. Monitoring queries and minimizing unnecessary full table scans is a key part of database performance tuning.
-
2. Index Scan
Oracle DB: Index Range Scan
SQL Server: Index Seek
MySQL: Index Lookup / Range Scan
PostgreSQL: Index Scan
An Index Scan uses an index to locate relevant rows and then fetches the full row from the table (often called the heap in PostgreSQL).
This means the database performs two steps:
- Find matching entries in the index
- Follow pointers to retrieve the actual rows in the table
When is it used:
- Queries filtering on columns with an index;
- When only a small subset of rows matches the criteria;
Because the database must jump between the index and the table, index scans can involve random I/O. For queries that return many rows, this can become slower than a sequential scan.
-
3. Covering Index Scan
Oracle DB: Index Fast Full Scan
SQL Server: Index Seek (covering)
MySQL: Covering Index
PostgreSQL: Index Only Scan
A covering index scan can satisfy the query using only the index, without reading the main table. This avoids additional I/O and can be extremely fast.
In PostgreSQL, this appears as an Index Only Scan, although the database may still access the table if it needs to check row visibility.
When is it used:
- Queries that only request columns included in the index;
- Read-heavy queries where performance is critical;
Indexes require additional storage. They are most effective when queries are frequent and selective, and less useful if the query needs columns that are not part of the index.
4. Bitmap Index Scan
Oracle DB: Bitmap Index Scan
SQL Server: Bitmap Filter
MySQL: Not implemented the same way
PostgreSQL: Bitmap Index Scan & Bitmap Heap Scan
The database first scans one or more indexes and builds a bitmap representing the locations of matching rows. It then reads the table pages in a more optimized order.
When is it used:
- Queries with multiple indexed conditions (e.g., WHERE status='active' AND type='premium');
- Medium to large tables where reading rows individually would be slow;
More memory usage to build the bitmap. Very efficient for combining multiple indexes, but not ideal for very small or highly selective queries.
Choosing the right scan
The query planner decides which scan to use based on several factors:
- Table size
- Index availability
- Selectivity of the query
- Table statistics
Because of this, adding an index does not guarantee it will be used. If the planner estimates that most rows will be returned, it may choose a sequential scan instead.
Understanding these access methods helps interpret EXPLAIN output and design indexes that actually improve performance.
Console You Later!
Top comments (0)