PostgreSQL Partial and Covering Indexes in Laravel Migrations and Query Plans

Why Generic Indexes Leave Performance on the Table

Most Laravel applications add indexes reactively — a slow query appears, someone runs Schema::table('orders', fn($t) => $t->index('status')), and the problem is patched. That works until the table grows and the planner decides a sequential scan is cheaper because the index covers too many rows.

Two PostgreSQL features fix this at the schema level: partial indexes (index only the rows you actually query) and covering indexes (embed payload columns so the planner never touches the heap). Neither requires a custom database driver — just raw index definitions in your migrations.

Partial Indexes: Index Only What You Query

A partial index carries a WHERE clause. If your application only ever queries orders where status = 'pending', index only those rows.

// database/migrations/2024_06_01_000001_add_partial_index_to_orders.php
public function up(): void
{
    DB::statement(
        "CREATE INDEX idx_orders_pending_created
         ON orders (created_at DESC)
         WHERE status = 'pending'"
    );
}

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

The index is tiny — it only contains rows where status = 'pending'. The planner will use it when your query includes the same predicate:

// Planner sees the WHERE clause matches the partial index condition
Order::where('status', 'pending')
     ->orderByDesc('created_at')
     ->limit(50)
     ->get();

If you omit ->where('status', 'pending'), PostgreSQL will not use this index. That is intentional — the index is meaningless for other statuses.

Covering Indexes: Eliminate Heap Fetches

A covering index uses INCLUDE to attach non-key columns. The planner can satisfy the entire query from the index leaf pages without a heap fetch (an Index Only Scan).

DB::statement(
    "CREATE INDEX idx_users_email_covering
     ON users (email)
     INCLUDE (id, name, created_at)"
);

Now this query never touches the users heap:

User::select('id', 'name', 'created_at')
    ->where('email', $email)
    ->first();

The INCLUDE columns are not part of the B-tree key, so they add no ordering overhead and do not bloat the index structure as aggressively as adding them to the key would.

Reading EXPLAIN ANALYZE Output

Always verify with EXPLAIN (ANALYZE, BUFFERS):

$sql = User::select('id', 'name', 'created_at')
    ->where('email', 'alice@example.com')
    ->toRawSql();

$plan = DB::select("EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) {$sql}");
foreach ($plan as $row) {
    echo $row->{'QUERY PLAN'} . "\n";
}

Key lines to look for:

• Index Only Scan — the covering index is working; heap fetches are zero.
• Index Scan — the index is used but heap rows are still fetched (add INCLUDE columns).
• Bitmap Heap Scan — common for range queries; acceptable but check Heap Blocks: exact=0 for visibility map wins.
• Seq Scan — the planner chose a full scan; check row estimates and work_mem.

Index Only Scan using idx_users_email_covering on users
  (cost=0.43..8.45 rows=1 width=52)
  (actual time=0.021..0.022 rows=1 loops=1)
  Index Cond: (email = 'alice@example.com')
  Heap Fetches: 0

Heap Fetches: 0 confirms the visibility map is current and no heap I/O occurred.

Combining Both: Partial Covering Index

You can combine both techniques:

DB::statement(
    "CREATE INDEX idx_invoices_unpaid_covering
     ON invoices (due_date ASC)
     INCLUDE (id, customer_id, total_cents)
     WHERE paid_at IS NULL"
);

This index is small (only unpaid invoices), ordered for due-date queries, and carries the columns needed for a dashboard list — all without touching the heap.

Practical Takeaways

• Use partial indexes when a query always filters on a low-cardinality column with a fixed value (status, type, boolean flags).
• Use INCLUDE to turn an Index Scan into an Index Only Scan for read-heavy list queries.
• Combine both for high-traffic, narrow-predicate queries like dashboards or queue polling.
• Always confirm with EXPLAIN (ANALYZE, BUFFERS) — the planner can surprise you.
• Run VACUUM ANALYZE after bulk loads; stale statistics cause the planner to ignore valid indexes.