<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: left right</title>
    <description>The latest articles on DEV Community by left right (@left_right_fa0ba71ad8ef57).</description>
    <link>https://dev.to/left_right_fa0ba71ad8ef57</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F1532124%2Fb67db617-e627-4489-8003-aafb7a83b86b.jpg</url>
      <title>DEV Community: left right</title>
      <link>https://dev.to/left_right_fa0ba71ad8ef57</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/left_right_fa0ba71ad8ef57"/>
    <language>en</language>
    <item>
      <title>9 PostgreSQL fixes that took real queries from ~100 ms to under 1 ms (with EXPLAIN output)</title>
      <dc:creator>left right</dc:creator>
      <pubDate>Wed, 18 Nov 2026 20:14:58 +0000</pubDate>
      <link>https://dev.to/left_right_fa0ba71ad8ef57/9-postgresql-fixes-that-took-real-queries-from-100-ms-to-under-1-ms-with-explain-output-3mmi</link>
      <guid>https://dev.to/left_right_fa0ba71ad8ef57/9-postgresql-fixes-that-took-real-queries-from-100-ms-to-under-1-ms-with-explain-output-3mmi</guid>
      <description>&lt;p&gt;Most "slow database" problems I see are not exotic. They are the same handful of patterns: a missing index on a foreign key, a function wrapped around a column, an &lt;code&gt;OFFSET&lt;/code&gt; that reads 200,000 rows to show 20.&lt;/p&gt;

&lt;p&gt;I ran each pattern below on a 1,000,000-row &lt;code&gt;orders&lt;/code&gt; table in PostgreSQL 16.6, captured &lt;code&gt;EXPLAIN (ANALYZE, BUFFERS)&lt;/code&gt; before and after the fix, and kept the numbers as they came out. Your timings will differ — the &lt;em&gt;shape&lt;/em&gt; of the plan is what matters.&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Fix&lt;/th&gt;
&lt;th&gt;Before&lt;/th&gt;
&lt;th&gt;After&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Index the foreign key&lt;/td&gt;
&lt;td&gt;67.7 ms&lt;/td&gt;
&lt;td&gt;0.10 ms&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Composite index (equality first, then sort column)&lt;/td&gt;
&lt;td&gt;80.0 ms&lt;/td&gt;
&lt;td&gt;0.20 ms&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Expression index for &lt;code&gt;lower(email)&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;14.2 ms&lt;/td&gt;
&lt;td&gt;0.07 ms&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Rewrite &lt;code&gt;date(created_at) = …&lt;/code&gt; as a range&lt;/td&gt;
&lt;td&gt;100.2 ms&lt;/td&gt;
&lt;td&gt;0.31 ms&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Partial index for the hot slice&lt;/td&gt;
&lt;td&gt;82.3 ms&lt;/td&gt;
&lt;td&gt;0.15 ms&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Covering index (&lt;code&gt;INCLUDE&lt;/code&gt;)&lt;/td&gt;
&lt;td&gt;41.2 ms&lt;/td&gt;
&lt;td&gt;3.9 ms&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Keyset pagination instead of &lt;code&gt;OFFSET&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;28.2 ms&lt;/td&gt;
&lt;td&gt;0.11 ms&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;code&gt;pg_trgm&lt;/code&gt; for &lt;code&gt;ILIKE '%text%'&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;168.5 ms&lt;/td&gt;
&lt;td&gt;1.1 ms&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Planner estimate instead of exact &lt;code&gt;count(*)&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;84.1 ms&lt;/td&gt;
&lt;td&gt;0.07 ms&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  1. Index your foreign keys
&lt;/h2&gt;

&lt;p&gt;PostgreSQL creates an index for a primary key, &lt;strong&gt;not&lt;/strong&gt; for a foreign key. So this innocent query:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;total&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;4242&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;reads the whole table:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Gather  (actual time=2.554..67.674 rows=10 loops=1)
  -&amp;gt;  Parallel Seq Scan on orders  (actual time=16.595..43.016 rows=3 loops=3)
        Filter: (customer_id = 4242)
        Rows Removed by Filter: 333330
        Buffers: shared hit=96 read=15055
Execution Time: 67.727 ms
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The giveaway is &lt;code&gt;Rows Removed by Filter: 333330&lt;/code&gt; per worker — a million rows checked to return ten. One index later:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;orders_customer_id_idx&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;





