One fact that not many people know about PostgreSQL is that it does hard parsing for every SQL statement that hits the database. It doesn't matter if the same query has millions of repeated executions. Except when using prepared statements, the lowest cost-based execution plan is chosen each time a SQL query is executed. It does not matter if it is the same SQL query that has already been executed several times. This is because there is no such plan table that stores execution plans and is reusable. This may bring some challenges at times. While it is great that PostgreSQL finds it optimal to prepare a plan each time, a plan may change if statistics are not intact and there is no way to keep track of such abnormal changes.
For this reason, PostgreSQL provides a module called auto_explain that helps to log execution plans of a query that runs for more than a certain time. In this recipe, we shall discuss auto_explain...