explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lu8k

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=928,174.75..928,175.44 rows=10 width=350) (actual rows= loops=)

  • Rows out: 10 rows with 81096 ms to first row, 81097 ms to end, start offset by 59 ms.
  • (slice0) Executor memory: 974K bytes.
  • (slice1) Executor memory: 2,294K bytes (entry db).
  • (slice2) Executor memory: 3,376K bytes (entry db).
  • (slice3) Executor memory: 1,524K bytes (seg74).
  • (slice4) Executor memory: 2,350K bytes avg x 96 workers, 2652K bytes max (seg76).
  • (slice5) Executor memory: 2,631K bytes avg x 96 workers, 2692K bytes max (seg0).
  • (slice6) Executor memory: 1,611K bytes avg x 96 workers, 1677K bytes max (seg9). Work_mem: 1K bytes max.
  • (slice7) Executor memory: 1,496K bytes (entry db).
  • (slice8) Executor memory: 2,372K bytes avg x 96 workers, 2380K bytes max (seg91). Work_mem: 1K bytes max.
  • (slice9) Executor memory: 1,612K bytes avg x 96 workers, 1758K bytes max (seg13). Work_mem: 1K bytes max.
  • (slice10) Executor memory: 807K bytes avg x 96 workers, 1424K bytes max (seg81). Work_mem: 529K bytes max.Statement statistics:
  • Memory used: 128,000K bytesSettings: enable_groupagg=offOptimizer status: legacy query optimizer
2. 0.000 0.000 ↓ 0.0

Gather Motion 96:1 (slice10; segments: 96) (cost=928,174.75..928,175.44 rows=10 width=350) (actual rows= loops=)

  • Merge Key: t.tabletype, t.table_name, t.source_schema, t.source_table_name, t.target_schema, t.target_table_name
  • Rows out: 10 rows at destination with 81096 ms to first row, 81097 ms to end, start offset by 59 ms.
3. 0.000 0.000 ↓ 0.0

Limit (cost=928,174.75..928,175.24 rows=1 width=350) (actual rows= loops=)

  • Rows out: Avg 1.1 rows x 16 workers. Max 2 rows (seg53) with 81055 ms to end, start offset by 76 ms.
4. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=928,174.75..928,175.24 rows=1 width=350) (actual rows= loops=)

  • Group By: t.tabletype, t.table_name, t.source_schema, t.source_table_name, t.target_schema, t.target_table_name
  • Rows out: Avg 1.1 rows x 16 workers. Max 2 rows (seg53) with 81055 ms to end, start offset by 76 ms.
  • Executor memory: 16K bytes avg, 41K bytes max (seg50).
5. 0.000 0.000 ↓ 0.0

Sort (cost=928,174.75..928,174.78 rows=1 width=390) (actual rows= loops=)

  • Sort Key: t.tabletype, t.table_name, t.source_schema, t.source_table_name, t.target_schema, t.target_table_name
  • Rows out: Avg 122.7 rows x 16 workers. Max 489 rows (seg81) with 81027 ms to end, start offset by 72 ms.
  • Executor memory: 71K bytes avg, 529K bytes max (seg81).
  • Work_mem used: 71K bytes avg, 529K bytes max (seg81). Workfile: (0 spilling, 0 reused)
6. 0.000 0.000 ↓ 0.0

Redistribute Motion 96:96 (slice9; segments: 96) (cost=42,163.72..928,174.58 rows=1 width=390) (actual rows= loops=)

  • Hash Key: t.tabletype, t.table_name, t.source_schema, t.source_table_name, t.target_schema, t.target_table_name
  • Rows out: Avg 122.7 rows x 16 workers at destination. Max 489 rows (seg81) with 28708 ms to first row, 81025 ms to end, start offset by 73 ms.
7. 0.000 0.000 ↓ 0.0

Hash Join (cost=42,163.72..928,174.37 rows=1 width=390) (actual rows= loops=)

  • Hash Cond: pa.attrelid = pc.oid
  • Rows out: Avg 115.5 rows x 17 workers. Max 465 rows (seg13) with 43395 ms to first row, 81007 ms to end, start offset by 90 ms.
  • Executor memory: 1K bytes avg, 1K bytes max (seg13).
  • Work_mem used: 1K bytes avg, 1K bytes max (seg13). Workfile: (0 spilling, 0 reused)
  • (seg13) Hash chain length 1.0 avg, 1 max, using 2 of 16417 buckets.
8. 0.000 0.000 ↓ 0.0

