explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o2xy : abc

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

Gather Motion 64:1 (slice6; segments: 64) (cost=2,219,319.96..2,219,326.64 rows=2,673 width=2,814) (actual rows= loops=)

  • Merge Key: best_datetime
  • Rows out: 8391 rows at destination with 3338 ms to first row, 3349 ms to end, start offset by 3227 ms.
  • (slice0) Executor memory: 2949K bytes.
  • (slice1) Executor memory: 1007K bytes avg x 64 workers, 1111K bytes max (seg11).
  • (slice2) Executor memory: 3219K bytes avg x 64 workers, 3219K bytes max (seg0). Work_mem: 12K bytes max.
  • (slice3) Executor memory: 3171K bytes avg x 64 workers, 3531K bytes max (seg9). Work_mem: 10K bytes max.
  • (slice4) * Executor memory: 91797K bytes avg x 64 workers, 91938K bytes max (seg21). Work_mem: 63856K bytes max, 93537K bytes wanted.
  • (slice5) Executor memory: 1502K bytes avg x 64 workers, 1502K bytes max (seg0).
  • (slice6) Executor memory: 19137K bytes avg x 64 workers, 19250K bytes max (seg10). Work_mem: 5133K bytes max.
  • Memory used: 256000K bytes
  • Memory wanted: 469180K bytes
2. 0.000 0.000 ↓ 0.0

Sort (cost=2,219,319.96..2,219,326.64 rows=42 width=2,814) (actual rows= loops=)

  • Sort Key: pf.best_datetime
  • Rows out: Avg 131.1 rows x 64 workers. Max 587 rows (seg10) with 3277 ms to end, start offset by 3286 ms.
  • Executor memory: 82K bytes avg, 145K bytes max (seg10).
  • Work_mem used: 82K bytes avg, 145K bytes max (seg10). Workfile: (0 spilling)
3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,063,196.18..2,219,167.82 rows=42 width=2,814) (actual rows= loops=)

  • Hash Cond: pf.to_flght_rprt_id = tk.flght_rprt_id
  • Rows out: Avg 131.1 rows x 64 workers. Max 587 rows (seg10) with 1483 ms to first row, 3274 ms to end, start offset by 3286 ms.
  • Executor memory: 5102K bytes avg, 5133K bytes max (seg45).
  • Work_mem used: 5102K bytes avg, 5133K bytes max (seg45). Workfile: (0 spilling)
  • (seg10) Hash chain length 1.5 avg, 9 max, using 78073 of 131111 buckets.
4. 0.000 0.000 ↓ 0.0

Redistribute Motion 64:64 (slice4; segments: 64) (cost=1,427,050.78..1,582,976.69 rows=42 width=2,794) (actual rows= loops=)

  • Hash Key: pf.to_flght_rprt_id
  • Rows out: Avg 131.1 rows x 64 workers at destination. Max 587 rows (seg10) with 911 ms to first row, 2690 ms to end, start offset by 3859 ms.
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,427,050.78..1,582,923.23 rows=42 width=2,794) (actual rows= loops=)

  • Hash Cond: rf.st_message_seq_id = leap_rmd_param_result.message_seq_id
  • Rows out: Avg 131.1 rows x 64 workers. Max 877 rows (seg10) with 1482 ms to first row, 3217 ms to end, start offset by 3286 ms.
  • Executor memory: 63853K bytes avg, 63856K bytes max (seg54).
  • Work_mem used: 63853K bytes avg, 63856K bytes max (seg54). Workfile: (64 spilling)
  • Work_mem wanted: 92132K bytes avg, 93537K bytes max (seg9) to lessen workfile I/O affecting 64 workers.
  • (seg9) Initial batch 0:
  • (seg9) Wrote 46992K bytes to inner workfile.
  • (seg9) Overflow batch 1:
  • (seg9) Read 46985K bytes from inner workfile.
  • (seg9) Read 7K bytes from outer workfile.
  • (seg9) Hash chain length 1.7 avg, 8 max, using 10317 of 16438 buckets.
  • (seg10) Initial batch 0:
  • (seg10) Wrote 46096K bytes to inner workfile.
  • (seg10) Wrote 80K bytes to outer workfile.
  • (seg10) Overflow batch 1:
  • (seg10) Read 46098K bytes from inner workfile.
  • (seg10) Read 85K bytes from outer workfile.
  • (seg10) Hash chain length 1.7 avg, 8 max, using 10312 of 16438 buckets.
6. 0.000 0.000 ↓ 0.0

Redistribute Motion 64:64 (slice3; segments: 64) (cost=99,775.05..158,717.40 rows=42 width=91) (actual rows= loops=)

  • Hash Key: rf.st_message_seq_id
  • Rows out: Avg 131.1 rows x 64 workers at destination. Max 877 rows (seg10) with 1195 ms to first row, 2852 ms to end, start offset by
7. 0.000 0.000 ↓ 0.0

Hash Join (cost=99,775.05..158,663.95 rows=42 width=91) (actual rows= loops=)

  • Hash Cond: pf.phm_flight_def_id = cf.phm_flight_def_id
  • Rows out: Avg 131.1 rows x 64 workers. Max 155 rows (seg44) with 1480 ms to first row, 1512 ms to end, start offset by 3260 ms.
  • Executor memory: 8K bytes avg, 10K bytes max (seg44).
  • Work_mem used: 8K bytes avg, 10K bytes max (seg44). Workfile: (0 spilling)
  • (seg9) Hash chain length 1.0 avg, 1 max, using 146 of 131111 buckets.
  • (seg44) Hash chain length 1.0 avg, 1 max, using 155 of 131111 buckets.
