Posted on
• Artificial Intelligence

Artificial Intelligence SQL Troubleshooting

Author
  • User
    linuxbash
    Posts by this author
    Posts by this author

AI-Assisted SQL Troubleshooting from the Linux Bash Prompt

If you’ve ever stared at a slow query wondering “where is this time going?”, you’re not alone. SQL performance issues can hide behind complex joins, missing indexes, parameter sniffing, or data skew. The good news: you can combine old-school Bash skills with modern AI to triage, explain, and fix many SQL problems faster.

This post shows a practical workflow to:

  • Capture the evidence (slow query + execution plan) from the command line

  • Normalize and anonymize it safely

  • Ask an AI assistant for performance advice

  • Apply and verify changes in a repeatable Bash loop

You’ll leave with plug-and-play snippets, minimal dependencies, and a reusable script.

Why use AI for SQL troubleshooting?

  • Pattern recognition: AI can quickly spot common anti-patterns (sequential scans, Cartesian joins, non-sargable predicates).

  • Knowledge compression: You get distilled best-practice hints (index design, query rewrites, relevant config knobs) without trawling docs.

  • Speed: A tight loop of “collect → analyze → change → verify” means fewer blind alleys and faster relief.

AI doesn’t replace your judgment. It augments it—especially when you include solid context like the schema and an execution plan.

Prerequisites and installation

We’ll use Bash-friendly tools:

  • curl (HTTP requests)

  • jq (JSON handling)

  • sqlite3 or psql (to demo plans; use what you have)

  • An LLM API key (e.g., OpenAI-compatible endpoint)

Install the basics for your distro:

Debian/Ubuntu (apt):

sudo apt update
sudo apt install -y curl jq sqlite3 postgresql-client

Fedora/RHEL/CentOS (dnf):

sudo dnf install -y curl jq sqlite postgresql

openSUSE (zypper):

sudo zypper refresh
sudo zypper install -y curl jq sqlite3 postgresql

Set your LLM API key (example uses OpenAI-compatible API; substitute your own):

export OPENAI_API_KEY="sk-...your-key..."

Note: If your organization forbids sending production data to cloud services, use a vetted endpoint, a self-hosted LLM, or scrub/anonymize aggressively (see below).

The core loop: collect → normalize → ask → apply → verify

1) Capture the problem (query + plan) from the shell

PostgreSQL example (EXPLAIN with timing, buffers, JSON for machine readability):

SQL="SELECT * FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.email = 'user@example.com';"

PLAN_JSON="$(psql -X -A -t -c \
"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) $SQL" mydb 2>/dev/null)"

echo "$PLAN_JSON" | jq '.[0]."Plan" | .Node Type, .Actual Total Time, .Plans?'

SQLite example (text plan; still AI-friendly):

sqlite3 demo.db "EXPLAIN QUERY PLAN
SELECT * FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE c.email = 'user@example.com';"

Tip: For PostgreSQL you can also capture the schema quickly:

SCHEMA="$(pg_dump -s mydb 2>/dev/null | sed -n '1,500p')"

Limit output to what the AI needs (only relevant tables/indexes) to save tokens and protect sensitive info.

2) Normalize and anonymize before sending to AI

Scrub literals and PII to reduce risk and improve generalization:

anonymize_sql() {
  # Replace string literals and long numerics with placeholders
  sed -E "s/'[^']*'/'?'/g; s/[0-9]{2,}/?/g"
}

ANON_SQL="$(printf "%s\n" "$SQL" | anonymize_sql)"

You can also filter schema:

ANON_SCHEMA="$(printf "%s\n" "$SCHEMA" \
  | grep -E '^(CREATE TABLE|CREATE INDEX|ALTER TABLE|-- Table:|-- Name:)' \
  | sed -E 's/[A-Za-z0-9._-]{8,}/T_/g')"

Be intentional: don’t ship row samples, secrets, or business-only identifiers to third parties.

3) Ask an LLM from Bash (structured, reproducible)

Here’s a minimal, reusable Bash helper that uses curl + jq to query an OpenAI-compatible chat endpoint. It instructs the model to give precise, actionable advice.

Create ai_sql_advice.sh:

#!/usr/bin/env bash
set -euo pipefail

: "${OPENAI_API_KEY:?Set OPENAI_API_KEY}"
LLM_ENDPOINT="${LLM_ENDPOINT:-https://api.openai.com/v1/chat/completions}"
LLM_MODEL="${LLM_MODEL:-gpt-4o-mini}"

read -r -d '' SYSTEM <<'SYS'
You are a senior database performance engineer.
Given SQL, schema, and an execution plan, identify the root cause and propose 1–3 concrete fixes.
Prefer: index recommendations (with exact DDL), query rewrites, or config changes.
Output must be concise and include exact commands in code blocks.
SYS

SQL_INPUT="${1:-}"
PLAN_INPUT="${2:-}"
SCHEMA_INPUT="${3:-}"

