sql-optimizer prompt

Version 1.0.0 — registry.pascalai.org

Analyze and optimize SQL queries for performance, correctness and readability.

Variables

Variable	Description
{{database_engine}}	Database system: PostgreSQL, MySQL, SQLite, SQL Server, Oracle
{{table_sizes}}	Approximate row counts for the tables in the query (e.g. "users: 5M, orders: 50M")
{{current_execution_time}}	How long the query currently takes (e.g. "3.2 seconds")
{{optimization_goal}}	Primary goal: reduce latency, reduce I/O, reduce CPU, improve readability

Prompt Template

You are an expert database performance engineer specializing in {{database_engine}}. A query is running on the following tables (approximate sizes): {{table_sizes}} Current execution time: {{current_execution_time}} Primary optimization goal: {{optimization_goal}} Analyze the SQL query provided and produce a comprehensive optimization report covering: 1. **Query Analysis** — Parse the query structure, joins, subqueries, and aggregations 2. **Performance Issues Identified** — Full-table scans, missing indexes, inefficient joins, Cartesian products 3. **Index Recommendations** — Exact CREATE INDEX statements with justification for each 4. **Optimized Query** — Rewritten query with inline comments explaining each change 5. **Schema Suggestions** — Partitioning, materialized views, denormalization if appropriate 6. **Expected Improvement** — Estimated reduction in execution time and resource usage 7. **Database-specific Tips** — Features and hints specific to {{database_engine}} that apply here ...

Install

ppm install sql-optimizer

API Usage

# Raw prompt text
GET https://registry.pascalai.org/v1/packages/sql-optimizer/1.0.0/raw

# Rendered with variables
GET https://registry.pascalai.org/v1/packages/sql-optimizer/1.0.0/render?database_engine=PostgreSQL&table_sizes=users%3A2M+orders%3A20M&current_execution_time=4.5+seconds&optimization_goal=reduce+latency

PascalAI Usage

uses promptlib;

var
  Prompt   := Get('sql-optimizer');
  Rendered := Bind(Prompt, [
    'database_engine',        'PostgreSQL 16',
    'table_sizes',            'users: 2M rows, orders: 20M rows, products: 50K rows',
    'current_execution_time', '4.5 seconds',
    'optimization_goal',      'reduce latency for dashboard queries'
  ]);
  Report := LLM.Complete(Rendered);

Output Sections

1. Query Analysis

Structural breakdown of joins, subqueries, aggregations, and filter predicates.

2. Performance Issues Identified

Full-table scans, missing indexes, implicit type casts, inefficient join order, unnecessary DISTINCT or ORDER BY.

3. Index Recommendations

Exact CREATE INDEX statements with reasoning for each covering index, composite index, or partial index.

4. Optimized Query

Fully rewritten SQL with inline comments explaining every transformation applied.

5. Schema Suggestions

Partitioning strategies, materialized views, denormalization opportunities, or column type changes.

6. Expected Improvement

Estimated reduction in execution time, I/O reads, and memory usage relative to the current query.

7. Database-specific Tips

Engine-specific features such as query hints, planner settings, extensions, or EXPLAIN plan guidance.