&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Bitmap Heap Scan on orders  (actual time=0.032..0.058 rows=10 loops=1)
  -&amp;gt;  Bitmap Index Scan on orders_customer_id_idx
        Index Cond: (customer_id = 4242)
        Buffers: shared hit=6
Execution Time: 0.104 ms
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;From 15,151 buffers to 16. &lt;strong&gt;Rule:&lt;/strong&gt; every foreign key you filter or join on deserves an index.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. Composite index: equality columns first, then the range/sort column
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;total&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'pending'&lt;/span&gt; &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;=&lt;/span&gt; &lt;span class="s1"&gt;'2026-06-01'&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt; &lt;span class="k"&gt;LIMIT&lt;/span&gt; &lt;span class="mi"&gt;50&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;An index on &lt;code&gt;(status, created_at)&lt;/code&gt; lets PostgreSQL jump straight to &lt;code&gt;pending&lt;/code&gt;, read rows already in date order and stop after 50. Column order matters: equality first, then the range or sort column. 80 ms → 0.2 ms.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Expression index for &lt;code&gt;lower()&lt;/code&gt; and friends
&lt;/h2&gt;

&lt;p&gt;&lt;code&gt;WHERE lower(email) = '…'&lt;/code&gt; cannot use a plain index on &lt;code&gt;email&lt;/code&gt;. Index the expression itself:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;customers_lower_email_idx&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;customers&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nb"&gt;lower&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;email&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;14.2 ms → 0.07 ms.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Keep the column bare (sargable queries)
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- slow: function on the column hides it from the index&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="kt"&gt;date&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="no"&gt;AT&lt;/span&gt; &lt;span class="kt"&gt;TIME&lt;/span&gt; &lt;span class="no"&gt;ZONE&lt;/span&gt; &lt;span class="s1"&gt;'UTC'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'2026-03-15'&lt;/span&gt;
&lt;span class="c1"&gt;-- fast: same rows, as a range&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;=&lt;/span&gt; &lt;span class="s1"&gt;'2026-03-15 00:00+00'&lt;/span&gt; &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt; &lt;span class="s1"&gt;'2026-03-16 00:00+00'&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;100.2 ms → 0.31 ms. No new index needed — just let the existing one work.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. Partial index for the small slice you query all the time
&lt;/h2&gt;

&lt;p&gt;If 5% of orders are &lt;code&gt;pending&lt;/code&gt; and your dashboard only ever asks for those, index only them:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;orders_pending_created_idx&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;created_at&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'pending'&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Smaller, faster to maintain, 82 ms → 0.15 ms.&lt;/p&gt;

&lt;h2&gt;
  
  
  6. Covering index for index-only scans
&lt;/h2&gt;

&lt;p&gt;When the index already contains every column a query needs, PostgreSQL can skip the table (&lt;code&gt;Index Only Scan&lt;/code&gt;, &lt;code&gt;Heap Fetches: 0&lt;/code&gt;) once the visibility map is current — i.e. after &lt;code&gt;VACUUM&lt;/code&gt;:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;orders_customer_total_idx&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="no"&gt;INCLUDE&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="k"&gt;VACUUM&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;  &lt;span class="c1"&gt;-- updates the visibility map so the heap can be skipped&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;41 ms → 3.9 ms for a &lt;code&gt;GROUP BY customer_id&lt;/code&gt; over 2,000 customers.&lt;/p&gt;

&lt;h2&gt;
  
  
  7. Keyset pagination instead of &lt;code&gt;OFFSET&lt;/code&gt;
&lt;/h2&gt;

&lt;p&gt;&lt;code&gt;OFFSET 200000&lt;/code&gt; still reads and throws away 200,000 rows. Remember the last id you showed:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;total&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="mi"&gt;200000&lt;/span&gt; &lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="k"&gt;LIMIT&lt;/span&gt; &lt;span class="mi"&gt;20&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Same cost on page 1 and page 10,000. 28 ms → 0.11 ms.&lt;/p&gt;

&lt;h2&gt;
  
  
  8. &lt;code&gt;ILIKE '%text%'&lt;/code&gt; with &lt;code&gt;pg_trgm&lt;/code&gt;
&lt;/h2&gt;

&lt;p&gt;A B-tree cannot help a leading wildcard. A trigram GIN index can:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="no"&gt;EXTENSION&lt;/span&gt; &lt;span class="k"&gt;IF&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;EXISTS&lt;/span&gt; &lt;span class="n"&gt;pg_trgm&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;orders_note_trgm_idx&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="k"&gt;USING&lt;/span&gt; &lt;span class="n"&gt;gin&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;note&lt;/span&gt; &lt;span class="n"&gt;gin_trgm_ops&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;168 ms → 1.1 ms.&lt;/p&gt;

