Query Performance

Indexing, EXPLAIN plans, query tuning, and planner behavior — everything that goes into making Postgres queries fast.

New in pganalyze: Server Groups and Cluster-Wide Query Performance

13 May, 2026

You have a Postgres primary and three read replicas. Two of the replicas are serving slow queries. Where do you start? Until now, pganalyze would have shown you four separate views, one per server. Aggregating the numbers would be a manual process as you flip between tabs, having to remember which queries ran where. When we introduced cluster-aware Index Advisor, we started fixing this fragmented experience. Considering the entire workload before creating index recommendations for a cluster was…

pganalyze Index Advisor is Now Cluster-Aware

04 February, 2026

Before now, pganalyze Index Advisor has checked for insights on Postgres databases one server at a time. If you had a primary with three read replicas, you'd get four separate sets of index recommendations, each treating the server workload in isolation. This works for single instance or standalone systems, but it creates problems for scaled out Postgres clusters with replicas that receive read queries. An unused index on your primary might be critical for queries running on your replicas. And…

How we used pg_query to rewrite queries to fix bad query plans

06 October, 2025

Rewriting SQL queries programmatically is harder than it looks. As a human, adding an extra AND condition to a WHERE clause is simple enough. But doing the same thing in code quickly gets complicated. You might try regex, but the real difficulty is coming up with a pattern that works for every variation of a query. AI could generate plausible rewrites, but it's hard to guarantee correctness. These rewrites may look valid, but SQL has many subtle corner cases, so it's difficult to prove that the…

Introducing pganalyze Query Advisor: Proactive Query Plan Optimization for Postgres

29 September, 2025

Postgres teams know the pain of one slow query snowballing into latency or downtime. Some have experimented with GenAI analysis, but it is too expensive to run continuously in the background. Others rely on general monitoring tools, but those cannot detect Postgres-specific patterns in query plans. What’s missing is a purpose-built, cost-effective solution that understands Postgres deeply and gives you actionable guidance. At pganalyze we create purpose-built systems that address common Postgres…

Comparing EXPLAIN Plans is hard (and how pganalyze does it)

06 February, 2025

The Postgres EXPLAIN command is invaluable when trying to understand query performance. SQL is a declarative language, and the Postgres query planner will decide the most efficient way to execute a query. However, plan selection is based on statistics, configuration settings, and heuristics—not a crystal ball. Sometimes there's a substantial gap between what the planner thinks is most efficient and reality. In those situations, EXPLAIN can help Postgres users understand the planner's "reasoning…

Introducing Query Tuning Workbooks: Safely Tune Postgres Queries on Production with pganalyze

08 January, 2025

At some point, every engineering team finds itself grappling with the complexity of query optimization. One query may run perfectly well for a particular customer’s parameters, yet degrade performance for another’s dataset. A small tweak that improves latency in staging might have unforeseen consequences in production. Until now, the standard approach to experimentation—perhaps running in a sandbox environment, copying and pasting results into an editor—hasn’t given developers or DBAs the full…

Introducing Postgres Plan Statistics in pganalyze for Amazon Aurora

21 November, 2024

At pganalyze we've offered query performance monitoring of Postgres databases for many years now, helping companies at scale ensure their Postgres database is performant and queries are as fast as possible. One common story we hear when it comes to analyzing Postgres performance, and identifying the root cause of slowdowns is: Has my query plan changed? Recently Amazon Aurora, the highly scalable AWS PostgreSQL service, has made execution plan data more readily available by introducing , a…

Introducing pganalyze Index Advisor 3.0 - A workload-aware system for finding missing indexes in Postgres

29 February, 2024

Indexing your database across a diverse set of queries across an entire table can be a challenge. You need to determine what indexes might be missing, how new indexes will interact with existing ones, and the overall impact on the specific workload. Continuously adapting indexes is difficult and time consuming; you might deal with a large number of tables, queries, and existing indexes, each with its own characteristics. For example, balancing the need for indexes against the potential downsides…

An automatic indexing system for Postgres: How we built the pganalyze Indexing Engine

01 June, 2022

Indexing is hard. Most people who have worked with databases will agree that regardless of how you do it, creating the right indexes on your database requires thought and detailed analysis. It's not something to be taken lightly. If you don't create the right indexes, your queries will be slow, and if you create too many indexes, your writes on busy tables will be slowed down significantly. But even if you know how to do it, indexing takes a lot of effort. And this has gotten more complex with…

A balanced approach to automatic Postgres indexing: The new version of the pganalyze Index Advisor

01 June, 2022

Historically, index creation, tuning and maintenance has been the task of database administrators who had a detailed understanding of the different queries used by applications. The fast-moving pace of modern application development, combined with a move to the cloud, has shifted the responsibility of indexing to application developers - without giving them the right tools. Application developers today spend a lot of time manually creating indexes for their Postgres queries, reviewing database…

The Unexpected Find That Freed 20GB of Unused Index Space

04 May, 2022

How to free space without dropping indexes or deleting data Every few months we get an alert from our database monitoring to warn us that we are about to run out of space. Usually we just provision more storage and forget about it, but this time we were under quarantine, and the system in question was under less load than usual. We thought this is a good opportunity to do some cleanups that would otherwise be much more challenging. To start from the end, we ended up freeing more than 70GB of un…

How Postgres Chooses Which Index To Use For A Query

01 April, 2022

Using Postgres sometimes feels like magic. But sometimes the magic is too much, such as when you are trying to understand the reason behind a seemingly bad Postgres query plan. I've often times found myself in a situation where I asked myself: "Postgres, what are you thinking?". Staring at an EXPLAIN plan, seeing a , and being puzzled as to why Postgres isn't doing what I am expecting. This has led me down the path of reading the Postgres source, in search for answers. Why is Postgres choosing a…

Understanding Postgres GIN Indexes: The Good and the Bad

02 December, 2021

Adding, tuning and removing indexes is an essential part of maintaining an application that uses a database. Oftentimes, our applications rely on sophisticated database features and data types, such as JSONB, array types or full text search in Postgres. A simple B-tree index does not work in such situations, for example to index a JSONB column. Instead, we need to look beyond, to GIN indexes. Almost 15 years ago to the dot, GIN indexes were added in Postgres 8.2, and they have since become an…

A better way to index your Postgres database: pganalyze Index Advisor

23 September, 2021

When you run an application with a relational database attached, you will no doubt have encountered this question: Which indexes should I create? For some of us, indexing comes naturally, and B-tree, GIN and GIST are words of everyday use. And for some of us it’s more challenging to find out which index to create, taking a lot of time to get right. But what unites us is that creating and tweaking indexes is part of our job when we use a relational database such as Postgres in production. We need…

Using Postgres CREATE INDEX: Understanding operator classes, index types & more

12 August, 2021

Most developers working with databases know the challenge: New code gets deployed to production, and suddenly the application is slow. We investigate, look at our APM tools and our database monitoring, and we find out that the new code caused a new query to be issued. We investigate further, and discover the query is not able to use an index. But what makes an index usable by a query, and how can we add the right index in Postgres? In this post we’ll look at the practical aspects of using the…

Introducing Automated Postgres EXPLAIN Visualization & Insights

16 December, 2019

Today, we’re excited to introduce you to the next evolution of pganalyze. We updated our logo and overall brand, worked on our documentation to help you understand Postgres and its internals better and, most importantly, we’re proud to announce a new key feature on our platform: Automated EXPLAIN Visualization & Insights. Automatic Collection of Query Plans Automatic Visualization of Postgres EXPLAIN Plans pganalyze EXPLAIN Insights The new pganalyze brand Offering this functionality to you is a…