Postgres Indexes for Slow Admin Dashboards

Your admin dashboard felt instant when the database held a few thousand orders. Now there are millions. The orders page takes nine seconds to load, the search box freezes after every keystroke, and the support team has learned to open a page and go make coffee while it loads.

This is one of the most common performance problems in growing web apps, and the good news is that it's usually very fixable. Admin dashboards slow down in a handful of predictable ways, and PostgreSQL has a specific tool for each one.

This guide shows how to find the slow database query in PostgreSQL, how to read its plan with EXPLAIN ANALYZE, and which index or query change fixes each typical dashboard pattern. Every example was tested on a sample table of 500,000 orders in PostgreSQL 16, and the timings below come from those tests.

Why Admin Dashboards Get Slow

Admin screens combine almost every expensive database pattern in one place:

• Lists filtered by status, date, customer or category
• Sorting, usually by newest first
• Pagination that lets people jump to page 4,000
• A total count at the top of the table
• A free-text search box
• Related data pulled in by the ORM for every row

When the app is small, none of this matters. As data grows, the same queries slowly get worse, until one day the dashboard crosses the line from "a bit slow" to "unusable."

Step 1: Find the Queries That Actually Hurt

Don't guess which query is slow. Measure. PostgreSQL's pg_stat_statements extension records statistics for every query your app runs. According to PostgreSQL's pg_stat_statements documentation, you enable it by adding it to shared_preload_libraries in postgresql.conf, restarting the server, then running this in your database:

sql

CREATE EXTENSION pg_stat_statements;

Then list the queries consuming the most total time:

sql

SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Sort by total time, not by the single slowest run. A dashboard query that takes 300 milliseconds but runs 50,000 times a day matters far more than a monthly report that takes ten seconds once.

If you can't install the extension, setting log_min_duration_statement makes PostgreSQL log every query slower than a threshold you choose.

Step 2: Read the Plan With EXPLAIN ANALYZE

Once you know which query is slow, ask PostgreSQL how it runs it:

sql

EXPLAIN (ANALYZE, BUFFERS) SELECT id, status, total, created_at FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 50;

On the 500,000-row test table with no index, the plan showed a Parallel Seq Scan that read the whole table, about 6,000 data pages, then sorted the matching rows to find the newest 50. Execution time was about 35 milliseconds. That doesn't sound terrible, but it grows with every new order, and a real dashboard fires several queries like this per page view.

When reading any plan, look for:

• Seq Scan on a large table: PostgreSQL is reading every row.
• Estimated rows far from actual rows: the planner's statistics are stale or misleading.
• Sorts that spill to disk: usually a sign an index could deliver rows already sorted.
• Nested loops with huge loop counts: often an N+1 problem or a missing join index.

One important warning: EXPLAIN ANALYZE actually runs the query. That's harmless for a SELECT, but for an UPDATE or DELETE it makes real changes. Wrap those in BEGIN; and ROLLBACK;, or test on a replica.

Step 3: Match the Index to the Dashboard Query

Filtered and Sorted Lists: Composite Indexes

For the query above, the fix is a composite index that matches both the filter and the sort:

sql

CREATE INDEX CONCURRENTLY orders_status_created_id_idx  ON orders (status, created_at DESC, id DESC);

After adding it, the same query used an Index Scan, read just 5 pages instead of about 6,000, and finished in about 0.04 milliseconds, roughly a thousand times faster.

Column order matters. Put columns you filter with equality (status = 'paid') first, then the column you sort or range-filter by (created_at). Adding id as a final column gives a unique, stable order, which also makes the pagination technique in Step 4 work cleanly.

Status Filters: Partial Indexes

If a dashboard tab only ever shows a small subset of rows, such as pending orders, a partial index covers just those rows:

sql

CREATE INDEX CONCURRENTLY orders_pending_idx  ON orders (created_at) WHERE status = 'pending';

Partial indexes are smaller, faster to scan and cheaper to maintain than indexing the whole table. In testing, PostgreSQL used this index for the pending tab immediately.

Search Boxes: Trigram Indexes

Admin search boxes usually generate queries like WHERE email ILIKE '%smith%'. A normal B-tree index can't help with a wildcard at the start, so PostgreSQL falls back to scanning every row.

The pg_trgm extension solves this:

sql

CREATE EXTENSION pg_trgm; CREATE INDEX CONCURRENTLY orders_email_trgm_idx  ON orders USING gin (email gin_trgm_ops);

With the trigram index in place, the test search switched from a full scan to a Bitmap Index Scan on the new index. For searching long text, such as notes or descriptions, PostgreSQL's built-in full-text search is often a better fit.

Foreign Keys and Joins

PostgreSQL automatically indexes primary keys, but it does not automatically index foreign key columns. If your dashboard filters or joins orders by customer_id, index it yourself:

sql

CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);

Missing foreign key indexes are one of the most common causes of slow customer detail pages and reports.

List Views That Never Touch the Table

If a list shows only a few columns, PostgreSQL can sometimes answer entirely from the index, called an index-only scan. In testing, the composite index above produced an Index Only Scan for a query that selected just id and created_at. You can also add extra display columns with INCLUDE, but keep it to the few columns the list really shows, because bigger indexes cost more to maintain.

