Welcome to the June 2024 edition of the pganalyze newsletter!
In this month's newsletter you can find a recap and recording of our successful webinar on optimizing Postgres queries (thanks to all who joined!), the full webinar slides and Q&A, as well as our talk on query optimization we recently gave at North Bay Python.
If you're interested in Constraint Programming (CP), we just published a practical introduction blog post to using CP-SAT in Python that explains how to use CP models in your own code, for your own optimization problems. In a follow-up post we'll walk through how to utilize CP models for solving index selection in Postgres.
And if you're utilizing pganalyze Enterprise Server, we released a new patch release, v2024.04.3, that contains a handful of important bugfixes (see below), upgrading is recommended.
As always, you will also find other product updates and all other recent "5mins of Postgres" episodes in this newsletter.
All the best,
Lukas
Blog post: A practical introduction to Constraint Programming using CP-SAT and Python
But how can we utilize Constraint Programming (CP) for other programming problems, and understand more about its mechanisms in practice? In this blog post Philippe Olivier, principal author behind our CP model for Postgres Index Selection, walks through a simple scheduling example, utilizing the CP-SAT solver and Python.
If you're interested how to use CP in your own projects, this is the post to read! And if you're interested in more examples of how this works for index selection in Postgres, look out for part 2 of this series, coming later in July.
Webinar recording: Optimizing slow queries with EXPLAIN to fix bad query plans
Our webinar two weeks ago was a big success! With over 500 live attendees, we were excited to walk through how to optimize Postgres queries by understanding the choices the Postgres planner makes, and which ways exist to influence query plans in the right direction.
We ended up having over 70 questions during the Q&A, some of which were answered live and you can find in the recording, and many more that we answered afterwards and provided in the slide deck at the end.
And if you're interested in a shorter version focused on the essentials, we gave a 25min talk at North Bay Python last weekend, that talked about many of the same concepts.
We discuss two changes in the upcoming Postgres 17 release that improve query plans for queries that involve CTEs. This can improve query plans where you would see an explicit CTE scan, due to use of the MATERIALIZED keyword, or because Postgres wasn't able to pull up a query to the upper plan level.
We discuss tuning the Postgres work_mem setting for your workload, and why it can be quite confusing to interpret the meaning of work_mem correctly for a given query. We also discuss the impact of hash_mem_multiplier, and why it's default changed from 1.0 to 2.0 in Postgres 15.
Subscribe on YouTube
We publish weekly videos on Postgres, our conference talks, tutorials, product walk-throughs, and more.
Show Primary or Replica indication badge in the server list
In the server list of the Organization Dashboard page or in the list or server selection dropdown, a badge is added to indicate wether the server is primary or replica.
Add EXPLAIN annotation for inefficient Nested Loops
This flags Nested Loops explicitly that had an estimated row count of "1", but ended up having 1000 or more actual rows, oftentimes indicating that an alternate join method (Hash or Merge Join) might have been utilized if the row estimate during planning were correct.
Alert Policy: Enhanced Email notifications
Email notifications are now sent by default for all critical severity issues. Previously, some critical issues did not trigger email notifications due to an oversight. This has been corrected.
Several checks were missing from the Alert Policy page, which prevented email notifications for those checks. This update includes all checks on the Alert Policy page, making it possible to enable email notifications for every critical severity issue.
Per-table VACUUM information:
Fix rendering bug when autovacuum_vacuum_insert_threshold is 0
Per-table VACUUM information: Add pagination to vacuum/analyze activity list
Blocking queries issue page: Ensure link to Connections page is correctly shown
Review us on G2
Please leave your feedback (good or bad!) in this survey - we appreciate it!
