Postgres: Consider enabling planning time for queries in pg_stat_statements
Now, with Postgres 14, we have total_plan_time
in pg_stat_statements, but it doesn't have data yet:
gitlabhq_production=# \! hostname
patroni-main-v14-101-db-gprd
gitlabhq_production=# select (select extract(epoch from (now() - stats_reset)) from pg_stat_statements_info),
sum(total_exec_time),
sum(total_plan_time)
from pg_stat_statements;
extract | sum | sum
----------------+-------------------+-----
2992174.821444 | 5281093638.660841 | 0
(1 row)
To have this metric, we need to enable pg_stat_statements.track_planning
(docs).
However, we should understand the overhead of doing this (benchmark).
The benefit of having this metric is high - we would understand which queries suffer from long planning time, which might be significant for many queries, especially those involving tables with many indexes.
Another motivation of having this enabled: in some cases, it might be tempting to think that optimization targeted on planning time reduction is promising, while it is not the case for queries with high execution time – to understand, where to put efforts, we should have metrics.
Edited by Nikolay Samokhvalov