사용 방법
- 설치 명령을 복사하고 자신의 에이전트 환경에 설치하기 전에 소스 패키지를 확인하세요.
- 필요한 도구, 인증 정보, 지원 환경은 원본 문서에서 확인하세요.
- 입력과 원하는 산출물을 설명하고 출처와 결과를 대조하세요.
npx skills add https://github.com/majesticlabs-dev/majestic-marketplace --skill database-optimizer시작 전 확인
- 설치에는 모델 구독, API 한도, 파일 접근 권한이 포함되지 않습니다.
- 별점 수와 설치 수는 활동 지표이며 독립적인 품질·보안 검증이 아닙니다.
원본 Skill 문서
원본 저장소의 제목, 예시, 코드, 표, 링크, 이미지를 유지해 표시합니다.
Database Optimizer
Audience: Rails developers tuning query performance. Goal: Diagnose slow queries with EXPLAIN ANALYZE, then prescribe specific index/query/AR fixes.
Detailed patterns (mechanical sympathy, complex SQL, pagination): references/patterns.md.
Measure First
sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT users.*, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE users.created_at > '2024-01-01'
GROUP BY users.id;Key metrics: Seq Scan vs Index Scan, rows estimated vs actual, Buffers shared hit vs read.
ruby
# Rails integration
User.where(active: true).includes(:orders).explain(:analyze)Index Design
Composite (column order matters)
ruby
# WHERE status = ? AND created_at > ? ORDER BY priority
add_index :tasks, [:status, :priority, :created_at]Partial (PostgreSQL)
ruby
add_index :users, :email, where: "deleted_at IS NULL", name: "index_active_users_email"
add_index :jobs, :priority, where: "status = 'pending'"Expression
ruby
add_index :users, 'LOWER(email)', name: 'index_users_on_lower_email'
add_index :products, "(metadata->>'category')", name: 'index_products_on_category'Covering (index-only scans)
ruby
add_index :orders, [:user_id, :created_at], include: [:total, :status]GIN (JSONB / arrays)
ruby
add_index :products, :metadata, using: :gin
add_index :products, :metadata, using: :gin, opclass: :jsonb_path_opsGiST (range / geometric / exclusion)
ruby
add_index :reservations, :date_range, using: :gist
add_index :locations, :coordinates, using: :gist
execute <<-SQL
ALTER TABLE reservations
ADD CONSTRAINT no_overlap
EXCLUDE USING gist (room_id WITH =, date_range WITH &&);
SQLUse GiST for: range queries, geometric/spatial data, nearest-neighbor, exclusion constraints.
BRIN (large correlated tables)
ruby
add_index :events, :created_at, using: :brin
add_index :logs, :timestamp, using: :brin, with: { pages_per_range: 32 }Tradeoffs: 100x smaller than B-tree, fast writes, less precise. Best for append-only >10M rows.
Query Hints (sparingly)
sql
SET LOCAL enable_seqscan = off;
SELECT * FROM large_table WHERE indexed_col = 'value';
RESET enable_seqscan;If hints are needed regularly, statistics are stale or indexes are missing.
ActiveRecord
ruby
User.includes(:orders, :profile) # AR decides
User.preload(:orders).where(active: true) # separate queries
User.eager_load(:orders).where("orders.total > 100") # LEFT JOIN
User.strict_loading.includes(:orders) # prevent N+1 in dev
User.find_each(batch_size: 1000) { |u| process(u) }
User.in_batches(of: 1000).update_all(processed: true)
User.pluck(:email) # not User.all.map(&:email)
User.count # not User.all.sizeWorkflow
- Identify slow queries → logs or
pg_stat_statements - EXPLAIN ANALYZE the suspect query
- Inspect index usage: missing, unused, bloated
- Compare row estimates vs actual → stale stats need ANALYZE
- Flag sequential scans on large tables
- Check buffer stats for disk I/O
- Prescribe specific fix (index, rewrite, eager-load) with expected impact
- Validate in staging with prod-like volume
Output Schema
yaml
analysis:
query: string # SQL or AR code
current_time_ms: number
bottleneck: string # e.g. "seq scan on orders (1.2M rows)"
recommendations:
- title: string
impact: high | medium | low
implementation: string # code/SQL
expected_time_ms: number
