How do you determine that an index is needed for a query? How do you set up queries and what system tables do you use?

To determine if a query needs an index, several signs can be observed:

• Queries with long execution times, especially those involving full table scans.
• Frequent filtering or joining operations on specific columns.
• The execution plan analysis (EXPLAIN PLAN) shows no use of indexes where they could be helpful.

System tables and views are used for query tuning and index analysis, for example:

• In PostgreSQL: pg_stat_user_tables, pg_stat_user_indexes, pg_index, pg_stat_statements.
• In MySQL: information_schema.STATISTICS, performance_schema.
• In Oracle: DBA_INDEXES, V$SQL, V$SQL_PLAN.

The process usually includes:

1. Analyzing slow queries and their execution plans.
2. Identifying columns that are frequently filtered or joined.
3. Creating indexes on these columns.
4. Monitoring the impact of indexes on performance.

It is important to consider that excessive indexes can slow down insert and update operations, so balance and monitoring are critical.