Laravel Database Index Tuning with Postgres EXPLAIN

The Cost of Default Eloquent Queries

Eloquent makes database interactions fast to write, but it hides how Postgres executes them under the hood. When your orders table has 20,000 rows, a simple query runs in 3ms. At 10 million rows, that same query takes 1.4 seconds and burns CPU cycles reading disk pages.

Consider a multi-tenant ecommerce application running on PHP 8.3 and Laravel 12. You write this Eloquent query to render a dashboard order list:

$orders = Order::where("tenant_id", $tenantId)
    ->where("status", "shipped")
    ->orderBy("created_at", "desc")
    ->take(20)
    ->get(["id", "tenant_id", "status", "total_cents", "created_at"]);

Without a targeted index, Postgres has two bad options. It can do a sequential scan over the entire table, filtering out mismatched rows. Or it can pick a single-column index on tenant_id, collect 500,000 matching row pointers from disk, pull the table blocks into memory, filter by status, and sort the remaining rows in memory using QuickSort. Both options drop your endpoint throughput off a cliff.

Composite Indexes and Column Ordering

A single-column index on tenant_id isn't enough when you filter on multiple fields and sort by another. You need a composite index. Column order inside a composite B-tree index determines whether Postgres can actually use it efficiently.

The standard rule for composite B-tree index layout is simple: put equality columns first, followed by range filters, and end with order-by columns.

For our order query, the exact column order must be (tenant_id, status, created_at). Here is how you define this migration in Laravel 12:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table("orders", function (Blueprint $table) {
            // Equality columns first, then sorting columns
            $table->index(["tenant_id", "status", "created_at"], "idx_orders_tenant_status_created");
        });
    }

    public function down(): void
    {
        Schema::table("orders", function (Blueprint $table) {
            $table->dropIndex("idx_orders_tenant_status_created");
        });
    }
};

If you reorder those columns to (created_at, status, tenant_id), Postgres cannot jump straight to the matching tenant_id. It has to walk the index tree sorted by timestamp, checking every entry to see if it matches the tenant. That degrades execution speed from O(log N) down toward O(N).

Reading Postgres EXPLAIN (ANALYZE, BUFFERS)

Never guess whether Postgres is using your index. Run EXPLAIN (ANALYZE, BUFFERS) directly in your database client or wrap it in an Artisan command during testing. Standard EXPLAIN gives an estimated query plan based on planner statistics. Adding ANALYZE executes the query and reports actual runtime numbers. Adding BUFFERS reveals how many 8kB memory pages Postgres read from shared memory versus disk.

Here is an actual execution plan from an unoptimized query running on Postgres 16:

Bitmap Heap Scan on orders  (cost=1240.12..48200.50 rows=1420 width=42) (actual time=42.102..185.430 rows=20 loops=1)
  Recheck Cond: ((tenant_id = '018f3a2b-8a12-7000-8000-111122223333'::uuid) AND (status = 'shipped'::text))
  Buffers: shared hit=1042 read=312
  ->  Bitmap Index Scan on orders_tenant_id_index  (cost=0.00..1239.77 rows=48120 width=0) (actual time=12.410..12.411 rows=48200 loops=1)
        Index Cond: (tenant_id = '018f3a2b-8a12-7000-8000-111122223333'::uuid)
Planning Time: 0.281 ms
Execution Time: 185.510 ms

Notice shared read=312. Postgres had to fetch 312 blocks (2.5 MB) directly from disk into memory because the single-column index didn't filter out non-shipped status values. The bitmap scan fetched 48,200 row pointers, only to discard 48,180 of them during the heap lookup stage.

When we add our multi-column index (tenant_id, status, created_at), the plan transforms:

Index Scan Backward using idx_orders_tenant_status_created on orders  (cost=0.42..12.85 rows=20 width=42) (actual time=0.045..0.082 rows=20 loops=1)
  Index Cond: ((tenant_id = '018f3a2b-8a12-7000-8000-111122223333'::uuid) AND (status = 'shipped'::text))
  Buffers: shared hit=4
Planning Time: 0.115 ms
Execution Time: 0.098 ms

Execution time dropped from 185ms to 0.098ms. The database engine read only 4 blocks from memory (shared hit=4) and zero from storage.

Covering Indexes with the INCLUDE Clause

Even with an Index Scan, Postgres must visit the table's main storage heap to retrieve columns that aren't stored in the index structure, such as total_cents. Fetching heap pages adds I/O overhead. If Postgres can answer the entire query from the index tree alone, it performs an Index Only Scan, skipping heap lookups entirely.

Postgres supports covering indexes using the INCLUDE clause. Key columns dictate the B-tree structure and ordering, while payload columns are appended directly to the leaf nodes without affecting B-tree sorting rules.

In Laravel 12, you can create a covering index using raw SQL inside a migration:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        // Raw DDL for Postgres INCLUDE support
        DB::statement('
            CREATE INDEX idx_orders_covering_dashboard 
            ON orders (tenant_id, status, created_at DESC) 
            INCLUDE (total_cents);
        ');
    }

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

With this covering index in place, running EXPLAIN (ANALYZE, BUFFERS) reveals maximum query efficiency:

Index Only Scan using idx_orders_covering_dashboard on orders  (cost=0.42..4.85 rows=20 width=42) (actual time=0.021..0.038 rows=20 loops=1)
  Index Cond: ((tenant_id = '018f3a2b-8a12-7000-8000-111122223333'::uuid) AND (status = 'shipped'::text))
  Heap Fetches: 0
  Buffers: shared hit=3
Planning Time: 0.089 ms
Execution Time: 0.051 ms

Pay attention to Heap Fetches: 0. Zero heap fetches means Postgres answered the select query without reading the main table once.

Gotchas, Write Amplification, and Maintenance

Indexes aren't free. Every time an application executes an INSERT, UPDATE, or DELETE statement on the orders table, Postgres must update every corresponding index. If your table has 12 separate single-column indexes, every insert turns into 13 write operations.

A frequent mistake in Laravel apps is indexing foreign keys individually while also adding composite indexes that start with those same foreign keys. If you have an index on (tenant_id) and another on (tenant_id, status, created_at), the single-column index is completely redundant. Drop it to save disk space and write overhead.

Another critical detail with Postgres Index Only Scans is the Visibility Map. Postgres tracks whether all tuples on a heap page are visible to all active transactions. If a page has unvacuumed modifications, an Index Only Scan must fall back to checking the heap page, increasing Heap Fetches. If you notice your Index Only Scans slowly degrading over time, verify that your autovacuum daemon is running aggressively enough on high-write tables.