Re: Monitoring query plan cache

Thanks for your reply, I have a follow-up question:
>> 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.)
>
Below is an example output from log_planner_stats:

LOG: PLANNER STATISTICS
DETAIL: ! system usage stats:
! 0.000132 elapsed 0.000000 user 0.000000 system sec
! [0.181972 user 0.052991 sys total]
! 0/0 [0/248] filesystem blocks in/out
! 0/0 [0/2705] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [1/4249] voluntary/involuntary context switches

How can we tell from this whether the query planner used a cached plan?
Is the example above a hit or a miss?

>> 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.

It is not always easy to tell the query type (function, prepared or
ad-hoc.) We use Python in mod_wsgi with psycopg2. The code shows ad-hoc
SQL, but who knows what the many layers between Python and the database do.

As suggested by Erwin Brandstetter on dba.stackexchange.com, I ran a
test with:

cursor.execute("""
load 'auto_explain';
set auto_explain.log_min_duration = 0;
set auto_explain.log_nested_statements = on;
select col1 from table1 where id = %(id)s
""", {'id': 123});
print(curs.fetchone()[0]);

The logging doesn't look like a cached plan, you can see the 123 value
but not a parameter like $1. This suggests Postgres was previously
compiling around 200 queries a second on our production machine. Is that
even possible?

Cheers,
Andomar