KWDB Performance Review

Read the required reference files first.

Tiered Reference Architecture

Tier 1 (Always Read)

• references/key-rules.md - Core engine differences and anti-patterns
• references/query-analysis.md - EXPLAIN output interpretation

Tier 2 (High-Frequency Optimization)

• references/timeseries-optimization.md - Time-series query patterns
• references/pagination-optimization.md - Cursor-based pagination

Tier 3 (Medium-Frequency)

• references/relational-optimization.md - B-tree indexes, join optimization
• references/cross-model-optimization.md - Hybrid query optimization

Tier 4 (Low-Frequency)

• references/schema-tuning.md - Partition interval, TTL, encoding
• references/index-analysis.md - Index review for relational tables
• references/config-optimization.md - Storage configuration parameter optimization

When to Activate

Should trigger:

• "optimize this query" / "优化查询"
• "slow query" / "查询很慢" / "慢查询"
• "explain this query" / "执行计划"
• "query performance" / "查询性能"
• "KWDB query slow"
• "全表扫描" / "查询超时"
• "时序数据查询慢" / "传感器数据"
• "TIME_BUCKET" / "时间聚合"
• "索引优化" (relational tables only)
• "config optimization" / "配置优化"
• "parameter tuning" / "参数调优"
• "存储配置" / "参数调整"

Should NOT trigger:

• Schema design ("create table", "add index") → kwdb-schema-design
• Deployment/configuration questions
• DML write optimization ("fast INSERT")
• Non-KWDB databases

Engine Detection

Before optimizing, determine which engine the query targets:

TIME SERIES TABLE:
  - Has ts_column, primary_tags in CREATE TABLE
  - Cannot have secondary indexes
  - Query must include time range filter
  - Primary tag filter uses hash index

RELATIONAL TABLE:
  - Standard SQL table
  - Can have B-tree, inverted indexes
  - Standard SQL optimization applies

Ask user if unclear.

Workflow

Step 1: Parse EXPLAIN Output

Key indicators to look for:

Step 2: Identify Anti-Patterns

Time-Series Critical Issues:

• Missing time range filter -> full partition scan
• Fuzzy match on primary tag (LIKE, SUBSTRING) -> hash index miss
• SELECT * -> unnecessary column IO
• Large OFFSET pagination -> memory pressure
• Manual GROUP BY instead of TIME_BUCKET

Relational Issues:

• Seq Scan on large table -> missing index
• Nested loop on large sets -> bad join order
• Missing index on join column

Step 3: Provide Optimized Query

Always provide:

1. The anti-pattern being fixed
2. The rewritten query
3. Expected improvement in EXPLAIN

Step 4: Validate

Include EXPLAIN (ANALYZE) to verify the optimization works.

Step 5: Configuration Optimization (Conditional)

Only activate when:

1. User explicitly mentions "config optimization" / "parameter tuning" / "配置优化" / "参数调优", OR
2. SQL optimization steps (1-4) are exhausted and performance issues persist

Per-Parameter Trigger (review on demand, not full scan):

Decision Tree:

1. Compression optimization / disk space reduction → Compression Group
   • Performance priority, disk sufficient → snappy/lz4, level=any, stage=1; extreme: stage=0
   • Disk space priority, CPU sufficient → zstd, level=high, stage=3
   • CPU usage too high → lz4, level=any, stage=1; if still high → stage=0
2. Excessive small blocks / write visibility delay / low compression ratio → Rows Per Block Group
   • High-throughput write → increase max (8192-16384)
   • Memory constrained → decrease max (2048)
   • Point queries → decrease max
   • Sequential scan → increase max
   • Low-latency small batch → increase min (1024+)
3. Write performance → ts.compress.last_segment.enabled (SSD: false, HDD: true)
4. Query performance / high memory → ts.block.lru_cache.max_limit
5. Last query performance / high memory → ts.last_cache_size.max_limit
6. Write performance (after #3) / high memory (after #4,#5) → ts.mem_segment_size.max_limit
7. Frequent compaction / disk tight → ts.reserved_last_segment.max_limit
8. Compaction backlog / high CPU → ts.compact.max_limit
9. Data cleanup → ts.auto_vacuum.enabled
10. Poor filter pushdown → ts.block_filter.sampling_ratio

Pre-conditions (confirm relevant resources before suggesting):

• Memory-related params: confirm available free memory with user
• Disk-related params: confirm available disk space with user
• CPU-related params: confirm CPU availability with user

Read references/config-optimization.md for detailed parameter guidance. See assets/example-configs.md for configuration tuning examples.

Config query approach:

• Use MCP tool if available: mcp__kwdb__read-query("SHOW CLUSTER SETTING ts.xxx")
• Otherwise, ask user to run: SHOW CLUSTER SETTING ts.xxx;
• NEVER use SHOW CLUSTER SETTINGS

Important:

• NEVER execute SET CLUSTER SETTING automatically
• Only provide SQL statements for user to review and execute

NOT for

• DDL operations: Creating/dropping tables, indexes (→ kwdb-schema-design)
• Deployment/Config: Memory settings, installation, replication
• Write optimization: Bulk INSERT, import performance
• Data migration: Moving data between databases
• Hardware sizing: Server specs, disk I/O recommendations
• Application tuning: Connection pooling, caching (beyond SQL)

Guardrails

1. Never suggest CREATE INDEX on time-series tables - they don't support secondary indexes
2. Time-series queries MUST have time range filters - warn if missing
3. Never recommend OFFSET for deep pagination - use time-based cursor
4. Always specify SELECT columns for time-series - no SELECT *
5. Verify table type before index recommendations
6. Explain WHY the optimization works - not just what to change
7. Validate with EXPLAIN before finishing
8. Confirm memory and disk before config changes - never suggest cache/memory size increases without confirmed free resources
9. Never auto-execute SET CLUSTER SETTING - only provide SQL for user to review
10. Never use SHOW CLUSTER SETTINGS - always query specific settings individually
11. Resource overload reduce warning - when recommending reduced values for memory/CPU-impact parameters, always include the risk warning about potential performance degradation

Output Format

## Intent
[Brief description of the optimization goal]

## Engine Type
[time-series / relational / mixed]

## Anti-Pattern Detected
[What was causing the slowness]

## Original Query
```sql
[query before optimization]

Optimized Query

[rewritten query]

Expected Improvement

[What should change in EXPLAIN]

Validation

EXPLAIN (ANALYZE) [optimized query];

For configuration optimization output format, see `assets/config-output-template.md`.