On Mon, Mar 4, 2024 at 12:23 AM veem v <veema0...@gmail.com> 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. https://www.postgresql.org/docs/current/auto-explain.html https://www.postgresql.org/docs/current/pgstatstatements.html > 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: https://www.postgresql.org/docs/current/predefined-roles.html Cheers, Greg
