A slow SQL statistics method, device and medium for a PostgreSQL database

Abstract

The application discloses a PostgreSQL database slow SQL statistics method, equipment and medium, and belongs to the field of data processing. The method comprises the following steps: adapting a query statement according to a PostgreSQL database version number, collecting snapshot data of a pg_stat_statements view at a preset period of time in a read-only connection; performing differential calculation on a current snapshot and a baseline snapshot stored in a local SQLite to obtain an accumulated execution frequency increment and an accumulated execution time increment of a SQL statement within a statistics period; when the increments are both greater than zero, calculating an average execution time, comparing the average execution time with a preset threshold, determining a slow SQL and generating a record. Through low-frequency collection, differential calculation and local storage, the application realizes low-overhead and high-precision statistics of the slow SQL, effectively reduces database load and storage pressure, and has good version compatibility and operation and maintenance practicability.

Description

Technical Field

[0001] This application relates to the field of data processing, and in particular to a method, device and medium for statistical analysis of slow SQL queries in a PostgreSQL database. Background Technology

[0002] As a mainstream open-source relational database, PostgreSQL is widely used in various business systems. Its SQL execution efficiency directly affects the business response speed, and accurate monitoring and statistics of slow SQL has become a core requirement for database operation and maintenance.

[0003] In existing technologies, pg_stat_activity supports real-time session viewing, providing basic data support for slow SQL analysis. However, pg_stat_activity-based solutions require high-frequency sampling (e.g., once per second), leading to a surge in data volume, excessive storage and computing resource consumption, and a lack of unified identifiers that prevent the merging of similar SQL queries.

[0004] While extensions such as pg_stat_monitor enhance statistical functionality, their inconsistent SQL standardization strategies can easily lead to duplicate statistics of the same logical SQL statements, and they are not adapted to the differences in field naming across different PostgreSQL versions.

[0005] The lightweight and persistent nature of the local embedded database SQLite makes local storage possible, but existing solutions do not fully leverage its advantages to achieve efficient differential computation and deduplication storage. Summary of the Invention

[0006] To address the technical problems described in the background, this application provides a method, device, and medium for collecting slow SQL statements in a PostgreSQL database. The method includes: determining the query statement corresponding to the target PostgreSQL database based on its version number; collecting data from the pg_stat_statements view of the target PostgreSQL database according to a preset collection period and the query statement, obtaining statistical snapshot data; the collector and the target PostgreSQL database are in a read-only connection; the statistical snapshot data includes at least a unique identifier for each SQL statement; marking the statistical snapshot data as the current snapshot and marking the previously stored statistical snapshot data in the local SQLite database as the baseline snapshot; traversing the baseline snapshot, and if there are statistical data in the baseline snapshot that have the same unique identifier as those in the current snapshot, performing incremental calculations on the statistical data with the same unique identifier to obtain the cumulative execution count increment and the cumulative execution time increment; determining whether the cumulative execution count increment and the cumulative execution time increment are greater than zero; when both are greater than zero, determining the average execution time of the SQL statement corresponding to the unique identifier in the current snapshot; determining whether the average execution time is greater than or equal to a preset slow SQL threshold; when greater than or equal to this threshold, classifying the SQL statement corresponding to the unique identifier as slow SQL and generating a slow SQL record.

[0007] In one example, after generating a slow SQL record, the method further includes: saving the slow SQL record and the sampling time to the slow SQL record table of the local SQLite database; writing the SQL text in the current snapshot to the SQL text dictionary table of the local SQLite database using the unique identifier of the SQL text as the primary key through an INSERT OR IGNORE statement; overwriting the baseline snapshot in the local SQLite database with the current snapshot, and appending the current snapshot as a historical snapshot.

