Partial Indexes, Covering Indexes, and Query Plan Analysis in Laravel

Why Generic Indexes Are Not Enough

Adding ->index() to a migration column is the first instinct when a query is slow. It works — until the table has millions of rows and your query filters on a narrow condition like status = 'pending'. A full B-tree index on status stores every row, including the 98% that are completed. The planner may skip it entirely and fall back to a sequential scan.

Two index types fix this elegantly: partial indexes (index only the rows you care about) and covering indexes (include all columns the query needs so the engine never touches the heap).

Partial Indexes

A partial index carries a WHERE clause. Only rows satisfying that predicate are indexed.

// database/migrations/2024_01_01_000000_add_partial_index_to_jobs_table.php
public function up(): void
{
    DB::statement(
        'CREATE INDEX idx_jobs_pending_created
         ON jobs (created_at)
         WHERE status = \'pending\''
    );
}

public function down(): void
{
    DB::statement('DROP INDEX IF EXISTS idx_jobs_pending_created');
}

Now this Eloquent query hits only the small pending slice:

$jobs = Job::where('status', 'pending')
    ->orderBy('created_at')
    ->limit(50)
    ->get();

The index is smaller, fits in cache more readily, and the planner chooses it confidently because the predicate matches exactly.

When to Use Partial Indexes

• Soft-deleted tables: index only deleted_at IS NULL rows.
• Queue-style tables: index only status IN ('pending', 'processing').
• Feature flags: index only is_active = true users.

Covering Indexes

Even with a good index, PostgreSQL performs a heap fetch for every matched row to retrieve columns not stored in the index. A covering index eliminates that with INCLUDE.

DB::statement(
    'CREATE INDEX idx_orders_user_status_covering
     ON orders (user_id, status)
     INCLUDE (total_cents, created_at)'
);

A query that selects only those four columns now triggers an Index Only Scan — zero heap access:

$summary = Order::where('user_id', $userId)
    ->where('status', 'completed')
    ->select(['user_id', 'status', 'total_cents', 'created_at'])
    ->get();

The INCLUDE columns are not part of the B-tree key, so they add minimal overhead to writes while making reads dramatically cheaper.

Reading EXPLAIN ANALYZE Output

Never guess — verify. Laravel makes it easy to dump the raw plan:

$sql = Order::where('user_id', 1)
    ->where('status', 'completed')
    ->select(['user_id', 'status', 'total_cents', 'created_at'])
    ->toRawSql();

$plan = DB::select('EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) ' . $sql);

foreach ($plan as $row) {
    echo $row->{'QUERY PLAN'} . "\n";
}

Key Lines to Watch

| Term | Good sign | Bad sign | |---|---|---| | Index Only Scan | Covering index hit | — | | Index Scan | Index used, heap fetched | Many heap fetches | | Seq Scan | Small table | Large table | | Heap Fetches | 0 | > 0 on covering index | | actual rows vs rows | Close match | Large divergence = stale stats |

If Heap Fetches is non-zero after adding a covering index, run VACUUM ANALYZE orders; — the visibility map may be stale.

Wiring It Into Your Workflow

Add a DB::listen hook in a local AppServiceProvider to log slow queries during development:

if (app()->isLocal()) {
    DB::listen(function (QueryExecuted $query) {
        if ($query->time > 100) {
            logger()->warning('Slow query', [
                'sql' => $query->sql,
                'ms'  => $query->time,
            ]);
        }
    });
}

Pair this with pg_stat_statements in staging to surface the real worst offenders before they reach production.

Takeaways

• Partial indexes shrink index size by scoping to a predicate — ideal for status columns and soft deletes.
• Covering indexes with INCLUDE enable Index Only Scans, eliminating heap fetches entirely.
• Always verify with EXPLAIN (ANALYZE, BUFFERS) — never assume an index is used.
• Stale visibility maps cause unexpected heap fetches; run VACUUM ANALYZE after bulk writes.
• Keep select() explicit in Eloquent so the planner can match covering indexes reliably.