Sequential Scan Detection | PG Pilot PostgreSQL Monitoring

What is this issue?

PostgreSQL can scan tables in two main ways:
- Sequential scan: Read every row from beginning to end
- Index scan: Use an index to find specific rows quickly

A high sequential scan ratio (>90%) on a large table suggests queries are
reading more data than necessary. This can indicate:
- Missing indexes for common query patterns
- Queries not using WHERE clauses effectively
- Statistics that cause the planner to avoid indexes

Note: Small tables are excluded (< 10,000 rows) as sequential scans
are often faster for them.

Why it matters

Slow Queries

Sequential scans read entire tables, taking O(n) time

I/O Pressure

Reading unnecessary data wastes disk and memory bandwidth

Cache Pollution

Unneeded rows evict useful data from shared buffers

Resource Waste

CPU time spent on rows that don't match the query

How PG Pilot detects it

```sql
SELECT
  schemaname,
  relname,
  seq_scan,
  idx_scan,
  round(100.0 * seq_scan / NULLIF(seq_scan + idx_scan, 0), 2) AS seq_scan_pct,
  n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 100
  AND n_live_tup > 10000
  AND seq_scan::float / NULLIF(seq_scan + idx_scan, 0) > 0.9
ORDER BY seq_scan DESC;
```

How to fix it

1

Identify the queries

Find queries that scan this table:

SELECT query, calls, total_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%your_table%'
ORDER BY total_exec_time DESC;

2

Analyze query patterns

Run EXPLAIN on the slow queries:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM your_table WHERE column = 'value';

Look for "Seq Scan" in the output.

3

Create targeted indexes

Add indexes for commonly filtered columns:

CREATE INDEX CONCURRENTLY idx_table_column
  ON your_table (column);

Consider partial indexes for selective conditions:

CREATE INDEX CONCURRENTLY idx_table_active
  ON your_table (status) WHERE status = 'active';

4

Update statistics

Ensure the planner has accurate statistics:

ANALYZE your_table;

Prevention

Review query patterns before deployment
Add indexes for common filter conditions
Monitor slow queries with pg_stat_statements
Regularly review sequential scan ratios

Related Issues

Unused Index Unindexed Foreign Key

Related Features

Table Monitoring Index Health

PostgreSQL Documentation

PostgreSQL Indexes