If slow SQL queries occur or SQL statements occupies a large amount of system memory space, analyze the queries based on the actual situation.

Check slow SQL statements

You can check the slow SQL statements that have been executed or are being executed.
  • View the slow SQL statements that have been executed.
    Use the pg_stat_statements view to check the slow SQL statements.
    SELECT total_time / calls AS  avg, query 
    FROM pg_stat_statements ORDER BY avg DESC LIMIT 10;
  • View the slow SQL statements that are being executed.
    Check each SQL statement whose execution time is longer than 1 second.
    select * from pg_stat_activity where state<>'idle' and now()-query_start > interval '1 s' order by query_start ;

Execute EXPLAIN statements to query SQL execution plans

You can execute EXPLAIN statements to query SQL execution plans. You can use these execution plans to analyze the resources consumed by SQL statements and the execution time of SQL statements.

When you execute the EXPLAIN statements to query SQL execution plans, use the following syntax:
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    BUFFERS [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }
The following table describes the parameters in the preceding code.
Parameter Description
ANALYZE Instructs the SQL statement to be executed to return the execution plan. The default value is FALSE. When you optimize SQL statements that modify data, you may need to execute the SQL statements, but you do not want to modify the existing data. In this case, you can include these SQL statements in a transaction and execute the transaction. After the SQL statements are analyzed, roll back the transaction.
VERBOSE Displays the additional information about the execution plan. The default value is FALSE. The additional information includes the output column list for each node in the execution plan, the table schema, the function schema, the alias of the table to which the columns specified in the expressions belong, and the name of the trigger that is called.
COSTS Displays the estimated startup cost and total cost of each plan node. The estimated number of rows and the estimated width of each row are also returned. The estimated startup cost means the cost consumed to find the first record that meets the specified conditions. The default value is TRUE.
BUFFERS Displays the information about buffer usage. The default value is FALSE. This parameter can be used only after the ANALYZE parameter is set to TRUE. The buffer information includes the number of hit blocks, the number of updated blocks, and the number of removed blocks among shared blocks, local blocks, and temporary blocks. Shared blocks contain data from regular tables and index blocks. Local blocks contain data from temporary tables or index blocks. Temporary blocks contain short-term data that is used in operations such as sorts and hashes.
FORMAT Specifies the output format. The default value is TEXT. The same result is returned regardless of the format you specify. The XML, JSON, and YAML formats are easier than other formats for programs to parse the execution plan of the SQL statement.

Analysis on views

Check the pg_stat_statements and polar_stat_sql views to analyze the resources required to execute SQL statements. You must create the corresponding plug-in for each of the views and enable each of the plug-ins.
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION polar_stat_sql;

postgres=# alter system set polar_stat_sql.enable_stat=on;
ALTER SYSTEM
postgres=# alter system set polar_stat_sql.enable_qps_monitor=on;
ALTER SYSTEM
postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)
  • For more information about the pg_stat_statements view, see pg_stat_statements.
  • The polar_stat_sql view includes the monitoring information of SQL statements.

    The information consists of the following four parts:

    • Statistics about nodes on which execution plans are performed. The nodes include the nodes on which scans, joins, aggregation, sorts, and hashes are performed.
    • Time of CPU running in system mode and in user mode, I/O, memory, the number of received and requested Inter-process communication (IPC) messages, and voluntary CPU context switches and involuntary CPU context switches at the SQL level. I/O is measured by the number of read/write bytes. Memory is measured by the number of requested pages and the number of page swaps.
    • Time consumed by each phase of executing an SQL statement. The time includes the time of parsing, analyzing, and rewriting the SQL statement, generating the execution plan, and executing the SQL statement.
    • Latch and lock statistics.
    The polar_stat_sql view records the accumulated data. To ensure the accuracy of the data, clear the existing data before you execute an SQL statement.
    postgres=# select polar_stat_sql_reset();
     polar_stat_sql_reset 
    ----------------------
     
    (1 row)