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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
