> ## Documentation Index
> Fetch the complete documentation index at: https://docs.pawsql.com/llms.txt
> Use this file to discover all available pages before exploring further.
> ## Agent Instructions
> PawSQL 是一个产品：Cloud 是公网部署形态，Engine / Optimizer / Auditor / Advisor / Patroller 是同一产品的组件与交付形态，不是彼此独立的产品。 / PawSQL is a single product: Cloud is the public deployment form, while Engine / Optimizer / Auditor / Advisor / Patroller are components and delivery forms of the same product, not separate products.
> 术语以站内术语表为准：SQL 审核对应英文 SQL Review，查询重写对应 Query Rewrite，索引推荐对应 Index Recommendation；英文内容统一用 Review，不用 Audit。 / Use the site glossary for terminology: 审核 is SQL Review, 重写 is Query Rewrite, 索引推荐 is Index Recommendation; English content uses Review, never Audit.
> 引用能力范围或版本支持时以对应页面为准；标注 unknown、或 status 非 published 的内容表示尚未经产品核实，不应作为事实引用。 / Cite capability scope and version support from the corresponding page; content marked unknown, or with a status other than published, is not yet product-verified and must not be cited as fact.
# Index Recommendation
> Generate and validate effective index strategies using SQL predicates, joins, sorting, grouping, selectivity, and existing indexes.
PawSQL Index Recommendation identifies index optimization opportunities and generates candidate indexes that better match SQL access paths.
The recommendation process goes beyond WHERE clauses. It also analyzes joins, ORDER BY, GROUP BY, column selectivity, existing indexes, and target-database execution plans.
## Overview
“Create an index on every WHERE column” is not a reliable indexing strategy.
An effective index usually depends on several factors:
* Which predicates provide meaningful filtering
* Which columns are used for joins
* Which columns should become leading columns in a composite index
* Whether the index can support sorting or grouping
* Whether equivalent or overlapping indexes already exist
* Whether a new index actually changes the execution plan
PawSQL separates index recommendation into candidate generation, filtering, simulation, and validation.
```mermaid theme={null}
flowchart LR
A["SQL"] --> B["Predicate / Join Analysis"] --> C["Index Candidates"] --> D["Existing Index Check"] --> E["Plan Validation"] --> F["Recommended Index"]
```
## Key Capabilities
### Predicate-driven candidate generation
Analyze equality, range, IN, LIKE, and other predicates to identify columns that may benefit from indexing.
### Join-aware indexing
Analyze join conditions to identify indexing opportunities on join keys.
For frequently joined business tables, this may be more important than analyzing WHERE predicates alone.
### Composite index design
Evaluate ordering across multiple index columns using:
* Selectivity
* Equality and range predicates
* Join conditions
* ORDER BY
* GROUP BY
to generate more practical composite-index candidates.
### Existing index analysis
Before recommending a new index, PawSQL evaluates existing indexes to avoid:
* Duplicate indexes
* Prefix-duplicate indexes
* Highly overlapping indexes
* Unnecessary index proliferation
### Database-aware index syntax
Index capabilities vary significantly by database.
PawSQL can account for database-specific characteristics such as:
* B-tree / bitmap and other index types
* INCLUDE / covering indexes
* Local / global indexes
* Concurrent or online index creation
* Partitioned-table indexes
* Distributed-database index restrictions
### Index effectiveness validation
The final goal is not to generate a `CREATE INDEX` statement. It is to determine:
Will this index actually change the access path and reduce SQL cost?
## Example
Original SQL:
```sql theme={null}
SELECT order_id, order_date, amount
FROM orders
WHERE customer_id = ?
AND order_date >= ?
ORDER BY order_date DESC;
```
Possible index candidate:
```sql theme={null}
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
```
The candidate may be useful because:
* `customer_id` is used for equality filtering
* `order_date` is used for a range predicate
* `order_date` also participates in sorting
The final recommendation still depends on existing indexes, data distribution, and execution-plan validation.
## Recommend → Simulate → Validate
PawSQL's indexing workflow can be summarized as:
```mermaid theme={null}
flowchart LR
A["Recommend"] --> B["Simulate"] --> C["Compare Plan"] --> D["Validate"]
```
If the target database supports virtual indexes, hypothetical indexes, or equivalent mechanisms, recommendations can be validated without immediately creating physical indexes.
## What PawSQL Tries to Avoid
A good index recommendation system must understand not only which indexes to add, but also which indexes should not be added.
Examples include:
* Mechanically indexing low-selectivity columns
* Creating many similar indexes for individual SQL statements
* Ignoring existing composite indexes
* Optimizing reads while ignoring write overhead
* Recommending indexes that do not change the execution plan
## Related Capabilities
## Related Use Cases