[0008] In one example, the baseline snapshot is traversed. If there is statistical data in the baseline snapshot that is the same as the unique identifier in the current snapshot, the statistical data with the same unique identifier is incrementally calculated to obtain the cumulative execution count increment and the cumulative execution time increment. Specifically, this includes: taking each unique identifier in the baseline snapshot as the traversal object, for each traversed unique identifier, checking whether there is a matching unique identifier in the current snapshot. If not, the unique identifier is skipped; if it exists, for each matching unique identifier that exists in both the baseline snapshot and the current snapshot, the cumulative execution count increment and the cumulative execution time increment are calculated. The cumulative execution count increment is calculated as the cumulative execution count value corresponding to the unique identifier in the current snapshot minus the cumulative execution count value corresponding to the unique identifier in the baseline snapshot; the cumulative execution time increment is calculated as the cumulative execution time value corresponding to the unique identifier in the current snapshot minus the cumulative execution time value corresponding to the unique identifier in the baseline snapshot.

[0009] In one example, it is determined whether the cumulative execution count increment and the cumulative execution time increment are greater than zero. When both the cumulative execution count increment and the cumulative execution time increment are greater than zero, the average execution time of the SQL corresponding to the unique identifier in the current snapshot is determined. Specifically, this includes: checking whether both the cumulative execution count increment and the cumulative execution time increment are greater than zero; if and only if both increment values ​​are greater than zero, it is determined that the SQL corresponding to the unique identifier has valid execution activity within the collection period, and the average execution time of the SQL is determined based on the ratio of the cumulative execution time increment to the cumulative execution count increment.

[0010] In one example, the method further includes: cleaning up historical data stored in the local SQLite database according to a preset cleaning period; the cleaning includes at least deleting data in the slow SQL record table whose sampling time is earlier than a first preset time; deleting data in the historical snapshot table whose sampling time is earlier than a second preset time; and deleting SQL text entries in the SQL text dictionary table that are not referenced by any slow SQL record within a preset time period.

[0011] In one example, based on the preset collection period of the collector and the query statement, the pg_stat_statements view of the target PostgreSQL database is collected to obtain statistical snapshot data. Specifically, this includes: triggering the collection task at regular intervals according to the collection period; establishing a read-only connection with the target PostgreSQL database each time it is triggered; executing the query statement through the read-only connection to obtain the raw data set in the pg_stat_statements view; and converting the time field in the raw data set to a unit to form statistical snapshot data.

[0012] In one example, the query statement corresponding to the target PostgreSQL database is determined based on the database version number of the target PostgreSQL database. Specifically, this includes: detecting the major version number of the target PostgreSQL database; if the major version number is 12, determining that the corresponding query statement uses the first set of field names; the first set of field names includes at least the total_time field used to represent the cumulative execution time; if the major version number is greater than or equal to 13, determining that the corresponding query statement uses the second set of field names; the second set of field names includes at least the total_exec_time field used to represent the cumulative execution time.

[0013] In one example, the statistical snapshot data previously stored in the local SQLite database is marked as the baseline snapshot. The method further includes: if there is no statistical snapshot data stored in the local SQLite database, the current snapshot is stored as the baseline snapshot in the local SQLite database to end the current statistical process.

[0014] On the other hand, embodiments of this application provide a PostgreSQL database slow SQL statistics device, including: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to perform any of the above-mentioned PostgreSQL database slow SQL statistics methods.

[0015] On the other hand, embodiments of this application provide a non-volatile computer storage medium for slow SQL statistics in a PostgreSQL database, which stores computer-executable instructions that can execute any of the above-mentioned methods for slow SQL statistics in a PostgreSQL database.

[0016] The above-described technical solutions adopted in the embodiments of this application can achieve the following beneficial effects:

[0017] By adaptively selecting query statements based on PostgreSQL version numbers and collecting cumulative counter snapshots of `pg_stat_statements` in a read-only, low-frequency manner, the performance impact and intrusion on the production database are significantly reduced. By designing a differential calculation mechanism between the baseline snapshot and the current snapshot, the incremental metrics of SQL execution within the statistical period are accurately obtained, thus achieving high-precision, traceable slow SQL identification and avoiding statistical errors based on instantaneous data. By introducing an SQL text dictionary table for unique storage and referencing of SQL text, the storage bloat caused by duplicate text is effectively solved, and the consistency of SQL aggregation analysis is ensured. By being compatible with field differences across different PostgreSQL versions and handling unified units, the solution's cross-version adaptability and operational convenience are improved. Furthermore, through localized storage, configurable cleanup strategies, and query interfaces, lightweight deployment and long-term maintainability are achieved while ensuring data integrity, providing a low-overhead, highly accurate, and easily integrated solution for slow SQL monitoring. Attached Figure Description

