Help Center > > Developer Guide> Setting GUC Parameters> Statistics During the Database Running> Query and Index Statistics Collector

Query and Index Statistics Collector

Updated at: Jul 15, 2020 GMT+08:00

The query and index statistics collector is used to collect statistics during database running. The statistics include the times of inserting and updating a table and an index, the number of disk blocks and tuples, and the time required for the last cleanup and analysis on each table. The statistics can be viewed by querying system view families pg_stats and pg_statistic. The following parameters are used to set the statistics collection feature in the server scope.

track_activities

Parameter description: Collects statistics about the commands that are being executed in session.

Type: SUSET

Value range: Boolean

  • on indicates that the statistics collection function is enabled.
  • off indicates that the statistics collection function is disabled.

Default value: on

track_counts

Parameter description: Collects statistics about data activities.

Type: SUSET

Value range: Boolean

  • on indicates that the statistics collection function is enabled.
  • off indicates that the statistics collection function is disabled.

When the database to be cleaned up is selected from the AutoVacuum automatic cleanup process, the database statistics are required. In this case, the default value is set to on.

Default value: on

track_io_timing

Parameter description: Collects statistics about I/O invoking timing in the database. The I/O timing statistics can be queried by using the pg_stat_database parameter.

Type: SUSET

Value range: Boolean

  • If this parameter is set to on, the collection function is enabled. In this case, the collector repeatedly queries the OS at the current time. As a result, large numbers of costs may occur on some platforms. Therefore, the default value is set to off.
  • off indicates that the statistics collection function is disabled.

Default value: off

track_functions

Parameter description: Collects statistics about invoking times and duration in a function.

Type: SUSET

When the SQL functions are set to inline functions queried by the invoking, these SQL functions cannot be traced no matter these functions are set or not.

Value range: enumerated values

  • pl indicates that only procedural language functions are traced.
  • all indicates that SQL and C language functions are traced.
  • none indicates that the function tracing function is disabled.

Default value: none

update_process_title

Parameter description: Collects statistics updated with a process name each time the server receives a new SQL statement.

The process name can be viewed on Windows task manager by running the ps command.

Type: SUSET

Value range: Boolean

  • on indicates that the statistics collection function is enabled.
  • off indicates that the statistics collection function is disabled.

Default value: off

track_thread_wait_status_interval

Parameter description: Specifies the interval of collecting the thread status information periodically.

Type: SUSET

Value range: 0 to 1 day. The unit is min.

Default value: 30min

enable_save_datachanged_timestamp

Parameter description: Specifies whether to record the time when INSERT, UPDATE, DELETE, or EXCHANGE/TRUNCATE/DROP PARTITION is performed on table data.

Type: USERSET

Value range: Boolean

  • on indicates that the time when an operation is performed on table data will be recorded.
  • off indicates that the time when an operation is performed on table data will not be recorded.

Default value: on

track_sql_count

Parameter description: Specifies whether to collect statistics on the number of the SELECT, INSERT, UPDATE, DELETE, and MERGE INTO statements that are being executed in each session, the response time of the SELECT, INSERT, UPDATE, and DELETE statements, and the number of DDL, DML, and DCL statements.

Type: SUSET

Value range: Boolean

  • on indicates that the statistics collection function is enabled.
  • off indicates that the statistics collection function is disabled.

Default value: off

  • The track_sql_count parameter is restricted by the track_activities parameter when the gs_sql_count or pgxc_sql_count view is queried.
    • If track_activities is set to on and track_sql_count is set to off, a warning message indicating that track_sql_count is disabled will be displayed when the view gs_sql_count, pgxc_sql_count, gs_workload_sql_count, pgxc_workload_sql_count, global_workload_sql_count, gs_workload_sql_elapse_time, pgxc_workload_sql_elapse_time, or global_workload_sql_elapse_time are queried.
    • If both track_activities and track_sql_count are set to off, two logs indicating that track_activities is disabled and track_sql_count is disabled will be displayed when the views are queried.
    • If track_activities is set to off and track_sql_count is set to on, a log indicating that track_activities is disabled will be displayed when the views are queried.
  • If track_sql_count is set to off, querying the gs_sql_count or pgxc_sql_count view returns 0.

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel