PostgreSQL EXPLAIN ANALYZE interpretation

<p>I have a slow PostgreSQL query and I ran EXPLAIN ANALYZE. I need to understand how to read the output, identify why the query is slow (wrong index, bad join order, row estimate mismatch), and know what to fix.</p>

<p>Systematic approach to reading EXPLAIN ANALYZE output:</p> <div class="highlight"><pre><span></span><code><span class="k">EXPLAIN</span><span class="w"> </span><span class="p">(</span><span class="k">ANALYZE</span><span class="p">,</span><span class="w"> </span><span class="n">BUFFERS</span><span class="p">,</span><span class="w"> </span><span class="n">FORMAT</span><span class="w"> </span><span class="nb">TEXT</span><span class="p">)</span> <span class="k">SELECT</span><span class="w"> </span><span class="n">t</span><span class="p">.</span><span class="o">*</span><span class="p">,</span><span class="w"> </span><span class="n">array_agg</span><span class="p">(</span><span class="n">tg</span><span class="p">.</span><span class="n">name</span><span class="p">)</span><span class="w"> </span><span class="k">as</span><span class="w"> </span><span class="n">tags</span> <span class="k">FROM</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="n">t</span> <span class="k">JOIN</span><span class="w"> </span><span class="n">trace_tags</span><span class="w"> </span><span class="n">tt</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">tt</span><span class="p">.</span><span class="n">trace_id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">t</span><span class="p">.</span><span class="n">id</span> <span class="k">JOIN</span><span class="w"> </span><span class="n">tags</span><span class="w"> </span><span class="n">tg</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">tg</span><span class="p">.</span><span class="n">id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">tt</span><span class="p">.</span><span class="n">tag_id</span> <span class="k">WHERE</span><span class="w"> </span><span class="n">t</span><span class="p">.</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="k">GROUP</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">t</span><span class="p">.</span><span class="n">id</span> <span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">t</span><span class="p">.</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> </code></pre></div> <p>Reading the output — key nodes:</p> <div class="highlight"><pre><span></span><code>Limit (cost=1200..1250 rows=20 width=512) (actual time=820..822 rows=20 loops=1) -&gt; Sort (cost=1200..1350 rows=60000) (actual time=820..821 rows=20 loops=1) Sort Key: t.created_at DESC Sort Method: top-N heapsort Memory: 35kB -&gt; Hash Join (cost=500..900 rows=60000) (actual time=12..810 rows=60000 loops=1) Hash Cond: tt.trace_id = t.id Buffers: shared hit=2000 read=5000 &lt;- 5000 disk reads! -&gt; Seq Scan on trace_tags (cost=0..300) &lt;- Missing index! </code></pre></div> <p>Diagnosis checklist: - <code>Seq Scan</code> on large tables → missing index - <code>cost=X rows=Y</code> vs <code>actual rows=Z</code> (Y &gt;&gt; Z) → stale statistics, run <code>ANALYZE</code> - <code>Buffers: read=N</code> high → cache miss, data not in shared_buffers - <code>Sort Method: external merge</code> → sort spilling to disk, increase <code>work_mem</code> - <code>Nested Loop</code> with large inner table → should be Hash Join; missing join index</p> <p>Fixes:</p> <div class="highlight"><pre><span></span><code><span class="c1">-- Fix missing index on join column:</span> <span class="k">CREATE</span><span class="w"> </span><span class="k">INDEX</span><span class="w"> </span><span class="n">CONCURRENTLY</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">trace_tags</span><span class="p">(</span><span class="n">trace_id</span><span class="p">);</span> <span class="c1">-- Fix stale stats:</span> <span class="k">ANALYZE</span><span class="w"> </span><span class="n">traces</span><span class="p">;</span> <span class="c1">-- Fix sort spill (session-level):</span> <span class="k">SET</span><span class="w"> </span><span class="n">work_mem</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">'64MB'</span><span class="p">;</span> </code></pre></div>