Bruce Momjian: Explaining the Postgres Query Optimizer Improvements

I wrote Explaining the Postgres Query Optimizer in 2011, and though I have presented it 22 times, it has changed little over the years. (I created a followup presentation this year.) Periodically, I check my slides to make sure they match the output of the current Postgres version. I did this recently using the file associated with the presentation and found that the EXPLAIN plans for the final LIMIT queries were different. I updated my slides, and then went to find the optimizer improvement responsible for this change. After applying a patch to enable compiling of older branches, I found the commit was from January 20, 2017. What was odd is that the patch added logical replication features, and did not modify the optimizer. Thanks to Andrew Gierth, I discovered that the commit changed my EXPLAIN plans because it increased the number of pg_class rows by 3%, and that changed the costs enough to change the EXPLAIN plans. Continue Reading »