How to understand from the request plan that indexes are missing?

To understand from the query plan what indexes are missing, pay attention to the following signs:

• The plan shows full table scans (Table Scan, Full Table Scan, Seq Scan) rather than index scans (Index Scan, Index Seek).
• High cost of operations associated with reading a large number of rows.
• No use of indexes on columns involved in WHERE, JOIN, ORDER BY conditions.

For example, if the query plan shows a full scan of the table for filtering by the user_id column, it likely means there is no index on this column. In such cases, creating an index can speed up the selection.

Example:

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

If the output shows Seq Scan on orders, it means the index is missing or not used.

Creating an index:

CREATE INDEX idx_orders_user_id ON orders(user_id);

After creating the index, the query plan should change, showing the use of an index scan.