-- Install the hypopg extension (if it is not already installed).
CREATE EXTENSION IF NOT EXISTS hypopg;

-- Install the index recommendation extension.
CREATE EXTENSION IF NOT EXISTS tencentdb_index_advisor;

SELECT extname, extversion FROM pg_extension 
WHERE extname IN ('hypopg', 'tencentdb_index_advisor')
ORDER BY extname;

tencentdb_index_advise(query_string text)
RETURNS TABLE (
    schema_name text,
    table_name  text,
    am_name     text,
    index_size  bigint,
    index_def   text
)

-- Disable covering index recommendations (to reduce index size).
SET tencentdb_index_advisor.enable_covering_index = off;

-- Limit the maximum number of recommended indexes to 3.
SET tencentdb_index_advisor.max_index_recommend = 3;

-- Skip the mutation optimization phase (to speed up recommendations).
SET tencentdb_index_advisor.try_variation_timeout = 0;

-- Enable workload compression during batch analysis.
SET tencentdb_index_advisor.enable_workload_compression = on;

-- Recommend indexes for slow queries.
SELECT schema_name, table_name, am_name,
       pg_size_pretty(index_size) AS est_size,
       index_def
FROM tencentdb_index_advise(
    'SELECT * FROM orders WHERE customer_id = 123 AND status = ''shipped'' AND order_date > ''2025-01-01'''
);

SELECT schema_name, table_name, am_name,
       pg_size_pretty(index_size) AS est_size,
       index_def
FROM tencentdb_index_advise(
    'SELECT o.id, c.name, o.amount
     FROM orders o JOIN customers c ON o.customer_id = c.id
     WHERE c.city = ''Beijing'' AND o.status = ''delivered'''
);

-- Analyze multiple business SQL statements simultaneously.
SELECT schema_name, table_name,
       pg_size_pretty(index_size) AS est_size,
       index_def
FROM tencentdb_index_advise('
    SELECT * FROM orders WHERE customer_id = 100;
    SELECT * FROM orders WHERE status = ''pending'' AND region = ''east'';
    SELECT * FROM products WHERE category = ''electronics'' AND price < 200;
    SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.city = ''Beijing'';
');

-- Enable covering index recommendations.
SET tencentdb_index_advisor.enable_covering_index = on;
SELECT index_def FROM tencentdb_index_advise(
    'SELECT customer_id, amount FROM orders WHERE status = ''shipped'' AND region = ''east'''
);
-- Result: CREATE INDEX ON public.orders USING btree (region, status, customer_id, amount)

-- Disable covering index recommendations.
SET tencentdb_index_advisor.enable_covering_index = off;
SELECT index_def FROM tencentdb_index_advise(
    'SELECT customer_id, amount FROM orders WHERE status = ''shipped'' AND region = ''east'''
);
-- Result: CREATE INDEX ON public.orders USING btree (region, status)

-- Index Recommendations for UPDATE Statements
SELECT index_def FROM tencentdb_index_advise(
    'UPDATE orders SET amount = 0 WHERE customer_id = 123 AND status = ''cancelled'''
);
-- Result: CREATE INDEX ON public.orders USING btree (customer_id, status)

-- Index Recommendations for DELETE Statements
SELECT index_def FROM tencentdb_index_advise(
    'DELETE FROM orders WHERE customer_id = 999 AND order_date < ''2025-01-01'''
);
-- Result: CREATE INDEX ON public.orders USING btree (customer_id, order_date)

Input the SQL statement.
    │
    ▼
Step 1: Query Parsing
    └── Parse clauses such as WHERE/JOIN/GROUP BY/ORDER BY.
         │
Step 2: Candidate Index Column Generation
    └── Identify candidate columns by priority:
         EQUALITY (equality) > RANGE (range) > JOIN (join)
         > GROUP_ORDER (sorting/grouping) > COVERING (covering)
         │
Step 3: Hypothetical Index Creation
    └── Create a hypothetical index using hypopg (does not occupy disk space).
         │
Step 4: Cost Evaluation
    └── Use the PostgreSQL optimizer to evaluate the execution cost difference with and without an index.
         │
Step 5: Greedy Selection + Mutation Optimization
    └── Use a greedy algorithm to select the index combination with the greatest benefit.
         During the mutation phase, different combinations are attempted to find a better solution.
         │
Step 6: Output Recommendations
    └── Return the CREATE INDEX statement and the estimated index size.

Step 1: Extract the TOP slow queries from pg_stat_statements.
    └── SELECT query FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;
         │
Step 2: Batch Input to Index Recommendation
    └── SELECT * FROM tencentdb_index_advise('slow_query1; slow_query2; ...');
         │
Step 3: Evaluate Recommended Indexes
    ├── Check the estimated index size to assess its impact on disk space.
    ├── Use EXPLAIN to verify changes in the query plan.
    └── Confirm that there are no duplicate/redundant indexes.
         │
Step 4: Create Indexes During Off-Peak Hours
    └── CREATE INDEX CONCURRENTLY ... (Creates the index online without locking the table.)
         │
Step 5: Verify the Results
    └── Compare query execution times and execution plans before and after creation.

-- Step 1: View the current execution plan and cost.
EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped';
-- Result: Seq Scan, cost=1218.0

-- Step 2: Obtain the Recommended Index
SELECT index_def FROM tencentdb_index_advise(
    'SELECT * FROM orders WHERE customer_id = 123 AND status = ''shipped'''
);
-- Result: CREATE INDEX ON public.orders USING btree (customer_id, status)

-- Step 3: Create the index (Use CONCURRENTLY in production environments.)
CREATE INDEX CONCURRENTLY idx_orders_cust_status 
    ON orders USING btree (customer_id, status);

-- Step 4: Verify the Optimization Results
EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped';
-- Result: Index Scan, cost=8.31 (99.3% performance improvement)