3 ms·
The downside is that a PostgreSQL version update can change internal optimizer rules, which may result in the plan being changed to a less performant one again.
by cube2222 5y ago
The downside is that a PostgreSQL version update can change internal optimizer rules, which may result in the plan being changed to a less performant one again.
In that case it's good to have monitoring in place to catch something like that. We're using Datadog Database monitoring which gives you a good breakdown of what the most pricey queries are - it uses information from the pg_stat_statements table, which contains a lot of useful stats about query execution performance.
You should regularly check such statistics to see if there aren't any queries which are unexpectedly consuming a big chunk of your database resources, as that might mean you're missing an index, or a non-optimal query plan has been chosen somewhere.
- topspin 5y ago> The downside is that a PostgreSQL version update can change internal optimizer rules This has actually happened relatively recently. PostgreSQL Common Table Expressions (WITH x as (some query)) used to be optimization fences by default, meaning that you can influence the optimizer using a CTE. This is a well known technique among PostgreSQL users that resort to it for lack of optimizer hints. In Pg12 they enhanced the optimizer to remove the optimization fence by default, so the query plans of existing queries automatically changed and sometimes became much slower as a result. If you want the old CTE behavior you have to modify the WITH clause via AS MATERIALIZED.