Welcome! This month, we’re focusing on how to make query optimization in Postgres both proactive and practical. We’ve released pganalyze Query Advisor, a new feature that automatically detects inefficient query patterns and helps you understand the why behind them.
We also take you behind the scenes in how we used pg_query to rewrite queries, showing how our open source parser lets us safely transform SQL without breaking semantics.
And if you’re catching up on what’s new in PostgreSQL 18, our Hands-on Postgres 18 webinar recording is available and walks through async I/O, B-tree Skip Scan, and other performance-focused improvements.
All the best,
Lukas
New Feature
Introducing pganalyze Query Advisor: Proactive Query Plan Optimization for Postgres
Postgres teams know the pain of one slow query snowballing into latency or downtime. GenAI analysis is too expensive to run continuously, and general monitoring tools can’t detect Postgres-specific plan issues. What’s been missing is a purpose-built, cost-effective solution that understands Postgres deeply and gives you actionable guidance.
Query Advisor continuously analyzes EXPLAIN plans, detects problematic patterns, and provides clear query rewrites you can test and apply with confidence. By identifying inefficient nested loops, poor index usage, and other anti-patterns automatically, it helps you fix performance problems before they affect production.
How We Used pg_query to Rewrite Queries to Fix Bad Query Plans
Rewriting SQL programmatically sounds simple—but doing it safely is surprisingly complex. Regex-based rewrites miss edge cases, and AI-generated fixes can’t guarantee correctness. As we built Query Advisor, we needed a way to suggest alternative query patterns without changing semantics.
Using our open source pg_query library, we can parse queries into syntax trees, adjust them at the tree level, and regenerate valid SQL deterministically. The latest update adds pretty-printing options, making rewrites easier to read and reason about. In this post, we show how to apply pg_query to real-world examples where query rewrites lead to better plans.
Hands on Postgres 18: Async I/O, B-tree Skip Scan, UUIDv7, and More
With the release of PostgreSQL 18, performance takes another big leap forward. The new asynchronous I/O subsystem changes how Postgres interacts with disk in both cloud and on-prem environments, and B-tree Skip Scan improves index efficiency for selective queries.
Other updates include planner optimizations, UUIDv7 support, and new EXPLAIN and statistics improvements that make tuning production workloads easier. Catch up on what’s new and see these features in action in our latest webinar.
The team had a great time at PGConf NYC, and we're excited to be at PGConf EU in Riga (Oct. 21-24) next. After that, join us at the PASS Data Community Summit in Seattle (Nov. 17 – 21) for more conversations and a pre-conference workshop. And for the first time ever, pganalyze will be at AWS re:Invent in Las Vegas (Dec. 1 – 5). We’re excited to connect with the broader cloud community and highlight how Postgres performance tuning fits into modern AWS workloads.
Proactive query plan optimization for Postgres is now generally available
Query Advisor continuously analyzes EXPLAIN plans in the background to detect problematic patterns and provide actionable query rewrites. Detected issues appear in the Query Advisor landing page, Workbooks, and query detail views
Index Advisor
Improve support of tables with many scans or columns
When a table has many scans or columns, the number of possible indexes to consider can be very large, which was causing OOM and prevented the Index Advisor from running on such tables.
This change improves memory usage and increases the support for these cases.
Note: There are still tables with a high number of scans or columns that we are not able to run the Index Advisor on, and we continue to work on supporting more of them.
Alerts & Check-ups
System / Replication: Improve loading performance of replication follower list
Improve "Replication: High Replication Lag" check-up
This adds a graph of the replication lag to the Issue page, and clarifies which IP address the affected replica is using, instead of naming the follower by internal UUID.
Workbooks
Add query tuning parameters to autocomplete list
Bug Fixes & Other Improvements
Update the sidebar insight listing for the Grid view in EXPLAIN plan views
Update overview of Log Insights
Use consistent colors for each category
Give more space to classification labels so they are readable without hovering
Drop classification codes to give the labels more space (the codes still show on hover)
Allow collapsing categories, and collapse some categories by default (the selection will persist)
