I have ~3.5M work objects in Pega, each containing a variable‑length embedded Page List. I need to apply search/filter criteria that match the corresponding fields in any item of the embedded list across all work objects.
I currently have a Report Definition on an Index‑ class built on the work object. I am using a class join between the Index‑ class (via pxInsIndexedKey) and the embedded list items (via pxInsIndexedKey) to apply my search criteria. I’m concerned that this approach will cause performance issues when we are searching across so many work objects and was wondering if:
My approach would meaningfully improve performance or if it is still likely to become a bottleneck.
And whether there is a better or more scalable design pattern for handling this type of requirement in Pega.
From my understanding, using an Index- class with joins on pxInsIndexedKey is a valid approach, but at ~3.5M records it can still become a performance bottleneck, especially with large/variable page lists and frequent queries.
What you can try:
Query directly on the Index class instead of joining with Work
Ensure proper DB indexes on frequently searched fields
Run the explain query to identify the missing indexes and cost factor. If you’re on Pega Cloud, work with Pega cloud team to add the necessary indexes or work with your DBAs to add the necessary indexes and fine tune the query based on DBA recommendations.