Step 4: Fix the Query Patterns Indexes Can't Save

Deep Pagination

Pagination with OFFSET gets slower the deeper users go, because PostgreSQL still has to read and throw away every skipped row. In testing, page 4,001 of the paid orders list (OFFSET 200000) read 200,050 rows and took about 24 milliseconds, even with a perfect index.

Keyset pagination, also called cursor pagination, avoids this by asking for rows after the last one shown:

sql

SELECT id, created_at FROM orders WHERE status = 'paid'  AND (created_at, id) < ('2026-10-01', 400000) ORDER BY created_at DESC, id DESC LIMIT 50;

The values in the brackets come from the last row of the previous page. With the composite index, this query read only the 50 rows it needed and took about 0.06 milliseconds, no matter how deep the page. The well-known site Use The Index, Luke explains this technique in detail. The trade-off is that users move with "next" and "previous" rather than jumping to an arbitrary page number, which is rarely a loss on admin screens.

Total Counts

SELECT COUNT(*) FROM orders has to look at every row. On the 500,000-row test table it took about 17 milliseconds, and it scales with table size, so on tens of millions of rows it becomes a real bottleneck.

Options include:

• Show an estimate from PostgreSQL's statistics, which is instant:

sql

  SELECT reltuples::bigint FROM pg_class WHERE oid = 'orders'::regclass;

• Cache counts and refresh them periodically.
• Show "50,000+" instead of an exact number once results pass a threshold.

N+1 Queries and SELECT *

ORMs make it easy to load a list of 50 orders and then run 50 more queries to fetch each customer. Use eager loading so related data arrives in one or two queries. Also select only the columns the dashboard displays, because SELECT * pulls extra data and prevents index-only scans.

Step 5: Add Indexes Safely in Production

A normal CREATE INDEX blocks writes to the table while it builds, which on a large, busy table can mean minutes of failed checkouts or stuck jobs. CREATE INDEX CONCURRENTLY builds the index without blocking writes, as described in PostgreSQL's CREATE INDEX documentation. It takes longer and uses more resources, but it keeps the app running.

There's one catch that trips up many developers: CREATE INDEX CONCURRENTLY can't run inside a transaction. In testing, PostgreSQL rejected it with the error "cannot run inside a transaction block." Some frameworks, including Laravel on PostgreSQL, wrap migrations in transactions by default, so a migration that creates a concurrent index needs to opt out of the transaction.

After adding indexes:

• Run ANALYZE on the table, or after any large data load, so the planner has fresh statistics.
• Check for unused indexes with pg_stat_user_indexes. Indexes with idx_scan = 0 over a long period may be candidates for removal, because every index slows down inserts and updates.
• Measure again with pg_stat_statements to confirm the improvement.

Quick Checklist

• Enable pg_stat_statements and sort by total execution time
• Run EXPLAIN (ANALYZE, BUFFERS) on the worst queries
• Add composite indexes that match filters, then sort order
• Use partial indexes for small, frequently filtered subsets
• Add pg_trgm indexes for ILIKE '%term%' search boxes
• Index foreign keys used in joins and filters
• Replace deep OFFSET pagination with keyset pagination
• Avoid exact counts on huge tables, and build indexes concurrently

Frequently Asked Questions

Why is my PostgreSQL query so slow?

The most common causes are missing or mismatched indexes, queries that scan whole tables, deep OFFSET pagination, exact counts on large tables, leading-wildcard searches and N+1 queries from an ORM. Stale planner statistics can also lead PostgreSQL to choose a poor plan. EXPLAIN ANALYZE shows which of these is happening.

How do I find slow queries in PostgreSQL?

Enable the pg_stat_statements extension and sort queries by total execution time to see which ones cost the most overall. You can also set log_min_duration_statement to log any query slower than a chosen threshold. Then run EXPLAIN ANALYZE on the worst offenders.

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN shows the plan PostgreSQL intends to use, with estimated costs and row counts, without running the query. EXPLAIN ANALYZE actually executes the query and reports real timings and row counts. Because it runs the statement, test writes inside a transaction you roll back.

Why isn't PostgreSQL using my index?

Common reasons include an index whose column order doesn't match the query, a function wrapped around the indexed column, a leading wildcard in a LIKE search, stale statistics, or a query that returns a large share of the table, where scanning is genuinely faster. Run ANALYZE and compare the plan before and after.

Do too many indexes slow down PostgreSQL?

Yes, they can. Every index must be updated on each insert, update and delete, and it uses disk space and memory. Add indexes for queries that matter, check pg_stat_user_indexes for ones that are never used, and remove indexes that don't earn their cost.

Wrapping Up

Slow admin dashboards almost always come down to a few patterns. Find the worst queries with pg_stat_statements, read their plans with EXPLAIN ANALYZE, add composite, partial and trigram indexes that match how the dashboard really queries data, switch deep pagination to keyset pagination, avoid exact counts on huge tables and build indexes concurrently. On the test table, those changes turned a 35-millisecond list query into one that ran in well under a millisecond.

If you're still choosing a database for a growing app, this comparison of PostgreSQL vs MySQL for a Laravel SaaS covers the trade-offs. And if you'd like help tuning a slow dashboard or app, Khurram Shahzad offers Laravel and database performance work as part of his custom development services.