Table and index scan statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to collect and aggregate statistics related to table and index scans from PostgreSQL databases.
• Why: This job is critical to track database activity and performance efficiently, identifying areas that might benefit from optimization such as indexing strategies or query improvements. By analyzing scan statistics, the job helps in understanding the system’s interaction with the data, which is crucial for maintaining optimal performance and resource usage.
• Manual checking: You can manually verify the statistics collected by this job directly from the PostgreSQL system views by executing the following SQL command:

SELECT * FROM pg_stat_all_tables;

Job Details

• Job Name: Table and index scan statistics
• Description: The task gathers scan statistics on tables and indexes from the `pg_stat_all_tables` view.

Scans monitored include:

• Sequential scans (Number of sequential scans initiated on this table)
• Live rows fetched by sequential scans (The count of live rows fetched by these scans)
• Index scans (Number of index-based scans initiated on this table)
• Live rows fetched by index scans (The count of live rows fetched through index scans)

Implementation Details

A series of operations involving the creation and maintenance of historical statistics tables and the function `dbw_scan_stat_func` which calculates differences between current and previous statistics records:

• create table `dbw_scan_base`
• create table `dbw_scan_histr`
• create table `dbw_scan_last`
• create or replace function `dbw_scan_stat_func`

Dependencies

• The task depends on the proper functioning and existence of its own supporting tables (`dbw_scan_stat_func`, `dbw_scan_histr`, `dbw_scan_base`, `dbw_scan_last`) and will also clean up after itself upon failure.

Reporting and Statistics

• Results are reported and visualized primarily through two detailed charts and reports:
• Top 10 tables with the most sequential scans and index scans in the last 24 hours.

Key Presentations Include:

• “Top 10 tables with most sequential scans last 24 hours”
• “Top 10 tables with most index scans last 24 hours”
• “Sequential scans history”
• “Index scans history”

Each report includes detailed statistics concerning scan counts and fetched rows, ordered by the highest activity.

Execution Notes

• Default Schedule: The task is scheduled to run at minute 15 and 45 of every hour.
• SQL Implementation: The principal SQL function `dbw_scan_stat_func` includes logic for capturing new data, updating old records, and deleting outdated data based on a period defined by the ‘keep data for’ parameter.

This monitoring job highlighted above is crucial for proactive database performance monitoring and tuning to ensure efficient data handling within a PostgreSQL environment.