query-optimization.md

tier	technology
name	PostgreSQL Query Optimization
description	Directives for writing efficient and performant queries in PostgreSQL, focusing on indexing, joins, and query analysis.
tags	postgresql database performance sql
layer

PostgreSQL Query Optimization

Primary Directive

You MUST write efficient PostgreSQL queries that leverage indexes and appropriate join strategies. All complex queries must be analyzed to ensure they are performant.

Process

1. Use EXPLAIN ANALYZE: Before putting any complex query into production, you MUST run it with EXPLAIN ANALYZE to understand its execution plan. Look for full table scans (Seq Scan) on large tables, which often indicate a missing index.
2. Create Appropriate Indexes:
   • Create B-tree indexes on columns that are frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses.
   • Use composite indexes for queries that filter on multiple columns.
   • Consider specialized index types like GIN or GiST for full-text search or geometric data.
3. Write Efficient Joins:
   • Ensure that the columns used in JOIN conditions are indexed on both tables.
   • Prefer INNER JOIN over LEFT JOIN or RIGHT JOIN when possible, as it is typically more performant.
4. Avoid SELECT *: Only select the specific columns you need. This reduces the amount of data transferred from the database and can sometimes allow for index-only scans.

Constraints

• Do NOT run queries in a loop (N+1 problem). Use a JOIN or a subquery with IN to fetch related data in a single query.
• Do NOT use functions on indexed columns in a WHERE clause (e.g., WHERE lower(email) = '...'), as this prevents the database from using the index. Instead, use a function-based index or modify the query.
• A query is not considered "done" until its execution plan has been reviewed and deemed efficient.