skills.rest
gabrielnsmnto
gabrielnsmntoCOMMUNITY
Explore all skills by this author →
GitHub

db-analyze-hotpaths

Runs EXPLAIN ANALYZE with BUFFERS on slow PostgreSQL queries to identify performance issues and recommend indexes.

3|Updated Feb 7, 2026
Search Tags:#query optimization#postgresql#supabase#performance analysis#pg_stat_statements#explain analyze#index advisor
One-click install
npx skills add https://github.com/gabrielnsmnto/kord-aios --skill db-analyze-hotpaths-gabrielnsmnto
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: db-analyze-hotpaths
Source: https://github.com/gabrielnsmnto/kord-aios/tree/main/src/features/builtin-skills/kord-aios/database/db-analyze-hotpaths
Command: npx skills add https://github.com/gabrielnsmnto/kord-aios --skill db-analyze-hotpaths-gabrielnsmnto

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Slow database queries are hard to diagnose without execution plan analysis. This Skill automates running EXPLAIN ANALYZE with BUFFERS on your hottest PostgreSQL/Supabase queries, interprets the output, and produces actionable index recommendations. ## Core Features & Use Cases - Hot Query Discovery: Pulls the slowest queries from pg_stat_statements ranked by mean execution time and total time share. - Deep Plan Analysis: Runs EXPLAIN with ANALYZE, BUFFERS, VERBOSE, COSTS, and TIMING, then flags sequential scans, row estimate mismatches, buffer cache misses, temp file spills, and expensive nested loops. - Index Recommendations: Uses the Supabase index_advisor extension to generate CREATE INDEX suggestions and compiles everything into a timestamped markdown performance report. - Use Case: Your Supabase app's dashboard endpoint is slow. Run this Skill to find the top 20 slowest queries, analyze the worst offenders, and get a report with concrete index migrations to apply. ## Quick Start Ask the agent to analyze the hot query paths on your Supabase database and generate a performance report with index recommendations.

Frequently Asked Questions about db-analyze-hotpaths

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I find slow queries in PostgreSQL?▼

Query pg_stat_statements ordered by mean_exec_time to find the slowest queries by average and total execution time. This Skill automates that lookup and lets you select which queries to analyze in depth.

How to use EXPLAIN ANALYZE with BUFFERS in PostgreSQL?▼

Run EXPLAIN (ANALYZE true, BUFFERS true, VERBOSE true, COSTS true, TIMING true) before your query. BUFFERS reveals shared hits versus disk reads and temp file usage, which exposes cache misses and insufficient work_mem.

Does this work with Supabase databases?▼

Yes, it is designed for Supabase. pg_stat_statements is enabled by default, and the index_advisor extension (Supabase Pro+) generates index suggestions. Supabase Studio's Query Performance Report offers a UI alternative.

What does a sequential scan in EXPLAIN output mean?▼

A Seq Scan means PostgreSQL reads the entire table instead of using an index. It is a problem on large tables when filters discard many rows; the fix is usually adding an index on the filtered columns.

Why does my query use temporary files in EXPLAIN BUFFERS output?▼

Temp read/written in BUFFERS output means the query spilled sorting or hashing to disk because work_mem is too small. Increase work_mem, optimize the query, or add indexes to eliminate the spill.

Related Skills

Explore More Software Engineering Skills→
mohamedgshoaibmohamedgshoaib
5

supabase-performance-tuning

Diagnose and optimize PostgreSQL and Supabase query performance with pg_stat_statements.

Community
Advanced
chicanoandres702chicanoandres702

database-optimizer

Analyze queries, indexes, and configurations to optimize database performance.

Community
Advanced
WhaleylawWhaleylaw

database-monitoring-specialist

Monitor PostgreSQL and Supabase databases for performance regressions and slow queries.

Community
Intermediate
PostHogPostHog
713

exploring-apm-traces

Investigates distributed OpenTelemetry traces and spans in PostHog APM via MCP tools.

Official
Advanced