performance tests on processes that are defined with sub-processes reveals that the existing 'ProcessInstancesWaitingForEvent' named query quickly becomes a performance bottleneck.

Specifically, performance tests have been conducted on jbpm5 (git hub commit #:  129b5b2d2f1e8173f16be6a8df8a2d06daf4b22d ) running on RHEL 5.5 with two CPUs with latest PostgreSQL available for that OS (PostgreSQL 8.4).  Performance tests revealed that postgresql processes quickly pegged CPU utilization at 90-95%.

As an alternative, the following modification to the 'ProcessInstancesWaitingForEvent' named query completely stabilized our test results by bringing PostgreSQL CPU utilization back down to 15-25% :



<named-query name="ProcessInstancesWaitingForEvent">
        <query>
  select
        processInstanceInfo.processInstanceId
  from
        ProcessInstanceInfo processInstanceInfo join    processInstanceInfo.eventTypes eventTypes
where
        eventTypes=:type
        </query>
</named-query>