Using Query Inspector to improve SQL queries

You can use Query Inspector to generate an EXPLAIN PLAN to help tune a SELECT SQL query for your internal Pega Platform databases on Pega Cloud. An EXPLAIN PLAN is the internal sequence of steps that the SQL query uses to tune the SQL query, which is useful for improving query efficiency. Use the Query Inspector to enter or paste an SQL query and generate an EXPLAIN PLAN.

Before you begin: You need the PegaRULES:DatabaseAdministrator role to access the Query Inspector. For more information, see Adding a role to an access group.
Note: Pega Cloud Services provides Query Runner, Query Inspector, and Schema Tools in Pega Platform for clients to manage their database. These database tools are not available to on-premises clients.
The EXPLAIN PLAN output organizes actions taken by the SELECT statement in order of execution sequence. An explain plan outputs the following:
  • The operation taken.
  • The name of the table on which the SELECT statement performed the operation.
  • The number of rows on which the SELECT statement performed the operation.
  • The cost (CPU usage) of the SELECT statement execution.
  • The length of the rows upon which the SELECT statement executed upon.
The types of operations for SELECT statements include the following.
  • FULL Table scans that read the entire table.
  • JOIN methods that explains the sequence of tables joined together.
  • Nested loops that assesses rows in a table more efficiently based on the assessment of a previous table.
  • INDEX SCAN and SEQUENCE SCANS that pull from multiple values within a table.
  1. In the header of Dev Studio, click Configure > System > Database > Query Inspector.
  2. Select the database from the Choose your database list.
  3. Enter or paste an SQL query into the Enter or paste statement to evaluate here field.
  4. Click Generate explain plan.