mcp_postgres/actions/
index_advisor.rs

1use serde_json::{json, Value};
2use tokio_postgres::Client;
3use crate::errors::Result as MCPResult;
4
5pub async fn suggest_indexes(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
6    let schema = params.as_ref().and_then(|p| p.get("schema").and_then(|v| v.as_str()));
7    let min_scan_threshold = params.as_ref().and_then(|p| p.get("min_scan_threshold").and_then(|v| v.as_i64())).unwrap_or(100);
8    let min_table_size_mb = params.as_ref().and_then(|p| p.get("min_table_size_mb").and_then(|v| v.as_i64())).unwrap_or(10);
9
10    let schema_filter = match schema {
11        Some(s) => format!("AND n.nspname = '{}'", s.replace('\'', "''")),
12        None => "AND n.nspname NOT IN ('pg_catalog', 'information_schema')".to_string(),
13    };
14
15    let rows = client.query(
16        &format!(
17            "SELECT s.schemaname, s.tablename, s.seq_scan, s.seq_tup_read,
18                    s.n_live_tup,
19                    pg_total_relation_size(quote_ident(s.schemaname)||'.'||quote_ident(s.tablename)) AS total_size,
20                    COALESCE(
21                        (SELECT string_agg(a.attname::text, ',' ORDER BY a.attnum)
22                         FROM pg_catalog.pg_attribute a
23                         JOIN pg_catalog.pg_class c ON c.oid = a.attrelid
24                         JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
25                         WHERE n.nspname = s.schemaname AND c.relname = s.tablename
26                           AND a.attnum > 0 AND NOT a.attisdropped
27                        ), ''
28                    ) AS columns,
29                    (SELECT COUNT(*) FROM pg_catalog.pg_index i
30                     JOIN pg_catalog.pg_class c ON c.oid = i.indrelid
31                     JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
32                     WHERE n.nspname = s.schemaname AND c.relname = s.tablename
33                    ) AS index_count
34             FROM pg_stat_user_tables s
35             WHERE s.seq_scan > $1
36               AND pg_total_relation_size(quote_ident(s.schemaname)||'.'||quote_ident(s.tablename)) > $2 * 1048576
37               {}
38             ORDER BY s.seq_tup_read DESC
39             LIMIT 20",
40            schema_filter,
41        ),
42        &[&min_scan_threshold, &min_table_size_mb],
43    ).await?;
44
45    let mut suggestions = Vec::with_capacity(rows.len());
46    for row in &rows {
47        let schemaname: String = row.get(0);
48        let tablename: String = row.get(1);
49        let seq_scan: i64 = row.get(2);
50        let seq_tup_read: i64 = row.get(3);
51        let n_live_tup: Option<i64> = row.get(4);
52        let total_size: i64 = row.get(5);
53        let columns_str: String = row.get::<_, Option<String>>(6).unwrap_or_default();
54        let existing_count: i64 = row.get::<_, i64>(7);
55
56        let columns: Vec<&str> = if columns_str.is_empty() {
57            vec!["(unknown)"]
58        } else {
59            columns_str.split(',').collect()
60        };
61
62        let rationale = if existing_count == 0 {
63            format!("No indexes exist ({} seq scans)", seq_scan)
64        } else {
65            format!("High seq scan count ({} scans, {} rows read) on a {:.1} MB table with {} existing index(es)",
66                seq_scan, seq_tup_read, total_size as f64 / 1048576.0, existing_count)
67        };
68
69        suggestions.push(json!({
70            "schema": schemaname,
71            "table": tablename,
72            "suggested_columns": columns,
73            "suggested_ddl": format!(
74                "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_{}_{}_on_{} ON \"{}\".\"{}\" ({});",
75                schemaname, tablename, columns[0], schemaname, tablename, columns.join(", "),
76            ),
77            "rationale": rationale,
78            "seq_scans": seq_scan,
79            "seq_tup_read": seq_tup_read,
80            "table_size_bytes": total_size,
81            "existing_indexes": existing_count,
82            "estimated_live_rows": n_live_tup,
83        }));
84    }
85
86    Ok(json!({
87        "suggestions": suggestions,
88        "total_suggestions": suggestions.len(),
89        "note": "Review each suggestion before applying. Index creation affects write performance.",
90    }))
91}
mcp_postgres/actions/index_advisor.rs

mcp_postgres/actions/
index_advisor.rs