Redistribute Motion 1:96 (slice1) (cost=0.00..828,644.30 rows=22,946,210 width=76) (actual rows= loops=)

  • Hash Key: pa.attrelid
  • Rows out: Avg 187098.1 rows x 17 workers at destination. Max 194920 rows (seg74) with 0.052 ms to first row, 73185 ms to end, start offset by 7880 ms.
9. 0.000 0.000 ↓ 0.0

Seq Scan on pg_attribute pa (cost=0.00..369,720.10 rows=22,946,210 width=76) (actual rows= loops=)

  • Rows out: 17,986,705 rows with 2.115 ms to first row, 51402 ms to end, start offset by 253 ms.
10. 0.000 0.000 ↓ 0.0

Hash (cost=42,163.63..42,163.63 rows=1 width=322) (actual rows= loops=)

  • Rows in: Avg 1.1 rows x 17 workers. Max 2 rows (seg13) with 7790 ms to end, start offset by 90 ms.
11. 0.000 0.000 ↓ 0.0

Redistribute Motion 96:96 (slice8; segments: 96) (cost=142.13..42,163.63 rows=1 width=322) (actual rows= loops=)

  • Hash Key: pc.oid
  • Rows out: Avg 1.1 rows x 17 workers at destination. Max 2 rows (seg13) with 7780 ms to first row, 7790 ms to end, start offset by 90 ms.
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=142.13..42,163.49 rows=1 width=322) (actual rows= loops=)

  • Hash Cond: t.source_schema = n.nspname::text AND pc.relnamespace = n.oid
  • Rows out: 18 rows (seg91) with 7766 ms to first row, 7776 ms to end, start offset by 93 ms.
  • Executor memory: 1K bytes avg, 1K bytes max (seg6).
  • Work_mem used: 1K bytes avg, 1K bytes max (seg6). Workfile: (0 spilling, 0 reused)
  • (seg6) Hash chain length 1.0 avg, 1 max, using 7 of 65539 buckets.
13. 0.000 0.000 ↓ 0.0

Redistribute Motion 96:96 (slice6; segments: 96) (cost=57.69..42,078.80 rows=1 width=326) (actual rows= loops=)

  • Hash Key: pc.relnamespace, t.source_schema
  • Rows out: Avg 27.0 rows x 2 workers at destination. Max 36 rows (seg80) with 7765 ms to first row, 7774 ms to end, start offset by 95 ms.
14. 0.000 0.000 ↓ 0.0

Hash Join (cost=57.69..42,078.11 rows=1 width=326) (actual rows= loops=)

  • Hash Cond: pc.relname = t.table_name::name
  • Rows out: Avg 3.2 rows x 17 workers. Max 6 rows (seg73) with 1207 ms to first row, 7761 ms to end, start offset by 97 ms.
  • Executor memory: 1K bytes avg, 1K bytes max (seg73).
  • Work_mem used: 1K bytes avg, 1K bytes max (seg73). Workfile: (0 spilling, 0 reused)
  • (seg73) Hash chain length 3.0 avg, 3 max, using 2 of 16417 buckets.
15. 0.000 0.000 ↓ 0.0

Redistribute Motion 1:96 (slice2) (cost=0.00..40,634.53 rows=554,151 width=72) (actual rows= loops=)

  • Hash Key: pc.relname
  • Rows out: Avg 6390.2 rows x 17 workers at destination. Max 6552 rows (seg49) with 135 ms to first row, 7726 ms to end, start offset by 140 ms.
16. 0.000 0.000 ↓ 0.0

Seq Scan on pg_class pc (cost=0.00..29,551.51 rows=554,151 width=72) (actual rows= loops=)

  • Rows out: 614,210 rows with 0.127 ms to first row, 6668 ms to end, start offset by 205 ms.
17. 0.000 0.000 ↓ 0.0

Hash (cost=57.46..57.46 rows=1 width=318) (actual rows= loops=)

  • Rows in: Avg 3.2 rows x 17 workers. Max 6 rows (seg73) with 43 ms to end, start offset by 97 ms.
18. 0.000 0.000 ↓ 0.0

Redistribute Motion 96:96 (slice5; segments: 96) (cost=19.35..57.46 rows=1 width=318) (actual rows= loops=)

  • Hash Key: t.table_name::name
  • Rows out: Avg 3.2 rows x 17 workers at destination. Max 6 rows (seg73) with 42 ms to first row, 43 ms to end, start offset by 97 ms.
19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=19.35..57.10 rows=1 width=318) (actual rows= loops=)

  • Rows out: Avg 3.6 rows x 15 workers. Max 6 rows (seg27) with 34 ms to end, start offset by 104 ms.