[0018] To more clearly illustrate the technical solution of this application, some embodiments of this application will be described in detail below with reference to the accompanying drawings, in which:

[0019] Figure 1 A flowchart illustrating a slow SQL statistics method for PostgreSQL databases provided in this application embodiment;

[0020] Figure 2 A system architecture diagram of a slow SQL statistics method for PostgreSQL database provided in this application embodiment;

[0021] Figure 3 This is a schematic diagram of the structure of a slow SQL statistics device for a PostgreSQL database provided in an embodiment of this application. Detailed Implementation

[0022] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0023] Some embodiments of this application will now be described in detail with reference to the accompanying drawings.

[0024] Figure 1This is a flowchart illustrating a slow SQL statistics method for PostgreSQL databases, provided as an embodiment of this application. This method can be applied to different business domains. Certain input parameters or intermediate results in this process can be manually adjusted to help improve accuracy.

[0025] The analysis method involved in the embodiments of this application can be implemented by a terminal device or a server, and this application does not impose any special limitations on it. For ease of understanding and description, the following embodiments are all described in detail using a server as an example.

[0026] Based on this Figure 1 The process may include the following steps:

[0027] S101: Determine the query statement corresponding to the target PostgreSQL database based on the database version number of the target PostgreSQL database.

[0028] In some embodiments of this application, the collector first obtains the version information of the target PostgreSQL database through a read-only connection and parses its major version number. Based on the major version number, the system automatically selects the appropriate SQL query template: if the major version number is 12, a query statement containing fields such as total_time, mean_time, max_time, and blk_read_time is used; if the major version number is greater than or equal to 13, a query statement using fields such as total_exec_time, mean_exec_time, max_exec_time, and shared_blk_read_time is used.

[0029] This design directly solves the problem of naming differences in statistical fields across different PostgreSQL versions. Furthermore, regardless of which set of fields is used, the cumulative time value they represent (e.g., in PostgreSQL 13+, the default unit for total_exec_time is milliseconds) will be uniformly divided by 1000.0 in subsequent processing to convert it to a floating-point number in seconds. This ensures consistency in the units of all time metrics, laying the foundation for subsequent difference calculations and threshold comparisons.

[0030] S102: Based on the preset collection period of the collector and the query statement, the pg_stat_statements view of the target PostgreSQL database is collected to obtain statistical snapshot data; the collector and the target PostgreSQL database are read-only connected; the statistical snapshot data includes at least a unique identifier for each SQL statement.

[0031] In some embodiments of this application, the collector is triggered as an independent daemon process or a scheduled task according to a pre-configured collection cycle. Each time it is triggered, it re-establishes a read-only connection to the target database and sets a reasonable timeout for the connection to prevent the collection thread from being suspended for a long time due to network or database congestion.

[0032] Subsequently, the collector executes a defined, version-adapted query statement to retrieve the full set of raw SQL execution statistics from the `pg_stat_statements` view. The collection process is read-only and infrequent, minimizing the performance impact on the production database. Each record in the retrieved raw data set corresponds to a unique queryid generated by the database, the original SQL text query, and a series of performance metrics such as cumulative execution calls and cumulative execution time. This set, containing all queryids and their corresponding metrics, constitutes the statistical snapshot data for this collection, serving as the current baseline for subsequent differential calculations.

[0033] S103: Mark the statistical snapshot data as the current snapshot, and mark the statistical snapshot data previously stored in the local SQLite database as the baseline snapshot.

[0034] In some embodiments of this application, after obtaining the statistical snapshot data, the collected statistical snapshot data is first marked as the "current snapshot" in memory. Then, the system accesses the locally embedded SQLite database, reads the complete snapshot data of the last successfully collected and persisted snapshot from the pgss_latest table, which is dedicated to storing the latest snapshot, and marks it as the "baseline snapshot".

