PostgreSQL composite indexes for multi-column query patterns

<p>Queries filter on multiple columns: <code>WHERE status = 'validated' AND trust_score &gt; 0.5 ORDER BY created_at DESC</code>. Single-column indexes on each column aren't used effectively. Query planner does a full table scan.</p>

<p>Design composite indexes to match the exact query pattern (column order matters):</p> <div class="highlight"><pre><span></span><code><span class="c1">-- BAD: Three single-column indexes — planner may not combine them efficiently</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">idx_traces_status</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">traces</span><span class="p">(</span><span class="n">status</span><span class="p">);</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">idx_traces_trust_score</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">traces</span><span class="p">(</span><span class="n">trust_score</span><span class="p">);</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">idx_traces_created_at</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">traces</span><span class="p">(</span><span class="n">created_at</span><span class="p">);</span> <span class="c1">-- GOOD: Composite index ordered: equality filter → range filter → sort</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">idx_traces_status_trust_created</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">traces</span><span class="p">(</span><span class="n">status</span><span class="p">,</span><span class="w"> </span><span class="n">trust_score</span><span class="w"> </span><span class="k">DESC</span><span class="p">,</span><span class="w"> </span><span class="n">created_at</span><span class="w"> </span><span class="k">DESC</span><span class="p">);</span> <span class="c1">-- Partial index — only indexes rows that match the WHERE (smaller, faster)</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">idx_validated_traces</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">traces</span><span class="p">(</span><span class="n">trust_score</span><span class="w"> </span><span class="k">DESC</span><span class="p">,</span><span class="w"> </span><span class="n">created_at</span><span class="w"> </span><span class="k">DESC</span><span class="p">)</span> <span class="k">WHERE</span><span class="w"> </span><span class="n">status</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">'validated'</span><span class="p">;</span> <span class="c1">-- Partial index for non-null embeddings (used by embedding worker polling)</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">idx_traces_needs_embedding</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">traces</span><span class="p">(</span><span class="n">id</span><span class="p">)</span> <span class="k">WHERE</span><span class="w"> </span><span class="n">embedding</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="k">NULL</span><span class="p">;</span> <span class="c1">-- Verify index is used</span> <span class="k">EXPLAIN</span><span class="w"> </span><span class="k">ANALYZE</span> <span class="k">SELECT</span><span class="w"> </span><span class="n">id</span><span class="p">,</span><span class="w"> </span><span class="n">title</span><span class="p">,</span><span class="w"> </span><span class="n">trust_score</span> <span class="k">FROM</span><span class="w"> </span><span class="n">traces</span> <span class="k">WHERE</span><span class="w"> </span><span class="n">status</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">'validated'</span> <span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="n">trust_score</span><span class="w"> </span><span class="o">&gt;</span><span class="w"> </span><span class="mi">0</span><span class="p">.</span><span class="mi">5</span> <span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">created_at</span><span class="w"> </span><span class="k">DESC</span> <span class="k">LIMIT</span><span class="w"> </span><span class="mi">20</span><span class="p">;</span> <span class="c1">-- Should show: Index Scan using idx_traces_status_trust_created</span> <span class="c1">-- NOT: Seq Scan</span> <span class="c1">-- Check index sizes and usage</span> <span class="k">SELECT</span> <span class="w"> </span><span class="n">indexname</span><span class="p">,</span> <span class="w"> </span><span class="n">pg_size_pretty</span><span class="p">(</span><span class="n">pg_relation_size</span><span class="p">(</span><span class="n">indexrelid</span><span class="p">))</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="k">size</span><span class="p">,</span> <span class="w"> </span><span class="n">idx_scan</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">scans</span><span class="p">,</span> <span class="w"> </span><span class="n">idx_tup_read</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">rows_read</span> <span class="k">FROM</span><span class="w"> </span><span class="n">pg_stat_user_indexes</span> <span class="k">WHERE</span><span class="w"> </span><span class="n">tablename</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">'traces'</span> <span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">idx_scan</span><span class="w"> </span><span class="k">DESC</span><span class="p">;</span> </code></pre></div> <p>Rule for column order: equality columns first (<code>=</code>), then range columns (<code>&gt;</code>, <code>&lt;</code>, <code>BETWEEN</code>), then ORDER BY columns last. Partial indexes are dramatically smaller and faster when the filter covers most queries. <code>INCLUDE</code> clause adds columns to the index leaf without sorting them (useful for covering indexes).</p>