Re: Monitoring query plan cache

Andomar <andomar(at)aule(dot)net> writes:
> 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?

If you're seeing that output then planning happened. The only way you get
a cached plan for a client-issued SQL statement is if the client uses
PREPARE/EXECUTE or the "extended query" protocol (parse/bind/execute).
Neither of those cases would go through here.

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

Well, at 132 microseconds apiece, it does not seem from this example that
repeat planning is a huge problem for you ... of course, some of your
statements might take longer, but you've not demonstrated here that you
have anything to worry about.

regards, tom lane