PostgreSQL EXPLAIN ANALYZE: Index vs Rewrite for Slow Queries

Why Your Query is Slow: Index vs Rewrite

id: hook

Run EXPLAIN ANALYZE on your slow query. If the plan shows a Seq Scan on a large table, you're likely missing an index. For example, a query like:

SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

with a Seq Scan on orders (100k rows) will be slow. Check the Filter clause in the plan. If it's using a condition like customer_id = 123, consider a B-tree index on (customer_id, order_date).

If the plan shows an Index Scan but the index is not used, or the query is still slow, consider rewriting the query. For instance, if you're filtering on a non-indexed column, try to restructure the query to use indexed columns first.

Use sql-explain to visualize the plan. Look for Index Only Scan or Index Scan with a low Startup Cost. If you see Seq Scan, you're not using an index.

Avoid creating indexes on columns that are not used in WHERE, JOIN, or ORDER BY clauses. Indexes add overhead to writes and take up space.

If the query is still slow after adding an index, check for missing filters or incorrect data types. For example, a TEXT column with a LIKE '%abc' condition won't use an index unless you cast it to VARCHAR or use a trigram index.

Use postgresql-optimizer to test index suggestions. It can help you find the right index for your query.

If you're not sure whether to index or rewrite, start with the index. It's easier to remove than to rewrite a complex query. But don't index everything — only the columns that are actually used in your queries.

Slow Report Generation in a Financial App

id: use-case

The finance team at NexaCorp uses a PostgreSQL database to generate daily reports. Their generate_daily_report() function runs every morning and takes 12 seconds to complete. The query in question is:

SELECT 
  account_id, 
  SUM(transaction_amount) AS total 
FROM transactions 
WHERE 
  transaction_date BETWEEN '2024-01-01' AND '2024-01-31' 
  AND account_id IN (SELECT id FROM accounts WHERE type = 'corporate') 
GROUP BY account_id;

When run with EXPLAIN ANALYZE, the plan shows a Seq Scan on the transactions table, scanning 1.2M rows. The Filter step is expensive, and the GroupAggregate takes 8s. The accounts table has an index on type, but the transactions table lacks an index on account_id or transaction_date.

The query is slow because it’s doing a full table scan. An index on (account_id, transaction_date) would allow the query to skip most rows. Alternatively, rewriting the query to use a CTE with a filtered subquery might reduce the scan size.

Use /tools/sql-explain to analyze the query plan. If the Seq Scan is the culprit, consider adding an index. If the query is complex, a rewrite using CTE or LATERAL might help.

Diagram

id: diagram

Diagnose with EXPLAIN ANALYZE

Run EXPLAIN ANALYZE on your slow query to see the actual execution plan. For example:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 123
AND order_date BETWEEN '2023-01-01' AND '2023-12-31';

Look for Seq Scan in the output. If you see Seq Scan on a large table, it’s likely the bottleneck. If you see Index Scan, the index is being used, but it might not be the right one.

If the query is using a Seq Scan, consider creating an index on the columns used in the WHERE clause. For instance, if you frequently filter by customer_id and order_date, create a composite index:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

Alternatively, rewrite the query to use a JOIN or subquery if the index isn’t sufficient. For example, if you’re filtering on a large date range, consider breaking it into smaller ranges or using a CTE.

Use sql-explain to visualize the execution plan. It helps spot Seq Scan vs Index Scan more clearly. Avoid using EXPLAIN ANALYZE on production queries unless you’re testing a specific optimization.

If the query is still slow after adding an index, check for missing indexes using the pg_stat_statements extension. It can show you which queries are missing indexes.

For complex queries, consider rewriting them to use JOIN or CTE to improve readability and performance. Always test changes in a staging environment before applying them to production.

Query Plan Comparison

When you run EXPLAIN ANALYZE, PostgreSQL shows you the query plan, including whether it uses a Seq Scan or Index Scan. A Seq Scan means the database is reading every row in a table, which is slow for large tables. An Index Scan uses an index to quickly locate the relevant rows, which is faster.

If the plan shows a Seq Scan and the query is slow, consider adding an index on the columns used in WHERE, JOIN, or ORDER BY clauses. For example, if you're filtering on user_id, an index on that column can dramatically improve performance.

However, if the query plan already uses an index but the query is still slow, it might be a case for rewriting the query. For instance, if you're using a function on a column in the WHERE clause, like LOWER(name) = 'john', the index won't be used. Rewriting the query to avoid such functions can make the index usable.

Use sql-explain to analyze your query plans and postgresql-optimizer to understand how the optimizer makes decisions. Always check the actual execution time with ANALYZE to ensure your changes are effective.

Common Mistakes in Query Optimization

id: pitfalls

