Recommend indexes for WHERE and JOIN clauses

Review a query and suggest indexes with read, write, and storage trade-offs.

freeworks offlinenothing uploaded

ToolIndex Advisor

Input

Output

—

Put this on your own site

The frame below runs the same code as this page, in the reader's own browser. Nothing is sent to us, and nothing is sent to you.

AccentBackground

Pick a dark background and the text and panels follow it, so the frame stays readable on a dark page.

How it works

Filter, join, ordering, and projected columns are compared with candidate index patterns, then trade-offs are listed. The advisor does not inspect live statistics or create indexes.

Advice must balance read speed against write cost.
Recommendations remain conditional on workload and selectivity.

Worked example

Query with JOIN and WHERE

Analyze a query with JOIN and WHERE clauses for index recommendations

Input

											SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.status = 'completed' ORDER BY o.created_at DESC

Output

												Index Advisor Report
==================================================
Queries analyzed: 1
Suggestions: 5

1. [HIGH] orders(status)
   Reason: Column used in WHERE clause with = operator
   SQL: CREATE INDEX idx_orders_status ON orders (status);

2. [HIGH] users(id)
   Reason: Column used in JOIN condition
   SQL: CREATE INDEX idx_users_id ON users (id);

3. [HIGH] orders(user_id)
   Reason: Column used in JOIN condition
   SQL: CREATE INDEX idx_orders_user_id ON orders (user_id);

4. [MEDIUM] orders(created_at)
   Reason: Columns used in ORDER BY clause
   SQL: CREATE INDEX idx_orders_created_at ON orders (created_at);

5. [LOW] orders(status, created_at)
   Reason: Composite index coverin…

When to use this

DBA reviews, migration planning, and performance reports evaluate index options.

Edge cases

A covering index can become expensive to maintain.
A partial index helps only when the query implies its predicate.
Stale statistics can make a useful index unused.

References

PostgreSQL indexes