{"id":1434,"date":"2026-10-15T18:00:00","date_gmt":"2026-10-15T17:00:00","guid":{"rendered":"https:\/\/wade.one\/blog\/?p=1434"},"modified":"2026-10-15T18:00:00","modified_gmt":"2026-10-15T17:00:00","slug":"read-the-postgresql-query-plan-before-adding-another-index","status":"publish","type":"post","link":"https:\/\/wade.one\/blog\/2026\/10\/15\/read-the-postgresql-query-plan-before-adding-another-index\/","title":{"rendered":"Read the PostgreSQL Query Plan Before Adding Another Index"},"content":{"rendered":"<p>When a database query gets slow, adding an index is an appealingly concrete thing to do. I would still look at the query plan first. An index on the wrong column combination can leave the original problem intact while giving every write another bit of maintenance to perform.<\/p>\n<p>PostgreSQL&#8217;s <a href=\"https:\/\/www.postgresql.org\/docs\/18\/using-explain.html\">EXPLAIN guide<\/a> shows how the planner expects to execute a query. EXPLAIN ANALYZE adds measurements from actually running it, which is useful but deserves care: it executes the statement, including changes made by write queries. Start with a safe query and representative data rather than casually experimenting against production.<\/p>\n<p>I look for where the work grows: a join producing far more rows than expected, a filter discarding most of what was read, or a sort covering a large result that the application barely uses. A sequential scan is not automatically a mistake. Sometimes reading a small table directly is the sensible plan, and forcing an index would only satisfy a prejudice.<\/p>\n<p>The useful outcome is a measured improvement to the query people run. Compare before and after with the same conditions, and keep the application behaviour intact. &#8220;We added three indexes&#8221; is an implementation detail. &#8220;This screen no longer makes people wait&#8221; is the result that matters.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When a database query gets slow, adding an index is an appealingly concrete thing to do.<\/p>\n","protected":false},"author":0,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[13],"tags":[38,253,259],"class_list":["post-1434","post","type-post","status-publish","format-standard","hentry","category-programming","tag-performance","tag-postgresql","tag-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.7 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Read the PostgreSQL Query Plan Before Adding Another Index - wade.one<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/wade.one\/blog\/2026\/10\/15\/read-the-postgresql-query-plan-before-adding-another-index\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Read the PostgreSQL Query Plan Before Adding Another Index - wade.one\" \/>\n<meta property=\"og:description\" content=\"When a database query gets slow, adding an index is an appealingly concrete thing to do.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/wade.one\/blog\/2026\/10\/15\/read-the-postgresql-query-plan-before-adding-another-index\/\" \/>\n<meta property=\"og:site_name\" content=\"wade.one\" \/>\n<meta property=\"article:published_time\" content=\"2026-10-15T17:00:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/Wade-Logo-cropped.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1518\" \/>\n\t<meta property=\"og:image:height\" content=\"1506\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@wadewomersley\" \/>\n<meta name=\"twitter:site\" content=\"@wadewomersley\" \/>\n<meta name=\"twitter:label1\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/15\\\/read-the-postgresql-query-plan-before-adding-another-index\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/15\\\/read-the-postgresql-query-plan-before-adding-another-index\\\/\"},\"author\":{\"name\":\"\",\"@id\":\"\"},\"headline\":\"Read the PostgreSQL Query Plan Before Adding Another Index\",\"datePublished\":\"2026-10-15T17:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/15\\\/read-the-postgresql-query-plan-before-adding-another-index\\\/\"},\"wordCount\":216,\"publisher\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#\\\/schema\\\/person\\\/8b4739f8f8bb2cff5d792d4b8779fcc3\"},\"keywords\":[\"performance\",\"postgresql\",\"sql\"],\"articleSection\":[\"Programming\"],\"inLanguage\":\"en-GB\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/15\\\/read-the-postgresql-query-plan-before-adding-another-index\\\/\",\"url\":\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/15\\\/read-the-postgresql-query-plan-before-adding-another-index\\\/\",\"name\":\"Read the PostgreSQL Query Plan Before Adding Another Index - wade.one\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#website\"},\"datePublished\":\"2026-10-15T17:00:00+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/15\\\/read-the-postgresql-query-plan-before-adding-another-index\\\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/15\\\/read-the-postgresql-query-plan-before-adding-another-index\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/2026\\\/10\\\/15\\\/read-the-postgresql-query-plan-before-adding-another-index\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/wade.one\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Read the PostgreSQL Query Plan Before Adding Another Index\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/wade.one\\\/blog\\\/\",\"name\":\"wade.one\",\"description\":\"wade womersley - york based software engineer\",\"publisher\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#\\\/schema\\\/person\\\/8b4739f8f8bb2cff5d792d4b8779fcc3\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/wade.one\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/#\\\/schema\\\/person\\\/8b4739f8f8bb2cff5d792d4b8779fcc3\",\"name\":\"Wade Womersley\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/200px.png\",\"url\":\"https:\\\/\\\/wade.one\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/200px.png\",\"contentUrl\":\"https:\\\/\\\/wade.one\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/200px.png\",\"width\":202,\"height\":200,\"caption\":\"Wade Womersley\"},\"logo\":{\"@id\":\"https:\\\/\\\/wade.one\\\/blog\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/200px.png\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Read the PostgreSQL Query Plan Before Adding Another Index - wade.one","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/wade.one\/blog\/2026\/10\/15\/read-the-postgresql-query-plan-before-adding-another-index\/","og_locale":"en_GB","og_type":"article","og_title":"Read the PostgreSQL Query Plan Before Adding Another Index - wade.one","og_description":"When a database query gets slow, adding an index is an appealingly concrete thing to do.","og_url":"https:\/\/wade.one\/blog\/2026\/10\/15\/read-the-postgresql-query-plan-before-adding-another-index\/","og_site_name":"wade.one","article_published_time":"2026-10-15T17:00:00+00:00","og_image":[{"width":1518,"height":1506,"url":"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/Wade-Logo-cropped.png","type":"image\/png"}],"twitter_card":"summary_large_image","twitter_creator":"@wadewomersley","twitter_site":"@wadewomersley","twitter_misc":{"Estimated reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/wade.one\/blog\/2026\/10\/15\/read-the-postgresql-query-plan-before-adding-another-index\/#article","isPartOf":{"@id":"https:\/\/wade.one\/blog\/2026\/10\/15\/read-the-postgresql-query-plan-before-adding-another-index\/"},"author":{"name":"","@id":""},"headline":"Read the PostgreSQL Query Plan Before Adding Another Index","datePublished":"2026-10-15T17:00:00+00:00","mainEntityOfPage":{"@id":"https:\/\/wade.one\/blog\/2026\/10\/15\/read-the-postgresql-query-plan-before-adding-another-index\/"},"wordCount":216,"publisher":{"@id":"https:\/\/wade.one\/blog\/#\/schema\/person\/8b4739f8f8bb2cff5d792d4b8779fcc3"},"keywords":["performance","postgresql","sql"],"articleSection":["Programming"],"inLanguage":"en-GB"},{"@type":"WebPage","@id":"https:\/\/wade.one\/blog\/2026\/10\/15\/read-the-postgresql-query-plan-before-adding-another-index\/","url":"https:\/\/wade.one\/blog\/2026\/10\/15\/read-the-postgresql-query-plan-before-adding-another-index\/","name":"Read the PostgreSQL Query Plan Before Adding Another Index - wade.one","isPartOf":{"@id":"https:\/\/wade.one\/blog\/#website"},"datePublished":"2026-10-15T17:00:00+00:00","breadcrumb":{"@id":"https:\/\/wade.one\/blog\/2026\/10\/15\/read-the-postgresql-query-plan-before-adding-another-index\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/wade.one\/blog\/2026\/10\/15\/read-the-postgresql-query-plan-before-adding-another-index\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/wade.one\/blog\/2026\/10\/15\/read-the-postgresql-query-plan-before-adding-another-index\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/wade.one\/blog\/"},{"@type":"ListItem","position":2,"name":"Read the PostgreSQL Query Plan Before Adding Another Index"}]},{"@type":"WebSite","@id":"https:\/\/wade.one\/blog\/#website","url":"https:\/\/wade.one\/blog\/","name":"wade.one","description":"wade womersley - york based software engineer","publisher":{"@id":"https:\/\/wade.one\/blog\/#\/schema\/person\/8b4739f8f8bb2cff5d792d4b8779fcc3"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/wade.one\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/wade.one\/blog\/#\/schema\/person\/8b4739f8f8bb2cff5d792d4b8779fcc3","name":"Wade Womersley","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/200px.png","url":"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/200px.png","contentUrl":"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/200px.png","width":202,"height":200,"caption":"Wade Womersley"},"logo":{"@id":"https:\/\/wade.one\/blog\/wp-content\/uploads\/2015\/02\/200px.png"}}]}},"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":1431,"url":"https:\/\/wade.one\/blog\/2026\/10\/12\/postgresql-14-support-ends-in-november-plan-the-upgrade-now\/","url_meta":{"origin":1434,"position":0},"title":"PostgreSQL 14 Support Ends in November: Plan the Upgrade Now","author":"","date":"October 12, 2026","format":false,"excerpt":"PostgreSQL 14 reaches the end of community support on 12 November 2026.","rel":"","context":"In &quot;Programming&quot;","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":938,"url":"https:\/\/wade.one\/blog\/2015\/07\/06\/cloudant-ibm-query-indexing-arrays-directly-using-erlang\/","url_meta":{"origin":1434,"position":1},"title":"Cloudant (IBM) Query &#8211; Indexing arrays directly using Erlang","author":"Wade","date":"July 6, 2015","format":false,"excerpt":"Really short post this one on indexing using Cloudant Query\u00a0to index arrays in your document, e.g. documents that have a field like this: { \"ids\": [ \"alpha\", \"bravo\", \"charlie\" ] } I was reading the documentation and just could not get it to index these items using anything but the\u2026","rel":"","context":"In &quot;Programming&quot;","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/wade.one\/blog\/wp-content\/uploads\/2015\/07\/SmJYeZ0r_400x400.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":431,"url":"https:\/\/wade.one\/blog\/2010\/01\/29\/mysql-binary16-and-scalability\/","url_meta":{"origin":1434,"position":2},"title":"MySQL &#8211; Binary(16) and scalability","author":"Wade","date":"January 29, 2010","format":false,"excerpt":"Over the past few months at work, we've seen our database grown from silly big to really silly big, it's still a way to go to get to the size of the big boys such as Facebook etc. but it's still a database stored in MySQL that most day-to-day PHP\u2026","rel":"","context":"In &quot;Programming&quot;","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":453,"url":"https:\/\/wade.one\/blog\/2010\/02\/26\/phpuk2010-part-2-mysql-stuff\/","url_meta":{"origin":1434,"position":3},"title":"#PHPUK2010 Part 2 (MySQL stuff)","author":"Wade","date":"February 26, 2010","format":false,"excerpt":"Just picked up a nice tid-bit on creating a unique index on a two column table where the values in each column may be either way around but you only ever want one instance of the value in that row. So what this means is, inserting 2,1 and 1,2 for\u2026","rel":"","context":"In &quot;Programming&quot;","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":437,"url":"https:\/\/wade.one\/blog\/2010\/01\/31\/mysql-and-binary16-the-reasonsbenefitsdrawbacks-mysql\/","url_meta":{"origin":1434,"position":4},"title":"MySQL and Binary(16) &#8211; The Reasons\/Benefits\/Drawbacks (#mysql)","author":"Wade","date":"January 31, 2010","format":false,"excerpt":"I recently posted an article about using BINARY(16) for storing MD5's as unique identifiers instead of simple integer ID's (usually auto increment); in that article I touched on one of the benefits, reducing JOIN's, but there are other reasons for doing it too, so I thought I'd post an article\u2026","rel":"","context":"In &quot;Programming&quot;","block_context":{"text":"Programming","link":"https:\/\/wade.one\/blog\/category\/programming\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1376,"url":"https:\/\/wade.one\/blog\/2026\/07\/15\/codeql-system-prompt-injection-query-is-a-useful-start\/","url_meta":{"origin":1434,"position":5},"title":"CodeQL&#8217;s System Prompt Injection Query Is a Useful Start","author":"Wade","date":"July 15, 2026","format":false,"excerpt":"CodeQL 2.26 can trace untrusted values into JavaScript and TypeScript system prompts, turning one AI security risk into something code review can see.","rel":"","context":"In &quot;AI&quot;","block_context":{"text":"AI","link":"https:\/\/wade.one\/blog\/category\/ai\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/posts\/1434","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/types\/post"}],"replies":[{"embeddable":true,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/comments?post=1434"}],"version-history":[{"count":1,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/posts\/1434\/revisions"}],"predecessor-version":[{"id":1455,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/posts\/1434\/revisions\/1455"}],"wp:attachment":[{"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/media?parent=1434"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/categories?post=1434"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wade.one\/blog\/wp-json\/wp\/v2\/tags?post=1434"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}