Summary

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)

or

“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).