PostgreSQL EXPLAIN ANALYZE interpretation and query optimization

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

<p>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="n">id</span><span class="p">,</span><span class="w"> </span><span class="n">t</span><span class="p">.</span><span class="n">title</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="w"> </span><span class="k">LIMIT</span><span class="w"> </span><span class="mi">20</span><span class="p">;</span> </code></pre></div> <p>Key nodes to look for:</p> <div class="highlight"><pre><span></span><code>-- Good: Index Scan using ix_traces_status_created on traces (actual time=0.1..5.2 rows=20) Bitmap Heap Scan on traces (actual time=12..45 rows=1000) -- Bad: Seq Scan on trace_tags &lt;- Missing index! (cost=500..1000 rows=100000) (actual rows=50) &lt;- 2000x over-estimate -&gt; stale stats Sort Method: external merge Disk: 2048kB &lt;- Sort spilling to disk Buffers: shared hit=100 read=5000 &lt;- 5000 disk reads, poor cache hit </code></pre></div> <p>Diagnosis and fixes:</p> <div class="highlight"><pre><span></span><code><span class="c1">-- Seq Scan 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">-- Stale statistics:</span> <span class="k">ANALYZE</span><span class="w"> </span><span class="n">traces</span><span class="p">;</span> <span class="c1">-- Sort spill:</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><span class="w"> </span><span class="c1">-- Session-level</span> <span class="c1">-- Cost estimate vs actual row mismatch:</span> <span class="c1">-- Check pg_stats for the column -- may need better statistics target</span> <span class="k">ALTER</span><span class="w"> </span><span class="k">TABLE</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="k">ALTER</span><span class="w"> </span><span class="k">COLUMN</span><span class="w"> </span><span class="n">status</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="k">STATISTICS</span><span class="w"> </span><span class="mi">500</span><span class="p">;</span> <span class="k">ANALYZE</span><span class="w"> </span><span class="n">traces</span><span class="p">;</span> </code></pre></div> <p>Key points: - Seq Scan on large tables indicates missing index - Estimated rows &gt;&gt; actual rows indicates stale statistics -- run ANALYZE - external merge in Sort means increasing work_mem will help - Buffers: read=N high means data not in shared_buffers -- consider index - CONCURRENTLY avoids table lock when adding indexes to production tables</p>