┌────────────────────────────────────────────────────────┐
│                    DBbrain Intelligent Diagnosis                    │
├────────────────────────────────────────────────────────┤
│                                                        │
│  ┌──────────┐    ┌──────────────┐    ┌─────────────┐   │
│  │  Slow Query   │    │  AI Diagnostic Engine  │    │  Optimization Suggestions    │   │
│  │  Collection     │ →  │  (Large Model Analysis) │ →  │  Generation        │   │
│  └──────────┘    └──────────────┘    └─────────────┘   │
│       ↑                  ↑                             │
│       │                  │                             │
│  ┌──────────┐    ┌──────────────┐                      │
│  │ pg_stat  │    │ EXPLAIN      │                      │
│  │  Statistics  │    │  Execution Plan     │                      │
│  └──────────┘    └──────────────┘                      │
└────────────────────────────────────────────────────────┘

-- 1) Obtain the execution plan for the slow query
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders
WHERE customer_id = 12345
  AND order_date > '2026-01-01'
ORDER BY order_date DESC;

-- 2) Use AI to analyze the execution plan (In production environments, it is recommended to retrieve the target SQL by queryid)
SELECT tencentdb_ai.chat_completions(
    '<your-llm-model>',
    'You are a PostgreSQL performance optimization expert. Analyze the following execution plan, identify performance bottlenecks, and provide optimization suggestions:' ||
    E'\\n' ||
    (SELECT query FROM pg_stat_statements WHERE queryid = $1)
);

-- Use intelligent index recommendations for slow queries.
SELECT * FROM tencentdb_index_advisor.advise_index(
    'SELECT * FROM orders WHERE customer_id = $1 AND order_date > $2 ORDER BY order_date DESC'
);

-- Batch workload analysis
SELECT * FROM tencentdb_index_advisor.advise_index_workload(
    ARRAY[
        'SELECT * FROM orders WHERE customer_id = $1',
        'SELECT * FROM orders WHERE status = $1 AND created_at > $2',
        'SELECT count(*) FROM orders WHERE product_id = $1'
    ]
);

SELECT * FROM users WHERE email = 'user@example.com';
-- Execution time: 2.5s, Seq Scan on users (rows=1000000)

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Optimized execution time: 0.5ms

ANALYZE orders;
ANALYZE order_items;

SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM discontinued);

-- Rewrite using NOT EXISTS to avoid NULL value issues and improve efficiency.
SELECT p.* FROM products p
WHERE NOT EXISTS (SELECT 1 FROM discontinued d WHERE d.product_id = p.id);