Welcome to the May 2024 edition of the pganalyze newsletter!
Our team just got back from PGConf.DEV in Vancouver, the annual Postgres development conference, where the plans are set for the next major Postgres version. We've had many excellent conversations last week, including future observability improvements in Postgres, autovacuum scheduling, tracking memory usage better, and more. I was also excited to share an update on libpg_query, which has existed for almost 10 years, and forms an important component of pganalyze.
Later this month we'll be hosting a free online webinar on optimizing slow queries with EXPLAIN to fix bad query plans. If you want to learn how to go from a slow query to finding the root cause, to identifying a solution, such as creating additional statistics, or tweaking query plans - we'll talk about that, and more. Whether you're a novice or an expert in using EXPLAIN, this webinar will have something useful for you.
And if you're curious what Postgres CVE-2024-4317 is about, we have a 5mins of Postgres episode that talks about the impact, and how to ensure you've applied the new Postgres minor releases correctly.
As always, you will also find product updates and all other recent "5mins of Postgres" episodes in this newsletter.
All the best,
Lukas
Webinar registration: Optimizing slow queries with EXPLAIN to fix bad query plans
The focus of this webinar is to teach the essential skills of optimizing slow queries with EXPLAIN ANALYZE, teach you about anti-patterns that you will encounter when debugging queries, and leave you with practical advice on how to address common problems.
The presentation is geared towards practitioners that work with Postgres, either as an app developer, or as part of the database or infrastructure team.
We discuss the updated Postgres minor releases that recently were released Specifically, we're going to talk about the security issue that was fixed in this release.
We discuss a common challenge when using equality and contains operators for querying JSONB columns, and why the Postgres planner will often get the estimates wrong for such queries, causing bad query plans.
We look at the Postgres 17 beta1 release that came out recently, some highlights of the release, some changes that unfortunately got reverted since the feature freeze, and how to help the community during the beta testing process.
Query details page: Correctly show index insights under "Index Advisor" tab
Due to a bug in how index insights were linked to queries, pganalyze was not showing Index Advisor insights on the query details page (only in the Index Advisor itself).
Add support for pgvector
This now allows queries that reference a schema that has vector data types to be processed correctly by pganalyze
Note that this does not (yet) add support for index suggestions for ivfflat/hnsw index types, but will make B-Tree/GIST index recommendations on queries involving vector operations
Scan details: Indicate whether a parameterized JOIN is expected
This is helpful to be able to understand why Index Advisor ignores JOIN conditions for its costing, as it now does when no parameterized scan is expected.
Other improvements and bugfixes
EXPLAIN Plans: Support all Aggregate nodes with text format explain
Previously, only the plain Aggregate with text format explain was supported. With this change, we support GroupAggregate, HashAggregate, and MixedAggregate better
EXPLAIN Text to JSON/JSON to Text: Support more properties/nodes
Add T_TidRangeScan and TID Cond support
Update rows removed by X behavior
Add proper space with planned partitions
Better memoize support
Support ON CONFLICT better
Add Rows Removed by Filter to JSON format always
Append and Aggregate node support
Explain Insights: Find original node causing a row mis-estimate
This reduces the number of nodes that mis-estimates are flagged on, so that only the root cause of a mis-estimate gets flagged
Review us on G2
Please leave your feedback (good or bad!) in this survey - we appreciate it!
.png?width=1120&upscale=true&name=image%20(8).png)