[0035] The `pgss_latest` table employs an overwrite write strategy, always retaining only one latest complete snapshot, dedicated solely to differential calculations. If a query of the `pgss_latest` table reveals it to be empty, it indicates the absence of a usable historical baseline. In this case, the system directly uses the current snapshot as the initial baseline, writing it completely to both the `pgss_latest` table and the `pgss_snapshot` historical table for archiving. Simultaneously, the SQL text is stored in the `query_dict` dictionary table, and the process ends, as meaningful differential calculations cannot be performed on the initial capture. This mechanism ensures system robustness during startup and breakpoint recovery.

[0036] S104: Traverse the baseline snapshots. If there is statistical data in the baseline snapshot that is the same as the unique identifier in the current snapshot, perform incremental calculation on the statistical data with the same unique identifier to obtain the cumulative execution count increment and the cumulative execution time increment.

[0037] In some embodiments of this application, when a baseline snapshot exists, the system begins to iterate through each record in the baseline snapshot. For each baseline queryid encountered, the system checks if the same queryid exists in the current snapshot. If it does not exist, it means that the SQL has not had any new execution activity during this collection period, so the record is skipped. If it exists, it indicates that the SQL was active between two snapshots and requires incremental calculation.

[0038] The core incremental calculation formula is:

[0039]

[0040]

[0041] In addition to these two key metrics, the system will also perform similar differential calculations on other cumulative metrics (such as rows, shared_blk_read, shared_blk_write, etc.) to obtain corresponding incremental values ​​(such as rows_delta), thereby comprehensively reflecting the changes in SQL resource consumption during the statistical period.

[0042] S105: Determine whether the cumulative execution count increment and the cumulative execution time increment are greater than zero. When both the cumulative execution count increment and the cumulative execution time increment are greater than zero, determine the average execution time of the SQL corresponding to the unique identifier in the current snapshot.

[0043] In some embodiments of this application, after obtaining calls_delta and total_exec_time_delta, the system first performs a validity check: it checks whether both are greater than zero. This check is crucial, as it effectively filters out invalid cases where the statistical counters are reset due to the database administrator executing pg_stat_statements_reset(), resulting in negative or zero increments.

[0044] Only when both `calls_delta>0` and `total_exec_time_delta>0` are true, is the SQL statement considered to have had valid execution activity within the current collection period, and its statistical increment is considered reliable.

[0045] Subsequently, based on these two reliable incremental values, the system calculates the average execution time of the SQL statement within the statistical period. The calculation formula is as follows:

[0046] .

[0047] This metric accurately reflects the average execution time (in seconds) of each SQL statement within the most recent sampling period, and is the core basis for determining whether it is a "slow SQL".

[0048] S106: Determine whether the average execution time is greater than or equal to a preset slow SQL threshold. If it is greater than or equal to the threshold, determine the SQL corresponding to the unique identifier as slow SQL and generate a slow SQL record.

[0049] In some embodiments of this application, the system compares avg_exec_time with a user-preset slow SQL judgment threshold. If avg_exec_time >= the threshold, the SQL statement corresponding to the queryid is formally judged as a "slow SQL".

[0050] After the determination, the system generates a structured slow SQL record. This record not only includes the core queryid and avg_exec_time, but also max_exec_time, which represents the worst-case scenario for a single execution within the period, extracted directly from the current snapshot, as well as all incremental metrics calculated in S104, such as calls_delta, total_exec_time_delta, and rows_delta, and is stamped with the current timestamp. These records are temporarily stored in batches and then persisted to the local SQLite database along with the SQL text. Specifically, the slow SQL record is stored in the slow_snapshots table, while its corresponding complete SQL text is uniquely stored in the query_dict dictionary table using INSERT OR IGNORE, thus completing a full closed loop from collection, calculation to determination.

[0051] It should be noted that, although the embodiments in this application are based on... Figure 1 Steps S101 to S106 will be described sequentially, but this does not mean that steps S101 and S106 must be performed in a strict order. The reason this embodiment follows this order is... Figure 1 The order in which steps S101 to S106 are described is provided to facilitate understanding of the technical solutions of the embodiments of this application by those skilled in the art. In other words, in the embodiments of this application, the order of steps S101 to S106 can be appropriately adjusted according to actual needs.

