What query optimization methods have you used? What do you look at in EXPLAIN ANALYZE and query plan?

When optimizing database queries, I usually use the following methods:

• Indexing: Adding indexes on columns involved in filters, JOINs, and sorting.
• Execution plan analysis (EXPLAIN ANALYZE): Studying what operations the database performs, how long each takes, and how many rows are processed.
• Avoiding full table scans (Seq Scan): Striving to make queries use indexes (Index Scan).
• JOIN optimization: Checking the order of table joins, types of JOINs, and the presence of indexes on keys.
• Reducing data volume: Selecting only necessary fields, using LIMIT, filters.
• Caching and materialized views: For frequently repeated complex queries.

In EXPLAIN ANALYZE, I look at:

• Actual Time: The execution time of each operation.
• Rows: The number of rows processed at each step.
• Cost: The estimated cost of the operation.
• Scan type: Seq Scan, Index Scan, Bitmap Heap Scan, etc.

Based on this data, I identify bottlenecks and make decisions on adding indexes, rewriting queries, or changing data structures.