Configuration options and troubleshooting for PostgreSQL table and index advisors

See the following sections to configure DPA to generate table and index advisors.

Specify which PostgreSQL databases DPA collects plans from

By default, DPA collects plans from all databases. If you do not want to use plans from all databases, you can specify a list of databases to include or to exclude.

1. Set the advanced option POSTGRESQL_PLAN_COLLECTION_DB_NAMES to specify the list of databases you want to include or exclude.

2. Set the advanced option POSTGRESQL_PLAN_COLLECTION_DB_NAMES_FILTER to specify whether you want to include or exclude the list of databases.

These options can be set at both the global level and the monitored instance level. If they are set at the instance level, DPA considers both the global and instance-level values. If the values do not conflict, DPA applies all values. For example:

Global settings	Monitored instance settings	Result
Not Set	Include A, B	For monitored instances with the instance-level settings, DPA collects plans from A and B. For all other monitored instances, DPA collects plans from all databases.
Include A, B, C	Include D	For monitored instances with the instance-level settings, DPA collects plans from A, B, C, and D. For all other monitored instances, DPA collects plans from A, B, and C.
Exclude A, B, C	Exclude D, E, F	For monitored instances with the instance-level settings, DPA collects plans from all databases except A, B, C, D, E, and F. For all other monitored instances, DPA collects plans from all databases except A, B, and C.

When the global setting and the instance-level setting have different specifications for a specific database, the instance-level setting overrides the global setting for the specified instance. Otherwise, the global settings apply. For example:

Global setting	Monitored instance settings	Result
Include A, B, C	Exclude C	For monitored instances with the instance-level settings, DPA collects plans from A and B. For all other monitored instances, DPA collects plans from A, B, and C.
Exclude A, B, C	Include C	For monitored instances with the instance-level settings, DPA collects plans from all databases except A and B. For all other monitored instances, DPA collects plans from all databases except A, B, and C.

Troubleshooting PostgreSQL table and index advisors

Execution plans are missing for some SQL statements

To provide table and index advisors, DPA needs the execution plans that PostgreSQL generates. To determine if DPA is able to get the plan for a SQL statement, search for the SQL statement and get the SQL hash value. Then run the following query:

select * from CONPPT_ where sqlhash = 'SQLHash'

If no values are returned, check for the following issues:

• The DPA monitoring user does not have the required privileges on tables where the SQL statement runs.

  Check the idc.log file and look for the following message: 

  Failed to collect query plans due to: ERROR: permission denied for table TableName.

  Resolution: To grant the required privileges, see the instructions for creating the monitoring user.

• The SQL statement is truncated in the pg_stat_activity.query field.

  PostgreSQL stores currently running SQL statements in the pg_stat_activity.query field. If there is not enough memory allocated to store the entire SQL statement, it truncates the query. DPA cannot collect the execution plan for a truncated SQL statement. By default, PostgreSQL reserves 1024 bytes of memory.

  Resolution: To avoid truncated queries, DPA recommends increasing the amount of reserved memory to 4096 bytes. To increase the reserved memory, modify the track_activity_query_size value as described in the instructions for configuring each database instance.

Index Bloat Metrics value is Unknown

On the Table Tuning Advisor page, the Index Bloat Metrics value is Unknown if the pgstattuple extension is not enabled on the database.

To enable the pgstattuple extension, connect to the database and run the following command:

CREATE EXTENSION pgstattuple;