On Mon, Mar 4, 2024 at 12:23 AM veem v <veema0000@xxxxxxxxx> wrote:
Additionally if a query was working fine but suddenly takes a suboptimal plan because of missing stats , do we have any hash value column on any performance view associated with the queryid which we can refer to see past vs current plans difference and identify such issues quickly and fix it?
You can use auto_explain; nothing else tracks things at that fine a level. You can use pg_stat_statements to track the average and max time for each query. Save and reset periodically to make it more useful.
I am not seeing any such column in pg_stat_activity or pg_stat_statements to hold hash value of the plan and also the query column is showing "<insufficient privilege>" for many of the entries, why so?
Ordinary users are not allowed to see what other people are running. You can add a user to the pg_read_all_stats role to allow this:
GRANT pg_read_all_stats TO alice;
Oftentimes someone needing access to the stats also needs a little more access, so consider the pg_monitor role as well. Both are documented here:
Cheers,
Greg
