PostgreSQL Partial, Covering, and Expression Indexes for Laravel Query Tuning

Beyond Basic Indexes: PostgreSQL's Hidden Performance Levers

Most Laravel developers reach for $table->index(['status', 'created_at']) and call it a day. That works — until your orders table hits 50 million rows and your dashboard query still does a sequential scan. PostgreSQL offers three index types that most teams underuse: partial, covering, and expression indexes. Each solves a distinct problem.

Partial Indexes: Index Only What You Query

A partial index includes only rows matching a WHERE predicate. If 95 % of your jobs table has status = 'processed' and your application only ever queries status = 'pending', a full index wastes space and write overhead on rows you never filter.

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

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

Now this Eloquent query hits the partial index directly:

Job::where('status', 'pending')
   ->orderByDesc('created_at')
   ->limit(100)
   ->get();

Run EXPLAIN ANALYZE to confirm:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 100;

You should see Index Scan using idx_jobs_pending_created with a tiny rows= estimate — not a Seq Scan.

Covering Indexes: Eliminate the Heap Fetch

PostgreSQL's INCLUDE clause lets you attach non-key columns to an index so the planner can satisfy a query entirely from the index page — an Index Only Scan — without touching the heap.

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

This is valuable for API list endpoints that always return the same small column set:

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

Without INCLUDE, PostgreSQL fetches the index entry, then does a second I/O to the heap row. With a covering index the heap fetch disappears entirely. On a busy read replica this difference is measurable.

Note: INCLUDE columns are not part of the B-tree key, so you cannot filter or sort on them via the index — they are payload only.

Expression Indexes: Index the Computation, Not the Column

If your application lowercases emails before lookup, or extracts a JSONB key, index the expression itself:

// Case-insensitive email lookup
DB::statement(
    'CREATE INDEX idx_users_lower_email ON users (lower(email))'
);

// Eloquent query must match the expression exactly
User::whereRaw('lower(email) = ?', [strtolower($input)])->first();

For JSONB columns storing user preferences:

DB::statement(
    "CREATE INDEX idx_profiles_country
     ON profiles ((settings->>'country'))"
);

Profile::whereRaw("settings->>'country' = ?", ['DE'])->get();

PostgreSQL will only use the expression index when the query predicate matches the expression exactly — including function name and argument order. Verify with EXPLAIN.

Combining All Three

You can compose these features. A partial covering expression index is valid:

CREATE INDEX idx_subscriptions_active_plan
ON subscriptions (lower(plan_name) DESC)
INCLUDE (user_id, expires_at)
WHERE cancelled_at IS NULL;

This serves a dashboard query that lists active subscriptions by plan name (case-insensitive) and needs user_id and expires_at without a heap fetch — and skips all cancelled rows entirely.

Key Takeaways

• Partial indexes shrink index size and write cost by excluding rows your queries never touch.
• Covering indexes (INCLUDE) enable Index Only Scans, removing heap I/O for fixed column sets.
• Expression indexes let the planner use an index when your predicate applies a function to a column.
• Always verify with EXPLAIN (ANALYZE, BUFFERS) — the planner may ignore an index if statistics are stale; run ANALYZE table_name after bulk loads.
• Laravel migrations can execute raw DDL via DB::statement(); wrap in Schema::hasIndex() guards for idempotency in CI.
• Indexes cost write overhead — profile before adding, and drop unused ones with pg_stat_user_indexes.