Fähigkeiten sql-optimization-patterns
📦

sql-optimization-patterns

Sicher

Optimize SQL queries and database performance

Auch verfügbar von: wshobson

Slow database queries frustrate users and increase infrastructure costs. This skill provides systematic patterns to analyze query plans, design effective indexes, and transform sluggish SQL into high-performance operations.

Unterstützt: Claude Codex Code(CC)
🥉 74 Bronze
1

Die Skill-ZIP herunterladen

2

In Claude hochladen

Gehe zu Einstellungen → Fähigkeiten → Skills → Skill hochladen

3

Einschalten und loslegen

Teste es

Verwendung von "sql-optimization-patterns". Query with sequential scans on a 10M row table

Erwartetes Ergebnis:

Analysis: The EXPLAIN shows a Sequential Scan because no index exists on the 'created_at' column used in the WHERE clause. Recommendation: CREATE INDEX idx_orders_created ON orders(created_at DESC). Expected improvement: Query time should drop from 2.3s to under 50ms for recent date ranges.

Verwendung von "sql-optimization-patterns". N+1 pattern loading user orders individually

Erwartetes Ergebnis:

Problem: 101 queries executed (1 for users + 100 for orders). Solution: Replace with a single JOIN query or use batch loading with WHERE user_id IN (...). The refactored approach reduces database round-trips by 99% and total execution time from 5.2s to 120ms.

Sicherheitsaudit

Sicher
v1 • 2/25/2026

Static analysis flagged 103 potential issues that are all false positives. The 'external_commands' findings are markdown code fence backticks in documentation, not shell execution. The 'weak cryptographic algorithm' findings reference SQL examples, not actual crypto code. The 'filesystem' finding is a PostgreSQL COPY command syntax example in documentation. This skill contains only markdown documentation with SQL optimization patterns and examples - no executable code or security risks.

2
Gescannte Dateien
543
Analysierte Zeilen
0
befunde
1
Gesamtzahl Audits
Keine Sicherheitsprobleme gefunden
Auditiert von: claude

Qualitätsbewertung

38
Architektur
100
Wartbarkeit
87
Inhalt
50
Community
100
Sicherheit
91
Spezifikationskonformität

Was du bauen kannst

E-commerce Platform Optimization

A development team struggles with slow product search and order history queries during peak traffic. The skill helps them analyze slow query logs, identify missing indexes on frequently filtered columns, and refactor N+1 patterns in order loading to reduce API response times from seconds to milliseconds.

Analytics Dashboard Performance

A data engineer needs to speed up aggregation queries for real-time dashboards displaying sales metrics. The skill guides implementation of materialized views for expensive calculations, covering indexes for index-only scans, and efficient GROUP BY patterns to achieve sub-second dashboard refresh rates.

Legacy Application Modernization

A senior developer inherits an application with dozens of slow queries causing timeout errors. The skill provides a systematic approach: capture slow queries with pg_stat_statements, analyze execution plans, prioritize high-impact optimizations, and implement proper indexing to eliminate 90% of performance issues without application rewrites.

Probiere diese Prompts

Basic Query Analysis 
I have a slow SQL query that takes over 5 seconds to run. Here is the query and table structure: [paste query and schema]. Can you analyze what might be causing the slowness and suggest specific indexes to create?
EXPLAIN Plan Interpretation 
I ran EXPLAIN ANALYZE on my query and got this output: [paste EXPLAIN output]. Please explain what each operation means, identify the performance bottlenecks, and recommend specific optimizations.
Index Strategy Design 
I have a table with columns [list columns] and my most common queries are: [list queries]. What indexing strategy should I use? Consider composite indexes, partial indexes, and covering indexes where appropriate.
N+1 Query Refactoring 
My application executes hundreds of similar queries in a loop: [describe pattern or show code]. This is causing performance issues. Help me refactor this using JOINs, batch loading, or other techniques to reduce the number of database round-trips.

Bewährte Verfahren

  • Always use EXPLAIN ANALYZE to understand actual query execution before optimizing
  • Create indexes on columns used in WHERE, JOIN, and ORDER BY clauses - but avoid over-indexing as each index slows write operations
  • Fetch only required columns instead of SELECT *, and filter data as early as possible in the query

Vermeiden

  • Using functions on indexed columns in WHERE clauses without corresponding functional indexes
  • Pagination with large OFFSET values on big tables instead of cursor-based approaches
  • Executing queries in application loops (N+1 pattern) instead of batch loading or JOINs

Häufig gestellte Fragen

How do I identify which queries to optimize first?
Focus on queries with high total impact: frequent queries with moderate slowness often matter more than rare very-slow queries. Use pg_stat_statements (PostgreSQL) or slow query logs to find queries by total execution time and call count.
Will adding more indexes always improve performance?
No. Indexes speed up reads but slow down INSERT, UPDATE, and DELETE operations. Each index consumes storage and must be maintained. Focus on indexes that support your most critical queries and avoid indexing low-cardinality columns.
What is the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the estimated query plan based on table statistics. EXPLAIN ANALYZE actually executes the query and reports real timing and row counts, revealing gaps between estimates and reality that often indicate stale statistics or parameter sniffing issues.
When should I use a composite index versus separate indexes?
Use composite indexes when queries filter on multiple columns together. Order columns by selectivity (most selective first) and consider your query patterns. A composite index on (A, B) supports queries filtering on A alone or A+B together, but not B alone.
How do I know if my index is being used?
Query pg_stat_user_indexes (PostgreSQL) to check idx_scan counts. If an index shows zero or very few scans despite relevant queries, it may not be usable or the optimizer chose a different plan. Use EXPLAIN to verify index usage for specific queries.
What is cursor-based pagination and when should I use it?
Cursor-based pagination uses a WHERE clause with the last seen value (e.g., WHERE created_at < last_cursor) instead of OFFSET. It maintains consistent performance regardless of page depth, unlike OFFSET which must scan and discard all preceding rows.

Entwicklerdetails

Autor

sickn33

Lizenz

MIT

Repository

https://github.com/sickn33/antigravity-awesome-skills/tree/main/skills/sql-optimization-patterns

Ref

main

Dateistruktur

📁 resources/

📄 implementation-playbook.md

📄 SKILL.md