How to analyze with EXPLAIN ANALYZE to examine the index, how to create indexes on multiple fields, and how to understand which fields?

To analyze index effectiveness using EXPLAIN ANALYZE, you need to run a query with this prefix and study the execution plan:

• Pay attention to operations like Index Scan or Bitmap Index Scan — they indicate index usage.
• If you see Seq Scan, it means the index is not used.
• Look at the cost and actual time — they help understand how much the index speeds up the query.

Creating composite indexes on multiple fields is done as follows:

CREATE INDEX idx_name ON table_name (field1, field2);

The order of fields is important: the index is effective for queries filtering by the first field, or by the first few fields in order.

How to decide which fields to index:

• Analyze frequently used fields in WHERE, JOIN, ORDER BY, GROUP BY.
• Use EXPLAIN ANALYZE for typical queries.
• Look at query usage statistics and profiling.

Example:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped';

If the query often filters by customer_id and status, it makes sense to create an index:

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);