Last week I posted about how we often don’t pick the optimal plan. I got asked about difficulties when trying to reproduce my results, so I’ll address that first (I forgot to mention a couple details). I also got questions about how to best spot this issue, and ways to mitigate this. I’ll discuss that too, although I don’t have any great solutions, but I’ll briefly discuss a couple possible planner/executor improvements that might allow handling this better.
The basic promise of a query optimizer is that it picks the “optimal” query plan. But there’s a catch - the plan selection relies on cost estimates, calculated from selectivity estimates and cost of basic resources (I/O, CPU, …). So the question is, how often do we actually pick the “fastest” plan? And the truth is we actually make mistakes quite often.
Consider the following chart, with durations of a simple SELECT query with a range condition. The condition...
I do a fair number of benchmarks, not only to validate patches, but also to find interesting (suspicious) stuff to improve. It’s an important part of my development workflow. And it’s fun ;-) But we’re dealing with complex systems (hardware, OS, DB, application), and that brings challenges. Every now and then I run into something that I don’t quite understand.
Consider a read-only pgbench, the simplest workload there is, with a single SELECT doing lookup by PK. If you do ...
The pgconf.dev conference, a revamp of the original PGCon, happened about two weeks ago. It’s the main event for Postgres developers, and one of the things we’re trying is an Advanced Patch Feedback Session (APFS).
We first tried that last year in Vancouver, and then again in Montreal. But I realized many people attending the conference either are not aware of the event at all, or are not sure what it’s about. So let me explain, and share some reflections from this year.
We’re now in the “feature freeze” phase of Postgres 18 development. That means no new features will get in - only bugfixes and cleanups of already committed changes. The goal is to test and stabilize the code before a release. PG 18 beta1 was released a couple days ago, so it’s a perfect time to do some testing and benchmarking.
One of the fundamental changes in PG 18 is going to be support for asynchronous I/O. And with beta1 out, it’s the right time to run your tests and benchmarks to test this new feature. Both for correctness and regression.
