plugins/aai-stack-postgres/skills/postgres-indexes/SKILL.md
February 1, 2026
Select agents to install to:
npx add-skill https://github.com/the-answerai/alphaagent-team/blob/main/plugins/aai-stack-postgres/skills/postgres-indexes/SKILL.md -a claude-code --skill postgres-indexesInstallation paths:
.claude/skills/postgres-indexes/# PostgreSQL Indexes Skill Patterns for creating and optimizing PostgreSQL indexes. ## Index Types ### B-tree Index (Default) ```sql -- Most common, good for equality and range queries CREATE INDEX idx_users_email ON users(email); -- Compound index CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC); -- Unique index CREATE UNIQUE INDEX idx_users_email_unique ON users(email); ``` ### Hash Index ```sql -- Fast for equality comparisons only CREATE INDEX idx_users_username_hash ON users USING HASH(username); ``` ### GIN Index (Generalized Inverted) ```sql -- For arrays CREATE INDEX idx_products_tags ON products USING GIN(tags); -- For JSONB CREATE INDEX idx_products_metadata ON products USING GIN(metadata); -- For full-text search CREATE INDEX idx_articles_search ON articles USING GIN(search_vector); ``` ### GiST Index (Generalized Search Tree) ```sql -- For geometric types CREATE INDEX idx_locations_point ON locations USING GIST(location); -- For range types CREATE INDEX idx_events_duration ON events USING GIST(duration); -- For full-text search (alternative to GIN) CREATE INDEX idx_articles_search_gist ON articles USING GIST(search_vector); ``` ### BRIN Index (Block Range) ```sql -- Efficient for large, naturally ordered tables CREATE INDEX idx_logs_created ON logs USING BRIN(created_at); -- Very compact, good for append-only tables CREATE INDEX idx_events_timestamp ON events USING BRIN(timestamp); ``` ## Partial Indexes ```sql -- Index only active users CREATE INDEX idx_active_users ON users(email) WHERE status = 'active'; -- Index only recent orders CREATE INDEX idx_recent_orders ON orders(created_at) WHERE created_at > '2024-01-01'; -- Index only non-null values CREATE INDEX idx_users_phone ON users(phone) WHERE phone IS NOT NULL; ``` ## Expression Indexes ```sql -- Index on lower case email CREATE INDEX idx_users_email_lower ON users(LOWER(email)); -- Index on date part CREATE INDEX idx_orders_date ON orders(DATE(created