8. 0.000 0.000 ↓ 0.0

Seq Scan on leap_phm_flight pf (cost=0.00..55,889.18 rows=18,540 width=78) (actual rows= loops=)

  • Filter: best_datetime > '2012-01-01'::date
  • Rows out: Avg 18523.4 rows x 64 workers. Max 18831 rows (seg54) with 0.260 ms to first row, 1681 ms to end, start offset
9. 0.000 0.000 ↓ 0.0

Hash (cost=99,722.13..99,722.13 rows=67 width=29) (actual rows= loops=)

  • Rows in: Avg 131.1 rows x 64 workers. Max 155 rows (seg44) with 1478 ms to end, start offset by 3261 ms.
10. 0.000 0.000 ↓ 0.0

Redistribute Motion 64:64 (slice2; segments: 64) (cost=77,454.09..99,722.13 rows=67 width=29) (actual rows= loops=)

  • Hash Key: cf.phm_flight_def_id
  • Rows out: Avg 131.1 rows x 64 workers at destination. Max 155 rows (seg44) with 1460 ms to first row, 1478 ms to en
11. 0.000 0.000 ↓ 0.0

Hash Join (cost=77,454.09..99,637.45 rows=67 width=29) (actual rows= loops=)

  • Hash Cond: rf.rmd_flight_def_id = cf.rmd_flight_def_id
  • Rows out: Avg 131.1 rows x 64 workers. Max 158 rows (seg14) with 1457 ms to first row, 1470 ms to end, start
  • Executor memory: 10K bytes avg, 12K bytes max (seg14).
  • Work_mem used: 10K bytes avg, 12K bytes max (seg14). Workfile: (0 spilling)
  • (seg14) Hash chain length 1.0 avg, 2 max, using 157 of 131111 buckets.
12. 0.000 0.000 ↓ 0.0

Seq Scan on leap_rmd_flight rf (cost=0.00..19,249.55 rows=18,006 width=21) (actual rows= loops=)

  • Rows out: Avg 18008.0 rows x 64 workers. Max 18327 rows (seg15) with 0.266 ms to first row, 6.970 ms to
13. 0.000 0.000 ↓ 0.0

Hash (cost=77,364.93..77,364.93 rows=112 width=30) (actual rows= loops=)

  • Rows in: Avg 131.1 rows x 64 workers. Max 158 rows (seg14) with 1455 ms to end, start offset by 3257 ms
14. 0.000 0.000 ↓ 0.0

Redistribute Motion 64:64 (slice1; segments: 64) (cost=0.00..77,364.93 rows=112 width=30) (actual rows= loops=)

  • Hash Key: cf.rmd_flight_def_id
  • Rows out: Avg 178.2 rows x 64 workers at destination. Max 3136 rows (seg10) with 0.058 ms to firs
15. 0.000 0.000 ↓ 0.0

Seq Scan on leap_common_flight cf (cost=0.00..77,222.27 rows=112 width=30) (actual rows= loops=)

  • Filter: engine_serial_number::text = ANY ('{598113,598114}'::text[])
  • Rows out: Avg 178.2 rows x 64 workers. Max 395 rows (seg10) with 0.114 ms to first row, 923
16. 0.000 0.000 ↓ 0.0

Hash (cost=1,215,981.04..1,215,981.04 rows=18,024 width=2,723) (actual rows= loops=)

  • Rows in: Avg 8805.8 rows x 64 workers. Max 9032 rows (seg35) with 347 ms to end, start offset by 3250 ms.
17. 0.000 0.000 ↓ 0.0

Seq Scan on leap_rmd_param_result (cost=0.00..1,215,981.04 rows=18,024 width=2,723) (actual rows= loops=)

  • Filter: report_type::text = 'Engine Start'::text
  • Rows out: Avg 17637.3 rows x 64 workers. Max 17920 rows (seg9) with 1.643 ms to first row, 185 ms to end, start offset by 3253
18. 0.000 0.000 ↓ 0.0

Hash (cost=541,564.40..541,564.40 rows=118,227 width=28) (actual rows= loops=)

  • Rows in: Avg 118453.7 rows x 64 workers. Max 119102 rows (seg35) with 609 ms to end, start offset by 3251 ms.
19. 0.000 0.000 ↓ 0.0

Redistribute Motion 64:64 (slice5; segments: 64) (cost=0.00..541,564.40 rows=118,227 width=28) (actual rows= loops=)

  • Hash Key: tk.flght_rprt_id
  • Rows out: Avg 118453.7 rows x 64 workers at destination. Max 119102 rows (seg35) with 45 ms to first row, 545 ms to end, start offset by 32
20. 0.000 0.000 ↓ 0.0

Seq Scan on leap_phm_param_result tk (cost=0.00..390,234.80 rows=118,227 width=28) (actual rows= loops=)

  • Rows out: Avg 118453.7 rows x 64 workers. Max 186824 rows (seg26) with 0.625 ms to first row, 548 ms to end, start offset by 3245 ms.