20. 0.000 0.000 ↓ 0.0

Broadcast Motion 1:96 (slice3; segments: 1) (cost=0.00..2.97 rows=288 width=160) (actual rows= loops=)

  • Rows out: Avg 3.0 rows x 96 workers at destination. Max 3 rows (seg0) with 0.021 ms to end, start offset by 138 ms.
21. 0.000 0.000 ↓ 0.0

Append (cost=0.00..0.06 rows=1 width=0) (actual rows= loops=)

  • Rows out: 3 rows with 0.018 ms to first row, 0.041 ms to end, start offset by 73 ms.
22. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0) (actual rows= loops=)

  • Rows out: 1 rows with 0.013 ms to end, start offset by 73 ms.
23. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

  • Rows out: 1 rows with 0.007 ms to end, start offset by 73 ms.
24. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1 width=0) (actual rows= loops=)

  • Rows out: 1 rows with 0.010 ms to first row, 0.011 ms to end, start offset by 73 ms.
25. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

  • Rows out: 1 rows with 0.004 ms to end, start offset by 73 ms.
26. 0.000 0.000 ↓ 0.0

Subquery Scan "*SELECT* 3" (cost=0.00..0.02 rows=1 width=0) (actual rows= loops=)

  • Rows out: 1 rows with 0.010 ms to end, start offset by 73 ms.
27. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

  • Rows out: 1 rows with 0.005 ms to end, start offset by 73 ms.
28. 0.000 0.000 ↓ 0.0

Materialize (cost=19.35..19.41 rows=1 width=158) (actual rows= loops=)

  • Rows out: Avg 4.6 rows x 15 workers. Max 7 rows (seg27) with 34 ms to end of 4 scans, start offset by 138 ms.
29. 0.000 0.000 ↓ 0.0

HashAggregate (cost=19.28..19.34 rows=1 width=158) (actual rows= loops=)

  • Group By: gen_queue_tenant_tables.table_name
  • Rows out: Avg 1.2 rows x 15 workers. Max 2 rows (seg27) with 33 ms to end, start offset by 105 ms.
  • Executor memory: 1,052K bytes avg, 1113K bytes max (seg0).
30. 0.000 0.000 ↓ 0.0

Redistribute Motion 96:96 (slice4; segments: 96) (cost=19.08..19.20 rows=1 width=158) (actual rows= loops=)

  • Hash Key: gen_queue_tenant_tables.table_name
  • Rows out: Avg 1.2 rows x 15 workers at destination. Max 2 rows (seg27) with 12 ms to first row, 32 ms to end, start offset by 105 ms.
31. 0.000 0.000 ↓ 0.0

HashAggregate (cost=19.08..19.08 rows=1 width=158) (actual rows= loops=)

  • Group By: gen_queue_tenant_tables.table_name
  • Rows out: 18 rows (seg76) with 1.343 ms to first row, 1.393 ms to end, start offset by 116 ms.
  • Executor memory: 1,041K bytes avg, 1113K bytes max (seg76).
32. 0.000 0.000 ↓ 0.0

Seq Scan on gen_queue_tenant_tables (cost=0.00..19.07 rows=1 width=23) (actual rows= loops=)

  • Filter: status::text = 'ENABLED'::text AND tenant_code::text = 'HDFS'::text AND table_name::text ~~ 'xx_ap%'::text
  • Rows out: 18 rows (seg76) with 0.175 ms to first row, 0.508 ms to end, start offset by 116 ms.
33. 0.000 0.000 ↓ 0.0

Hash (cost=82.63..82.63 rows=2 width=68) (actual rows= loops=)

  • Rows in: Avg 2.9 rows x 90 workers. Max 7 rows (seg6) with 0.038 ms to end, start offset by 88 ms.
34. 0.000 0.000 ↓ 0.0

Redistribute Motion 1:96 (slice7) (cost=0.00..82.63 rows=121 width=68) (actual rows= loops=)

  • Hash Key: n.oid, n.nspname::text
  • Rows out: Avg 2.9 rows x 90 workers at destination. Max 7 rows (seg6) with 0.022 ms to first row, 0.023 ms to end, start offset by 88 ms.
35. 0.000 0.000 ↓ 0.0

Seq Scan on pg_namespace n (cost=0.00..80.21 rows=121 width=68) (actual rows= loops=)

  • Rows out: 259 rows with 0.059 ms to first row, 9.480 ms to end, start offset by 66 ms.Slice statistics:
Total runtime : 81,166.378 ms