Thread: Performance of Parser?

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

Jignesh Shah <J.K.Shah@Sun.COM> writes:
> The appserver is basically using bunch of prepared statements that the
> server should be executing directly without doing the parsing again.

Better have another look at that theory, because you're clearly spending
a lot of time in parsing (operator resolution to be specific).  I think
your client code is failing to re-use prepared statements the way you
think it is.

            regards, tom lane

Jignesh Shah wrote:
> The appserver is basically using bunch of prepared statements that the
> server should be executing directly without doing the parsing again.
>

The first thing you need to do is turn on statement logging, if you
haven't already, to verify this statement.

cheers

andrew

On 13-Jan-07, at 7:24 PM, Tom Lane wrote:

> Jignesh Shah <J.K.Shah@Sun.COM> writes:
>> The appserver is basically using bunch of prepared statements that
>> the
>> server should be executing directly without doing the parsing again.
>
> Better have another look at that theory, because you're clearly
> spending
> a lot of time in parsing (operator resolution to be specific).  I
> think
> your client code is failing to re-use prepared statements the way you
> think it is.

This is exactly what is happening. The driver needs to cache
statements for this to work.

Dave
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that
> your
>        message can get through to the mailing list cleanly
>