EXPLAIN ANALYZE reveals the truth

PostgreSQL's query planner is sophisticated—it considers dozens of strategies for executing your SQL and picks what it thinks is fastest. But the planner only knows what it knows. Outdated statistics, missing indexes, or unusual data distributions can lead it to choose a terrible strategy.

EXPLAIN ANALYZE executes the query and reports what actually happened. Not estimates—actual execution times, actual row counts, actual buffer usage. This is the difference between guessing about performance and knowing.

Each node in a PostgreSQL query plan represents an operation: scanning a table, joining two datasets, sorting results, aggregating groups. The planner nests these operations, and EXPLAIN ANALYZE shows you the nesting depth along with the actual cost at each level.

The "Actual Rows" vs "Plan Rows" mismatch is particularly telling. When the planner thinks a table has 100 rows but it actually has 100,000, its cost calculations are wrong. This often happens after bulk data imports or major table changes before ANALYZE has run.

The --hash flag references a query from a previous perf:query output. This is powerful because you don't need to manually copy-paste SQL—you just reference the problematic query by its identifier. The hash is deterministic based on the normalized SQL pattern, so the same query always has the same hash across sessions.

You can also pass raw SQL with --sql="SELECT ...", but referencing by hash is usually more convenient since you've already identified the slow query through perf:query.

The 51x speedup comes from eliminating full table reads. Without an index, PostgreSQL reads every row, checks if it matches the filter, and keeps the matches. With an index—specifically a trigram (GIN) index for pattern matching—it can jump directly to the matching rows.

The Rows Removed by Filter: 15279 is a dead giveaway. PostgreSQL read 20,000 rows just to find 4,721 matches. That's 75% wasted work. An index turns this into direct lookups.

Note that indexes aren't free—they slow down writes and consume disk space. But for read-heavy tables, especially those with large row counts and selective WHERE clauses, the tradeoff is almost always worth it.

The CREATE INDEX CONCURRENTLY syntax is crucial in production. Regular CREATE INDEX locks the table for writes while building, which can cause downtime on large tables. The CONCURRENTLY option builds the index without locking, though it takes longer and uses more resources.

After creating an index, PostgreSQL won't immediately start using it. You may need to run ANALYZE table_name to update statistics so the planner knows the index exists and is selective.

Multi-tenant setups often use separate databases or schemas per tenant. The --db flag lets you override the database connection at runtime without touching config files or environment variables. This is perfect for investigating performance issues reported by specific tenants.

Because laraperf uses Laravel's connection system, it works with any tenancy approach—separate databases, schema-based, or row-level tenancy with database prefixes. Just specify the connection name and database, and you're analyzing production performance safely.