if [[ -z "$SQL_INPUT" || -z "$PLAN_INPUT" ]]; then
  echo "Usage: $0 '<SQL>' '<PLAN or PLAN_JSON>' ['<SCHEMA_SNIPPET>']" >&2
  exit 1
fi

PAYLOAD="$(jq -n \
  --arg model "$LLM_MODEL" \
  --arg sys "$SYSTEM" \
  --arg sql "$SQL_INPUT" \
  --arg plan "$PLAN_INPUT" \
  --arg schema "${SCHEMA_INPUT:-}" \
  '{
    model: $model,
    temperature: 0.2,
    messages: [
      {role: "system", content: $sys},
      {role: "user",
       content:
         ("SQL:\n" + $sql + "\n\n" +
          "Execution Plan:\n" + $plan + "\n\n" +
          ( ($schema|length) > 0
            ? ("Schema (partial):\n" + $schema + "\n\n")
            : "" ) +
          "Please provide exact DDL/rewrites and a brief rationale.")
      }
    ]
  }'
)"

curl -sS "$LLM_ENDPOINT" \
  -H "Authorization: Bearer $OPENAI_API_KEY" \
  -H "Content-Type: application/json" \
  -d "$PAYLOAD" | jq -r '.choices[0].message.content'

Make it executable:

chmod +x ai_sql_advice.sh

Example usage (PostgreSQL):

ANON_SQL="$(printf "%s" "$SQL" | anonymize_sql)"
./ai_sql_advice.sh "$ANON_SQL" "$PLAN_JSON" "$ANON_SCHEMA"

Example usage (SQLite; pass the text plan):

PLAN_TEXT="$(sqlite3 demo.db "EXPLAIN QUERY PLAN SELECT ...;")"
./ai_sql_advice.sh "$ANON_SQL" "$PLAN_TEXT"

4) Apply and verify: test changes, don’t guess

If the AI suggests an index for PostgreSQL:

-- Suggested:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_customers_email ON customers(email);

Apply in a safe environment:

psql -c "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_customers_email ON customers(email);" mydb

Re-check with EXPLAIN ANALYZE:

psql -X -A -t -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) $SQL" mydb \
 | jq '.[0]."Plan"."Actual Total Time"'

Look for:

  • Lower Actual Total Time

  • Index Scan replacing Seq Scan

  • Reduced shared/local read buffers

Rollback if there’s no gain or if regressions appear for other queries. For SQLite, analogous steps apply:

sqlite3 demo.db "CREATE INDEX IF NOT EXISTS idx_customers_email ON customers(email);"
sqlite3 demo.db "EXPLAIN QUERY PLAN SELECT ...;"

5) Automate the loop (optional)

A small Bash wrapper to run “before/after” automatically for PostgreSQL:

#!/usr/bin/env bash
set -euo pipefail
DB="${DB:-mydb}"
SQL="$1"

before="$(psql -X -A -t -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) $SQL" "$DB")"
echo "[Before] Total time:" \
  "$(printf "%s" "$before" | jq '.[0]."Plan"."Actual Total Time"') ms"

# Example: apply change suggested by AI (edit as needed)
psql -c "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_customers_email ON customers(email);" "$DB"

after="$(psql -X -A -t -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) $SQL" "$DB")"
echo "[After ] Total time:" \
  "$(printf "%s" "$after" | jq '.[0]."Plan"."Actual Total Time"') ms"

Real-world style example (condensed)

Symptom:

  • Query on customers.email is slow; plan shows a Seq Scan on customers.

AI suggestion:

  • Add a B-Tree index on customers(email) and ensure the predicate is sargable (no functions on the column).

  • Optional: rewrite SELECT to avoid SELECT * if not needed.

Action:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_customers_email ON customers(email);

Result:

  • Plan switches from Seq Scan to Index Scan on customers.

  • Latency drops from 420 ms to 8 ms on a medium dataset.

Practical tips

  • Keep prompts tight: include only the relevant part of the schema and the exact plan.

  • Sanitize everything: scrub literals, keys, user data before sending to AI.

  • Validate experimentally: EXPLAIN ANALYZE is your truth. Always measure before and after.

  • Index discipline: prefer targeted, narrow, selective indexes; composite indexes should match common filter/sort order.

  • Watch write impact: new indexes speed reads but slow writes; benchmark accordingly.

Conclusion and next steps

AI can accelerate SQL troubleshooting—especially when wrapped in a Bash-friendly workflow that captures plans, sanitizes inputs, and verifies outcomes. Start small: pick one slow query, run the loop above, and measure the win. Then:

  • Turn ai_sql_advice.sh into a team utility

  • Add guardrails (blacklist tables/columns, redact patterns)

  • Integrate with CI to flag regressions via EXPLAIN ANALYZE snapshots

Questions or want a follow-up with MySQL tips, composite index patterns, or automated schema diffs? Tell me what you’re tuning next and I’ll tailor the next post.

Related posts