Re: Monitoring query plan cache

Andomar <andomar(at)aule(dot)net> writes:
> We run a Postgres 9.1 system that handles a lot of identical queries
> (around 500 per second.) The queries originate from a Python WSGI script
> running under Apache.

> Performance is an issue, and we suspect query planning might be an
> underlying cause. I've rewritten the scripts from ad-hoc SQL to a
> Postgres functions (CREATE FUNCTION) and we saw server load go down
> quite a bit.

> Now we'd like to monitor the Postgres query parser, to confirm that it's
> not analyzing the same SQL over and over, and see if we missed other things.

> a) Is it possible that Postgres caches the query plans for psql
> functions, but not for ad-hoc SQL?

plpgsql functions would cache query plans. Ad-hoc SQL doesn't, unless you
explicitly make use of prepared queries.

> c) Can you monitor the query parser as a whole, with stats like
> parses/sec or cache hits/sec?

Possibly log_parser_stats/log_planner_stats/log_statement_stats
would help you. They're pretty old-school though; you'd need to
write some tool that scans the postmaster log and accumulates the
stats to get anything very useful out of those features. (It
could be that somebody's already done that, though --- take a
look at things like pgFouine.)

> d) Is there a way to montior how many reads are done against the
> statistics table (pg_statistics)?

Up to a point. The pg_stats functionality should work just the same for
pg_statistic as for any user table. However, that will only count actual
reads, and there's a per-backend catalog cache that will buffer rows
fetched from pg_statistic. So you can't really use pg_statistic reads as
a proxy for how often planning happened.

regards, tom lane