Hello All,
I am using the latest 8.2 source that I compiled with Sun Studio 11 and
tested it on Solaris 10 11/06 against an app server. I find that the CPU
utilization was higher than I expected and started digging through it.
Aparently the top CPU usage comes from the following stack trace which
is roughly about 10-15% of the total the postgresql uses.
Anyway a real developer might make more sense from this than I can
libc_psr.so.1`memcpy+0x524
postgres`SearchCatCache+0x24
postgres`getBaseType+0x2c
postgres`find_coercion_pathway+0x14
postgres`can_coerce_type+0x58
postgres`func_match_argtypes+0x24
postgres`oper_select_candidate+0x14
postgres`make_op+0x1a8
postgres`transformAExprAnd+0x1c
postgres`transformWhereClause+0x18
postgres`transformUpdateStmt+0x94
postgres`transformStmt+0x1dc
postgres`do_parse_analyze+0x18
postgres`parse_analyze_varparams+0x30
postgres`exec_parse_message+0x2fc
postgres`PostgresMain+0x117c
postgres`BackendRun+0x248
postgres`BackendStartup+0xf4
postgres`ServerLoop+0x4c8
postgres`PostmasterMain+0xca0
FUNCTION COUNT PCNT
postgres`can_coerce_type 1 0.1%
postgres`find_coercion_pathway 11 0.9%
postgres`SearchCatCache 43 3.4%
libc_psr.so.1`memcpy 136 10.6%
The appserver is basically using bunch of prepared statements that the
server should be executing directly without doing the parsing again.
Since it is the parser module that invokes the catalog search, does
anybody know how to improve the can_coerce_type function in order to
reduce the similar comparisions again and again for same type of statements.
I also wanted to check if postgresql stores prepared statements at the
server level or does it parse each incoming prepared statement again?
Any insight will help here in understanding what it is attempting to do
and what can be the possible workarounds.
Regards,
Jignesh
