Re: When manual analyze is needed

On Mon, Mar 4, 2024 at 12:23 AM veem v <veema0000(at)gmail(dot)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