[0052] pass Figure 1This method significantly reduces the performance impact and intrusiveness on the production database by adaptively selecting query statements based on the PostgreSQL version number and collecting cumulative counter snapshots of `pg_stat_statements` in a read-only, low-frequency manner. By designing a differential calculation mechanism between the baseline snapshot and the current snapshot, it accurately obtains the incremental indicators of SQL execution within the statistical period, thereby achieving high-precision and traceable slow SQL identification and avoiding statistical errors based on instantaneous data. By introducing an SQL text dictionary table for unique storage and referencing of SQL text, it effectively solves the storage bloat problem caused by duplicate text and ensures the consistency of SQL aggregation analysis. By being compatible with field differences across different PostgreSQL versions and handling unified units, it improves the solution's cross-version adaptability and operational convenience. Furthermore, through localized storage, configurable cleanup strategies, and query interfaces, it achieves lightweight deployment and long-term maintainability while ensuring data integrity, providing a low-overhead, highly accurate, and easily integrated solution for slow SQL monitoring.

[0053] Figure 2 This is a system architecture diagram of a slow SQL statistics method for PostgreSQL database provided in an embodiment of this application.

[0054] Figure 3 A schematic diagram of the structure of a slow SQL statistics device for a PostgreSQL database provided in this application embodiment includes:

[0055] At least one processor; and,

[0056] A memory that is communicatively connected to at least one processor; wherein,

[0057] A PostgreSQL database slow SQL statistics method that stores instructions executable by at least one processor, which are executed by at least one processor to enable at least one processor to perform any of the above.

[0058] Some embodiments of this application provide a non-volatile computer storage medium for slow SQL statistics of a PostgreSQL database, which stores computer-executable instructions that can execute any of the above-described methods for slow SQL statistics of a PostgreSQL database.

[0059] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device and medium embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the description of the method embodiments.

[0060] The devices and media provided in this application are one-to-one with the methods. Therefore, the devices and media also have similar beneficial technical effects as their corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the devices and media will not be repeated here.

[0061] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0062] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0063] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0064] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0065] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0066] Memory may include non-persistent storage in computer-readable media, random access memory (RAM), and non-volatile memory such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0067] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0068] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article, or apparatus. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes that element.

[0069] The above are merely embodiments of this application and are not intended to limit this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, or improvements made within the technical principles of this application should fall within the protection scope of this application.

Claims

1. A method for calculating slow SQL queries in a PostgreSQL database, characterized in that, The method includes: Based on the database version number of the target PostgreSQL database, determine the query statement corresponding to the target PostgreSQL database; According to the preset collection period of the collector and the query statement, the pg_stat_statements view of the target PostgreSQL database is collected to obtain statistical snapshot data; the collector and the target PostgreSQL database are read-only connected; the statistical snapshot data includes at least a unique identifier for each SQL statement; The statistical snapshot data is marked as the current snapshot, and the statistical snapshot data previously stored in the local SQLite database is marked as the baseline snapshot; The baseline snapshots are traversed. If there is statistical data in the baseline snapshot that has the same unique identifier as the current snapshot, the statistical data with the same unique identifier is incrementally calculated to obtain the cumulative execution count increment and the cumulative execution time increment. Determine whether the cumulative execution count increment and the cumulative execution time increment are greater than zero. When both the cumulative execution count increment and the cumulative execution time increment are greater than zero, determine the average execution time of the SQL corresponding to the unique identifier in the current snapshot. Determine whether the average execution time is greater than or equal to a preset slow SQL threshold. If it is greater than or equal to the threshold, the SQL corresponding to the unique identifier is identified as slow SQL and a slow SQL record is generated.