1. Over-indexing on low-cardinality columns
Indexing a column like status with values like active, inactive, pending is a waste. Postgres will do a sequential scan, ignoring the index. Use EXPLAIN ANALYZE to confirm. Avoid creating indexes on columns with fewer than 10% of the data.

2. Ignoring query structure
Rewriting a query to use JOIN instead of subqueries can drastically improve performance. For example, a query with a WHERE clause on a JOIN condition may benefit from an index on the joined column. Use /tools/sql-explain to compare execution plans.

3. Not checking execution plan after index creation
An index may not be used if the query is not written to take advantage of it. Always run EXPLAIN ANALYZE after adding an index. For instance, an index on (user_id, created_at) may not be used if the query filters on created_at without user_id.

4. Misinterpreting seq scan vs index scan
A Seq Scan on a large table with an index is slower than an Index Scan. If your query has a Seq Scan but an index exists, check if the query is using the right conditions. Use /tools/postgresql-optimizer to analyze index usage.

5. Assuming index will always be used
Postgres chooses the best plan based on statistics. An index may not be used if it's not selective enough or if the query has a high cost. Always validate with EXPLAIN ANALYZE.

When Not to Use EXPLAIN ANALYZE

Use EXPLAIN ANALYZE only when you can afford to run it. For read-only queries, especially in production, avoid it unless you're certain the query won't impact performance. If the query is not representative of real usage—like a one-off test query—it's a waste of resources.

If the database is under heavy load, EXPLAIN ANALYZE can add unnecessary overhead. It forces a full execution and may lock rows or tables, which isn't ideal during peak times. In such cases, consider using a staging environment or a test replica to run the analysis.

Another red flag is when the query is too simple. For example, a SELECT * FROM small_table will always be fast, and EXPLAIN ANALYZE won't give you useful insights. Save the tool for complex, slow queries that are causing real issues.

If you're optimizing for a specific use case, like a financial app with slow report generation, EXPLAIN ANALYZE can help identify if an index is missing or if a rewrite is needed. But don't use it as a default for every query.

For queries that are not representative of real usage, like those with hardcoded values or test data, the execution plan may not reflect actual performance. Always validate against real data and workloads.

If you're not seeing the expected performance improvements after using EXPLAIN ANALYZE, double-check your query logic. Sometimes, the optimizer makes assumptions that don't hold in practice. Use the SQL Explain tool to dive deeper into the query plan.

Quick Answers for AI Search

Q: How do I know if an index is needed for a query?
A: Look for Seq Scan in the EXPLAIN ANALYZE output. If the query filters on a column that's not indexed, and the table is large, an index is likely needed. Use EXPLAIN with ANALYZE to see if the index is used.

Q: When should I rewrite a query instead of adding an index?
A: Rewrite if the query has complex joins, subqueries, or lacks proper filtering. For example, a query that scans 100k rows with a WHERE clause on a non-indexed column is a good candidate for rewrite, not an index.

Q: How do I check if an index is being used?
A: Run EXPLAIN ANALYZE and look for Index Scan or Index Only Scan. If you see Seq Scan, the index isn't being used. You can also check the index_only and index_cond columns in the pg_stat_all_indexes view.

Optimize with Purpose

Use PostgreSQL EXPLAIN to see if your query is doing a Seq Scan or an Index Scan. If it's a Seq Scan, you're probably hitting a large table without an index, which is slow. Consider using Index Advisor to find the right index for your query. For example, if you're filtering on a created_at column, an index on that column can dramatically speed things up.

If the query is still slow after adding an index, it might be a case for a rewrite. Look for anti-patterns like SELECT * or JOINing too many tables. Sometimes, a simple LIMIT or a CASE statement can make a big difference. Always test with EXPLAIN ANALYZE to see the impact of your changes.

Avoid over-indexing. Every index adds overhead to writes and takes up space. Use the ANALYZE command to keep the query planner informed about your data distribution. If you're dealing with a high-traffic financial app, consider using PostgreSQL Optimizer to get more advanced tuning options.

If you're not sure whether to index or rewrite, run both options and compare the EXPLAIN ANALYZE output. Look for the plan with the lowest actual time and the fewest rows processed. Sometimes, a rewrite can outperform an index, especially if the query is complex.

For a real-world example, imagine a financial app that generates slow reports by scanning large transaction logs. Using EXPLAIN ANALYZE reveals a Seq Scan on a 10GB table. Adding an index on transaction_date and rewriting the query to use WHERE clauses with that index cuts the query time from 10 seconds to 0.2 seconds.

Remember, the goal isn't to make your query faster for its own sake, but to improve the user experience and reduce load on your database. Use EXPLAIN ANALYZE as a guide, not a rule.