PostgreSQL slow query statement record and analysis

Time:2021-10-15

1. Record

PostgreSQL takes version 12 as an example

We need to open the slow query log of PostgreSQL first. This is a precondition. First record our queries for a long time.

  • Edit profile:/var/postgres12/data/postgresql.conf
#log_min_duration_statement = -1        # -1 is disabled, 0 logs all statements
                                        # and their durations, > 0 logs only
                                        # statements running at least this number
                                        # of milliseconds

modify-1by200, indicating that operations greater than or equal to 200 milliseconds will be logged.

  • Overload configuration
/path/to/pg_ctl reload -D /path/to/pgdata

2. Get slow query statement

2.1 cut PostgreSQL overload

sudo su - postgres

2.2 get slow query statements

  • open the log file
less /path/to/pg_log/postgresql.log
  • Skip to end of file
Shift + G
  • Query keywordduration

Because the recorded log format is as follows:

duration xxx ms  select ….

3. Analysis

  • Connect to PostgreSQL server
path/to/psql -U postgres -d <database>
  • Turn on timing
\timing
  • analysis of sentences
explain select max(id) as max_id from test_table where  created < now() - interval '24 hours';
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=12673.45..12673.46 rows=1 width=8)
   ->  Gather  (cost=12673.24..12673.45 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=11673.24..11673.25 rows=1 width=8)
               ->  Parallel Seq Scan on test_table  (cost=0.00..10631.80 rows=416575 width=8)
                     Filter: (created < (now() - '24:00:00'::interval))
(6 rows)

explainThe query plan is displayed and will not be executed, so even update can be placed after explain.
But note that it can’t beexplain analyze, yesanalyzeIt really ran away, so this can’t be used forDML

DQL: SELECT
DML: UPDATE/DELETE/INSERT
DDL: CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
DCL: GRANT/ROLLBACK/COMMIT

In addition, you can see that the result showsSeq Scan, sequential scanning, so you can create an index for it, which becomesIndex Scan

create index test_index on test_table (created);

Then analyze again. If the time is not significantly reduced, the index can be removed.
Repeat the above steps to gradually reduce the slow query in the system.