Welcome to the February 2024 edition of the pganalyze newsletter!
In this newsletter you can find all the details about the just launched pganalyze Index Advisor 3.0, which is tailored to each of your table's workloads, and delivers customizable index recommendations that take important Postgres-specific functionality such as HOT updates and extended statistics into account.
As always, you will also find other product updates, recent podcast conversations, and all recent "5mins of Postgres" episodes in this newsletter.
All the best,
Lukas
Introducing pganalyze Index Advisor 3.0 - A workload-aware system for finding missing indexes in Postgres
We’re excited to introduce version 3.0 of the pganalyze Index Advisor! The new Index Advisor features our new constraint programming model for Index Selection, workload-specific configuration depending on the number of reads and writes on a given table, and more.
This new release also features a new user interface that emphasizes the ability to understand and debug why Index Advisor recommends creating a particular index, and which other indexes were considered. In the new UI we've also refined how index recommendations are shown on the "Index Advisor" page for each query, to make suggestions easier to understand.
Docker and DevOps: Postgres in Containers - Why (not)? → (65min) Last month, Lukas joined Bret Fisher and Nirmal Mehta to talk about Postgres in Kubernetes, why the CloudNativePG operator brings a lot of interesting concepts to the table, and what to consider when choosing which operator to use.
We talk about the planner improvements in Postgres 16, including better use of Incremental Sort, more efficient anti-JOINs, Hash Join improvements, more efficient window functions, and more. And all of that without having to change your queries!
We talk about how Postgres handles situations where planner statistics are out of date. We look at the logic for deciding when the planner goes to the actual indexes on a table to get the maximum value using the function, and performance implications of this approach.
We look at optimizing the performance of COUNT statements when only a subset of the data needs to be counted, through use of a LIMIT in a sub-SELECT, or what I like to call a "limited COUNT". We also discuss how this can be represented effectively in a web application.
We discuss a recently committed change to the Postgres 17 development branch that allows configuring the size of the Postgres SLRU caches, as well as improvements to LWLock contention on SLRU related locks. We review the background on what SLRU caches do in Postgres, and historic performance challenges experienced at scale.
Subscribe on YouTube
We publish weekly videos on Postgres, our conference talks, tutorials, product walk-throughs, and more.
Indexing Engine: Use extended statistics when estimating index costs
In certain workloads you might have queries like "WHERE employee_id = $1 AND department_id = $2", in which one column has a functional dependency on the other column (e.g. for a given employee_id, your table will always contain a specific department_id)
For such WHERE clauses, Index Advisor would sometimes suggest a multi-column index like "(employee_id, department_id)", since that results in a lower cost estimate
To help address these cases, Index Advisor will now take extended statistics into account, avoiding incorrect multi-column recommendations
Note this requires CREATE STATISTICS on the table within your database system, as well as pganalyze collector 0.53.0 or newer
Indexing Engine: Fix handling of JOIN conditions when a parameterized index scan is unlikely
Previously Index Advisor would treat a JOIN condition as being able to use an index, even if a parameterized index scan was unlikely
With this change, it's less likely that indexes will be recommended on JOIN conditions, as they will only be considered when the generic query plan (that pganalyze calculates) contains a Nested Loop with the relevant table on the inner side, allowing a parameterized index scan
API users, please note: If you are integrating with the getIssues API, please be aware that the new Index Advisor 3.0 issues are available under the new "index_advisor/indexing_engine" checkname. The old "index_advisor/missing_index" issues are still available in the API, but will be archived at the end of March 2024.
Bugfix: Skip collecting extended statistics for Postgres 11 and below
This resolves an issue that was introduced in the last release (0.53.0) that caused missing schema information when running on very old Postgres versions
Other improvements and bugfixes
Connections page: Increase resolution of graphs when zooming in
Schema Statistics: Add extended statistics information under "Columns"
Starting from the collector v0.53.0, the extended statistics information is tracked and used for Index Advisor recommendations to detect functional dependencies
You can now see the created extended statistics objects in the corresponding table's Schema Statistics "Columns" page
Alerts & Check-Up:
Show 30 days of history for resolved alerts, up from 7 days
Sort resolved issues by resolved timestamp descending by default
Review us on G2
Please leave your feedback (good or bad!) in this survey - we appreciate it!
