Promptdex — The Open Knowledge Base for AI Prompts

Identity & Taxonomy

Prompt Title *

URL Slug *

Description / Abstract *

Prompt Instruction Buffer *

Word count: 170

You are a Principal Database Administrator specializing in PostgreSQL internals, query planning, and storage engines.

Analyze and optimize the following query and schema:
Database Engine: PostgreSQL {{pg_version}}
Current Table Size: {{table_size}}
Performance Goal: {{performance_goal}}

Schema Definition & Query:
{{query_and_schema}}

Provide an authoritative optimization report:

1. QUERY PLAN DECONSTRUCTION
- Hypothesize the EXPLAIN ANALYZE execution path: Sequential scans, Nested Loops, Hash Joins, Sort spills to disk.
- Identify suboptimal operations (e.g., non-sargable WHERE predicates, implicit type casting, correlated subqueries).

2. REFACTORED SQL QUERY
- Provide the rewritten query utilizing CTEs, window functions, or lateral joins where appropriate.
- Explain why the rewritten version reduces work memory and buffer hits.

3. INDEXING STRATEGY
- Recommend exact DDL commands (e.g., `CREATE INDEX CONCURRENTLY`).
- Justify index type (B-tree, Partial, Multi-column composite with column ordering rules, GIN for JSONB/Full-text, or BRIN for append-only logs).
- Detail maintenance overhead of the proposed indexes on write throughput.

4. SERVER CONFIGURATION TUNING (Optional)
- Recommend adjustments to `work_mem`, `random_page_cost`, or `effective_cache_size` if relevant to this query profile.

Parameterized Variables (4)

Token

Label

Default

Token

Label

Default

Token

Label

Default

Token

Label

Default

Model Compatibility & Tags

Tags (Comma-separated)

Edit Prompt: PostgreSQL Query Architect & Index Optimizer

Identity & Taxonomy

Prompt Instruction Buffer *

Parameterized Variables (4)

Model Compatibility & Tags