Reading the Execution Plan
Eloquent makes it easy to fetch data. It's also dangerously simple to ignore what happens in the database. When a request starts crawling, adding more RAM to your database server won't help if the query is doing sequential scans on millions of rows. You need to look at the query plan using the database engine itself. Postgres provides the EXPLAIN command for this exact purpose. You can pipe your Laravel query definitions directly into it.
Before you alter any tables or add indexes, you need to capture the exact SQL and bindings generated by your application. Don't guess what Eloquent is executing. Dump the raw SQL with bindings using the toSql method or listen to the query builder directly in your development environment. Once you have the query string, connect to your database client and prepend EXPLAIN ANALYZE to see the actual execution time and cost metrics.
<?php
namespace App\Http\Controllers;
use App\Models\Order;
use Illuminate\Http\JsonResponse;
class OrderController extends Controller
{
public function index(): JsonResponse
{
$query = Order::query()
->where('status', 'pending')
->where('created_at', '>=', now()->subDays(7))
->orderBy('total', 'desc');
return response()->json([
'sql' => $query->toSql(),
'bindings' => $query->getBindings(),
]);
}
}When you run that generated SQL with EXPLAIN ANALYZE in your Postgres console, you get a breakdown of node types. Look out for Seq Scan nodes. A sequential scan means the database engine reads every single row in the table to find the matching records. If your orders table has five million rows, a sequential scan will read every single one of them from disk, ignoring any cache efficiency. You can read more about data handling patterns in Query Builder vs Eloquent: Real Cost in Laravel to understand how different fetching strategies affect these execution plans.
Database engines build execution plans using statistics gathered by the autovacuum daemon. If your table statistics are stale, the planner will guess wrong and choose a sequential scan even when a proper index exists. You'll need to run ANALYZE orders; in your console if you've just seeded millions of test records without letting Postgres update its internal row counts.
Targeting Missing Indexes
Finding a sequential scan is usually the symptom of a missing index. Developers often slap an index on every foreign key and call it a day. Composite queries need composite indexes arranged in a precise order. If you filter by status and sort by total, a single-column index on status won't prevent a sort operation or a sequential scan on the rest of the criteria.
Postgres evaluates index columns from left to right. If your query filters by status and then evaluates created_at, your index must match that exact cardinality. Putting the equality columns first and the range or sort columns last is the rule you have to follow. Get this wrong, and the query planner will decide that scanning the table is faster than walking an inefficient index tree.
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'pending'
AND created_at >= '2026-01-01'
ORDER BY total DESC;If the plan shows a Bitmap Heap Scan followed by a Bitmap Index Scan, Postgres is using your index to find candidate rows and then fetching them from the heap. That's normally fine. If the cost numbers are high, your index selectivity is poor. When an index contains too many duplicate values, the database engine skips it because random disk I/O for the heap fetches costs more than a sequential scan.
You should also check for index bloat over time. Heavy update traffic causes b-tree indexes to fragment. This forces Postgres to read more pages than necessary. Running reindex operations during maintenance windows keeps your leaf nodes compact and predictable.
Writing Zero-Downtime Index Migrations
Adding indexes on a massive production table in Postgres will lock the table for writes if you aren't careful. You cannot run a standard migration with CREATE INDEX on a table with tens of millions of rows while your application is receiving traffic. It will block all inserts and updates, causing connection timeouts across your PHP workers. Production requires safety.
You must use concurrent index creation. This tells Postgres to build the index without taking an exclusive lock on the table. It takes longer to complete and cannot run inside a standard database transaction, but it keeps your application online while the background build finishes. You also need to handle concurrent operations carefully, similar to the strategies discussed in Zero-Downtime Laravel Migrations in Postgres.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
public bool $withinTransaction = false;
public function up(): void
{
Schema::table('orders', function (Blueprint $table) {
$table->index(['status', 'created_at', 'total'], 'orders_status_created_total_index')->algorithm('concurrently');
});
}
public function down(): void
{
Schema::table('orders', function (Blueprint $table) {
$table->dropIndex('orders_status_created_total_index');
});
}
};Check your migration files to ensure withinTransaction is set to false when building indexes concurrently. Postgres throws an error if a concurrent index creation command runs inside an explicit transaction block. Once the index is active, run your EXPLAIN ANALYZE command again. You should see the plan shift from a sequential scan to an Index Scan or Index Only Scan, and execution time should drop from hundreds of milliseconds down to a fraction of a millisecond.
Don't stop at the first fix. Production workloads change. A query that runs fast today might degrade once your historical table growth hits critical thresholds. Keep an eye on your slow query logs and make query plan analysis part of your regular deployment review checklist.