2. The method according to claim 1, characterized in that, After generating the slow SQL record, the method further includes: The slow SQL records and sampling times are saved to the slow SQL record table in the local SQLite database; Write the SQL text in the current snapshot to the SQL text dictionary table of the local SQLite database using the unique identifier of the SQL text as the primary key via the INSERT OR IGNORE statement; The current snapshot overwrites the baseline snapshot in the local SQLite database, and the current snapshot is appended and stored as a historical snapshot.

3. The method according to claim 1, characterized in that, The process of traversing the baseline snapshots involves, if a statistical data point in the baseline snapshot contains a unique identifier identical to that in the current snapshot, performing incremental calculations on the statistical data point with the same unique identifier to obtain the cumulative execution count increment and the cumulative execution time increment. Specifically, this includes: Using each unique identifier in the baseline snapshot as the traversal object, for each traversed unique identifier, check if there is a matching unique identifier in the current snapshot. If not, skip the unique identifier. If they exist, for each identical unique identifier that exists in both the baseline snapshot and the current snapshot, perform cumulative execution count increment calculation and cumulative execution time increment calculation; the cumulative execution count increment calculation is the cumulative execution count value corresponding to the unique identifier in the current snapshot minus the cumulative execution count value corresponding to the unique identifier in the baseline snapshot; the cumulative execution time increment calculation is the cumulative execution time value corresponding to the unique identifier in the current snapshot minus the cumulative execution time value corresponding to the unique identifier in the baseline snapshot.

4. The method according to claim 1, characterized in that, The step of determining whether the cumulative execution count increment and the cumulative execution time increment are greater than zero, and when both the cumulative execution count increment and the cumulative execution time increment are greater than zero, determining the average execution time of the SQL corresponding to the unique identifier in the current snapshot, specifically includes: Check whether both the cumulative execution count increment and the cumulative execution time increment are greater than zero; If and only if both incremental values ​​are greater than zero, it is determined that the SQL corresponding to the unique identifier has a valid execution activity within the collection period, and the average execution time of the SQL is determined according to the ratio of the cumulative execution time increment to the cumulative execution count increment.

5. The method according to claim 1, characterized in that, The method further includes: According to a preset cleanup cycle, the historical data stored in the local SQLite database is cleaned up; the cleanup includes at least deleting data in the slow SQL record table whose sampling time is earlier than a first preset time; deleting data in the historical snapshot table whose sampling time is earlier than a second preset time; and deleting SQL text entries in the SQL text dictionary table that are not referenced by any slow SQL record within a preset time period.

6. The method according to claim 1, characterized in that, The step involves collecting statistical snapshot data from the pg_stat_statements view of the target PostgreSQL database according to the preset collection period of the collector and the query statement, specifically including: The data collection task is triggered periodically according to the stated collection cycle; On each trigger, a read-only connection is established with the target PostgreSQL database; By executing the query statement through the read-only connection, the original data set in the pg_stat_statements view is obtained; The time field in the original dataset is converted to a unit to form the statistical snapshot data.

7. The method according to claim 1, characterized in that, The step of determining the query statement corresponding to the target PostgreSQL database based on the database version number specifically includes: Detect the major version number of the target PostgreSQL database; If the major version number is 12, the corresponding query statement is determined to be the first group of field names; the first group of field names includes at least the total_time field used to represent the cumulative execution time; If the major version number is greater than or equal to 13, the corresponding query statement is determined to be the second group of field names; the second group of field names includes at least the total_exec_time field used to represent the cumulative execution time.

8. The method according to claim 1, characterized in that, The method further includes marking the previously stored statistical snapshot data in the local SQLite database as the baseline snapshot: If there is no statistical snapshot data stored in the local SQLite database, the current snapshot is stored as a baseline snapshot in the local SQLite database to end the current statistical process.

9. A slow SQL statistics device for PostgreSQL databases, characterized in that, include: At least one processor; as well as, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, which, when executed by the at least one processor, enables the at least one processor to perform a PostgreSQL database slow SQL statistics method according to any one of claims 1-8.

10. A slow SQL statistics storage medium for a PostgreSQL database, storing computer-executable instructions, characterized in that, The computer-executable instructions are capable of executing a slow SQL statistics method for a PostgreSQL database as described in any one of claims 1-8.

Images