&lt;h2&gt;
  
  
  9. Do you really need an exact &lt;code&gt;count(*)&lt;/code&gt;?
&lt;/h2&gt;

&lt;p&gt;For a "≈ 1.2M orders" badge, the planner's statistics are instant and usually within a few percent after &lt;code&gt;ANALYZE&lt;/code&gt;:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;reltuples&lt;/span&gt;&lt;span class="o"&gt;::&lt;/span&gt;&lt;span class="kt"&gt;bigint&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;estimated_rows&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;pg_class&lt;/span&gt; &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;oid&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'orders'&lt;/span&gt;&lt;span class="o"&gt;::&lt;/span&gt;&lt;span class="n"&gt;regclass&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;84 ms → 0.07 ms.&lt;/p&gt;

&lt;h2&gt;
  
  
  Bonus: a correctness trap, not a speed one
&lt;/h2&gt;

&lt;p&gt;&lt;code&gt;x NOT IN (SELECT y …)&lt;/code&gt; returns &lt;strong&gt;no rows at all&lt;/strong&gt; if the subquery contains a single &lt;code&gt;NULL&lt;/code&gt;. Use &lt;code&gt;NOT EXISTS&lt;/code&gt; instead. It is not faster here — it is &lt;em&gt;right&lt;/em&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  How to find these in your own database
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;Turn on &lt;code&gt;pg_stat_statements&lt;/code&gt; and sort by &lt;code&gt;total_exec_time&lt;/code&gt; — fix the queries that cost the most in total, not the slowest single run.&lt;/li&gt;
&lt;li&gt;Run &lt;code&gt;EXPLAIN (ANALYZE, BUFFERS)&lt;/code&gt; on the top offenders. Look for &lt;code&gt;Seq Scan&lt;/code&gt; with a large &lt;code&gt;Rows Removed by Filter&lt;/code&gt;, and for big &lt;code&gt;read=&lt;/code&gt; buffer counts.&lt;/li&gt;
&lt;li&gt;Change one thing, re-run, compare. Keep what helps; drop indexes nobody uses (&lt;code&gt;pg_stat_user_indexes.idx_scan = 0&lt;/code&gt;).&lt;/li&gt;
&lt;/ol&gt;




&lt;p&gt;I packaged this workflow — the 10 lessons with full before/after plans, a reproducible 1M-row demo dataset and 12 read-only diagnostic scripts (top queries, unused and duplicate indexes, vacuum health, blocking locks and more) — as the &lt;strong&gt;&lt;a href="https://dreamer5768.gumroad.com/l/postgresql-performance-playbook?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=pg-9-fixes" rel="noopener noreferrer"&gt;PostgreSQL Performance Playbook&lt;/a&gt;&lt;/strong&gt; ($29, 30-day money-back guarantee).&lt;/p&gt;

&lt;p&gt;Just want the one-page version? The &lt;strong&gt;&lt;a href="https://dreamer5768.gumroad.com/l/postgresql-performance-checklist?utm_source=devto&amp;amp;utm_medium=article&amp;amp;utm_campaign=pg-9-fixes" rel="noopener noreferrer"&gt;free PostgreSQL Performance Checklist&lt;/a&gt;&lt;/strong&gt; (PDF, $0) puts these checks on a single printable page.&lt;/p&gt;

&lt;p&gt;If you want more SQL practice first, two well-reviewed resources from other creators on Gumroad:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;a href="https://sylviavasilik.gumroad.com/l/HmyCz?a=657973603" rel="noopener noreferrer"&gt;SQL Practice Problems – Professional Package&lt;/a&gt; — 97 challenge problems (written for SQL Server/MySQL).&lt;/li&gt;
&lt;li&gt;
&lt;a href="https://decodepress.gumroad.com/l/postgresql?a=657973603" rel="noopener noreferrer"&gt;Decode PostgreSQL&lt;/a&gt; — how PostgreSQL works, explained without heavy code.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;em&gt;Disclosure: the last two are affiliate links — if you buy through them I earn a commission, at no extra cost to you.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>performance</category>
      <category>postgres</category>
      <category>sql</category>
      <category>database</category>
    </item>
  </channel>
</rss>
