What does EXPLAIN do and what changes when adding ANALYZE? When is an index useless?

The EXPLAIN command in SQL shows the execution plan of a query, that is, how the database management system (DBMS) intends to retrieve the data (for example, sequential table scan, index usage, etc.). This helps understand how efficiently the query is written.

Adding ANALYZE to EXPLAIN (EXPLAIN ANALYZE) executes the query itself and outputs the actual time taken for each step and the number of rows processed. This provides more accurate performance information.

An index can be useless if:

• The query selects a large portion of the table (for example, more than 30-40%), then the DBMS might prefer a full table scan.
• The column with the index contains few unique values (low selectivity).
• The query uses functions or operations that cannot utilize the index (for example, LIKE '%abc').
• The index does not cover all the necessary fields for the query, requiring additional table access.

Example:

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

It will display the plan and the actual costs of executing the query.