Method to identify issues
Identify pain points by reading an execution plan from the top down
Read an execution plan from the top down to check and analyze the operators that consume a large amount of time.
Check execution costs and compare the number of rows
Check for extremely high or low estimates of execution costs and compare the estimated rows with the actually executed rows to identify their issues.
Exercise caution with time-consuming operators
Exercise caution with operators such as NestLoop or a combination of Sort and GroupByAgg because they are seldom required in analytical processing scenarios.
Check specific operators
Check for unnecessary motion operators. Check whether the distribution key can be optimized to reduce motion operators and whether motion operators can be used for replicated tables.
Check whether the inner and outer tables are joined in an appropriate order.
Check whether scan operators can be used for indexes and partitioned tables.
Adjust parameters based on memory information
Query and analyze the temporary spill files to disk. Then, adjust the statement_mem parameter to an appropriate value.
Example
Update (cost=0.00..1274.11 rows=1 width=1) (actual time=995096.707..1517097.191 rows=245136 loops=1)
Executor Memory: 1kB Segments: 96 Max: 1kB (segment 0)
-> Partition Selector for t2 (cost=0.00..1274.04 rows=1 width=842) (actual time=995096.480..1514408.806 rows=245136 loops=1)
-> Redistribute Motion 96:96 (slice2; segments: 96) (cost=0.00..1274.04 rows=1 width=838) (actual time=995096.440..1513830.155 rows=245136 loops=1)
Hash Key: t2.c1, t2.c2
-> Split (cost=0.00..1274.04 rows=1 width=838) (actual time=995080.103..1496878.037 rows=245136 loops=1)
Executor Memory: 1kB Segments: 96 Max: 1kB (segment 0)
-> Hash Join (cost=0.00..1274.04 rows=1 width=1484) (actual time=995080.071..1496625.817 rows=122568 loops=1)
Hash Cond: ((t1.c1)::text = (t2.c1)::text)
Executor Memory: 33535270kB Segments: 96 Max: 349326kB (segment 33)
work_mem: 33535270kB Segments: 96 Max: 349326kB (segment 33) Workfile: (96 spilling)
Work_mem wanted: 26684983K bytes avg, 26684983K bytes max (seg0) to lessen workfile I/O affecting 96 workers.
-> Seq Scan on t1 (cost=0.00..672.28 rows=121412 width=736) (actual time=672.771..1039.167 rows=122568 loops=1)
Filter: ((t1.c2 = '2019-05-17'::date) AND ((t1.c3)::text = '0'::text))
-> Hash (cost=431.00..431.00 rows=1 width=762) (actual time=994417.443..994417.443 rows=34583155 loops=1)
-> Broadcast Motion 96:96 (slice1; segments: 96) (cost=0.00..431.00 rows=1 width=762) (actual time=25.562..912862.203 rows=34583155 loops=1)
-> Sequence (cost=0.00..431.00 rows=1 width=762) (actual time=34.475..4822.173 rows=361460 loops=1)
-> Partition Selector for t2 (dynamic scan id: 1) (cost=10.00..100.00 rows=2 width=4) (never executed)
Partitions selected: 27 (out of 27)
-> Dynamic Seq Scan on t2 (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=762) (actual time=34.441..4680.938 rows=361460 loops=1)
Partitions scanned: Avg 27.0 (out of 27) x 96 workers. Max 27 parts (seg0).The preceding example shows an execution plan by performing EXPLAIN ANALYZE on an SQL statement. You can use the preceding method to identify issues.
Read the execution plan from the top down to check the actual amount of time consumed by each operator. The hash join takes the longest time to complete.
The following hash join information shows that the inner table data is spilled to disk:
"work_mem: 33535270kB Segments: 96 Max: 349326kB (segment 33) Workfile: (96 spilling) Work_mem wanted: 26684983K bytes avg, 26684983K bytes max (seg0) to lessen workfile I/O affecting 96 workers."The following information shows that the inner table involved in the hash join has a broadcast motion in the build phase:
"Broadcast Motion 96:96 (slice1; segments: 96) (cost=0.00..431.00 rows=1 width=762) (actual time=25.562..912862.203 rows=34583155 loops=1)". The optimizer estimates that the t1 table has one row, which is different from the actual situation.The preceding diagnostic results show that the optimizer considers the t2 table as a small table due to a lack of timely statistics collection on the t1 table. The optimizer broadcasts the t2 table to all nodes during the hash join and builds a hash table by using the t2 table as the inner table. As a result, the t2 table data is spilled to disk and the SQL statement takes a long time to complete.
To resolve the preceding issues, you can execute the ANALYZE t2; statement to collect statistics on the t2 table again.