Slow SQL query issues in ApsaraDB RDS for PostgreSQL

Notes on EXPLAIN (ANALYZE) for DML SQL statements

The EXPLAIN (ANALYZE) statement and the EXPLAIN statement work in a similar way. The main difference is that the EXPLAIN (ANALYZE) statement executes the SQL statement. If the execution plan of an SQL query involves data changes such as DML UPDATE, INSERT, or DELETE operations, you must execute the EXPLAIN ANALYZE statement in the transaction. After the SQL statement is executed, you can roll the transaction back.

BEGIN;
EXPLAIN (ANALYZE) <DML(UPDATE/INSERT/DELETE) SQL>;
ROLLBACK;

How to read an execution plan

Scan nodes use multiple scan methods. This topic describes some of the most common methods in detail:

• Seq Scan:

  A sequential scan of the full table. This method is generally used for queries on tables that do not have an index. For example, the command explain(ANALYZE,VERBOSE,BUFFERS) select * from class where st_no=2; returns the following result:

  QUERY PLAN 
  --------------------------------------------------------------------------------------------------
  Seq Scan on public.class (cost=0.00..26.00 rows=1 width=35) (actual time=0.136..0.141 rows=1 loops=1) 
  // Seq Scan on public.class indicates the node type and the object it acts on. This means a full table scan is performed on the class table.
  // (cost=0.00..26.00 rows=1 width=35) is the cost estimate for this node. 0.00 is the startup cost. 26.00 is the total cost. rows is the estimated number of output rows. width is the average width of the output rows.
  // (actual time=0.136..0.141 rows=1 loops=1) is the actual execution information for this node. 0.136 is the startup time in ms. 0.141 is the time spent in ms. rows is the actual number of output rows. loops is the number of loops.
      Output: st_no, name // Represents the columns of the SQL output result set. This is displayed only when the VERBOSE option is on.
      Filter: (class.st_no = 2) // Indicates the Filter operation in the Seq Scan node. During the full table scan, each record is filtered. The filter condition is class.st_no = 2.
      Rows Removed by Filter: 1199 // Indicates how many rows were filtered. This is VERBOSE information for the Seq Scan node and is displayed only when the VERBOSE option is on.
      Buffers: shared hit=11 // Indicates that 11 data blocks were hit in the shared cache. This is BUFFERS information for the Seq Scan node and is displayed only when the BUFFERS option is on.
  
  Planning time: 0.066 ms // The time taken to generate the query plan.
  Execution time: 0.160 ms // The actual SQL execution time, excluding the time to generate the query plan.

• Index Scan:

  An index scan. This method is mainly used when the WHERE condition contains an index column. For example, if an index exists on the st_no column, the command EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * FROM class WHERE st_no=2; returns the following result:

  QUERY PLAN 
  --------------------------------------------------------------------------------------------------
  Index Scan using no_index on public.class (cost=0.28..8.29 rows=1 width=35) (actual time=0.022..0.023 rows=1 loops=1) 
  // An index scan is performed on the public.class table using the no_index index.
       Output: st_no, name 
       Index Cond: (class.st_no = 2) // The index scan condition is class.st_no = 2.
       Buffers: shared hit=3 
  Planning time: 0.119 ms 
  Execution time: 0.060 ms 
  (6 rows)

  The preceding result shows that after an index is used, the scan speed for the same table under the same conditions is faster. When fewer data blocks need to be scanned, the cost is lower and the speed is faster.

• Index Only Scan:

  A covering index scan. This method returns only the specified index columns. For example, the command EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT st_no FROM class WHERE st_no=2; returns the following result:

  QUERY PLAN 
  --------------------------------------------------------------------------------------------------
  Index Only Scan using no_index on public.class (cost=0.28..4.29 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=1) 
  // An index-only scan is performed on the public.class table using the no_index index.
      Output: st_no Index Cond: (class.st_no = 2) 
      Heap Fetches: 0 // The number of data blocks that need to be scanned.
      Buffers: shared hit=3 
  Planning time: 0.058 ms 
  Execution time: 0.036 ms 
  (7 rows)

• Bitmap Index Scan:

  A bitmap index scan is used to scan a table based on the indexes of the table and returns a bitmap. Both bitmap index scans and index scans are used to scan tables based on indexes. However, a bitmap index scan returns a bitmap instead of a tuple. In the bitmap, each bit represents a scanned data block.

• Bitmap Heap Scan:

  A Bitmap Heap Scan is generally the parent node of a Bitmap Index Scan. It converts the bitmap that is returned by the Bitmap Index Scan into corresponding tuples. Compared with an Index Scan, a Bitmap Index Scan transforms random reads into reads that follow the physical order of data blocks. This greatly improves scan performance when the data volume is large. For example, the command EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * FROM class WHERE st_no=2; returns the following result:

  QUERY PLAN 
  --------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on public.class (cost=4.29..8.30 rows=1 width=35) (actual time=0.025..0.025 rows=1 loops=1) Output: st_no, name 
  // A Bitmap Heap Scan is performed on the public.class table.
  Recheck Cond: (class.st_no = 2)
  // The condition for the Recheck operation of the Bitmap Heap Scan is class.st_no = 2.
  Heap Blocks: exact=1 
  // The exact number of scanned data blocks is 1.
  Buffers: shared hit=3 
  -> Bitmap Index Scan on no_index (cost=0.00..4.29 rows=1 width=0) (actual time=0.019..0.019 rows=1 loops=1) 
  // A bitmap index scan is performed using the no_index index.
  Index Cond: (class.st_no = 2) 
  // The bitmap index condition is class.st_no = 2.
  Buffers: shared hit=2 
  Planning time: 0.088 ms 
  Execution time: 0.063 ms 
  (10 rows)

  Note

  • In most cases, an Index Scan is faster than a Seq Scan.

  • If the result set is a large portion of the total data, an Index Scan can be slower than a Seq Scan. This is because an Index Scan must first scan the index and then read the table data.

  • If the result set is small but has many tuples, a Bitmap Index Scan performs better than an Index Scan.

  • If the index covers the result set, an Index Only Scan is usually the fastest because it only scans the index. However, performance can decrease in some cases, such as when a visibility map has not been generated.