Skip to main content

         This documentation site is for previous versions. Visit our new documentation site for current releases.      

This content has been archived and is no longer being updated.

Links may not function; however, this content may be relevant to outdated versions of the product.

Create a database index to improve performance of searches

Updated on September 13, 2021


A developer asks: The search gadget "Search For Work", defined on the Process Work space of the User portal, includes queries with some of our site-specific fields to use in searching for cases.  Do those fields automatically get indexed somehow when they get created and added to the Select Lists?

If not, is it recommended that we create indexes to improve query performance and reduce impact to overall system?  The same question applies to the standard "Find" gadget. We are concerned with any type of querying and/or reporting and the performance impact on the system. 


Suggested Approach

You are correct to be sensitive to the performance implications of reporting. 

When you expose additional properties as database columns, they can be used as search criteria. However, Process Commander does not automatically index every exposed column.

Fields used in the reports for criteria or display do not need to be indexed as long as they are exposed as database columns.  Nonetheless, it is a best practice to analyze which fields will be used most in your queries and work with your DBAs to add indexes to optimize performance of the generated SQL query.  Columns/properties that are used in WHERE and ORDER BY clauses are frequently good candidates for such indexing.   

Note that if you run a report and you wish to determine whether all the properties in the report are exposed as database columns, you can examine the clipboard's Code-Pega-List page that results from the query . If the SQL includes: 

"select * from..."  (in releases prior to V42SP5)


“Select pzPVStream from …”  (beginning from V42SP5)

then the blob column is accessed. Use of the asterisk or pzPvStream, respectively, indicates that at least one of the properties referenced in the report is not exposed as a database column (and therefore the blob is being queried).

  • Previous topic Configuring the number of database transaction retries
  • Next topic Effect of search encryption on performance


Pega Platform 7.1.1 - 8.3.1 System Architect Lead System Architect Business Architect Experience Designer Pega Delivery Leader System/Cloud Ops Administrator System Administration Reporting Financial Services Healthcare and Life Sciences Insurance Communications and Media Government Healthcare and Life Sciences Consumer Services Consumer Services Manufacturing Consumer Services

Have a question? Get answers now.

Visit the Support Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best. is not optimized for Internet Explorer. For the optimal experience, please use:

Close Deprecation Notice
Contact us