explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4kBU

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

Insert (cost=0.00..1,836.50 rows=1 width=40) (actual rows= loops=)

  • Rows out: Avg 13786.6 rows x 108 workers. Max 14070 rows (seg91) with 9146 ms to first row, 9253 ms to end.
  • Executor memory: 1K bytes avg, 1K bytes max (seg0).
  • (slice0) * Executor memory: 161564K bytes avg x 108 workers, 161564K bytes max (seg0). Work_mem: 114555K bytes max, 370274K bytes wanted.
  • Memory used: 344064K bytes
  • Memory wanted: 1481992K bytes
2. 0.000 0.000 ↓ 0.0

Result (cost=0.00..1,836.50 rows=1 width=44) (actual rows= loops=)

  • Rows out: Avg 13786.6 rows x 108 workers. Max 14070 rows (seg91) with 9146 ms to first row, 9230 ms to end.
3. 0.000 0.000 ↓ 0.0

Result (cost=0.00..1,836.50 rows=1 width=49) (actual rows= loops=)

  • Filter: (sum(entity_version.flg_act)) > 0
  • Rows out: Avg 13786.6 rows x 108 workers. Max 14070 rows (seg91) with 9146 ms to first row, 9228 ms to end.
4. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.00..1,836.50 rows=1 width=57) (actual rows= loops=)

  • Group By: action_entity_oper.dwh_entity_id, action_entity_oper.dwh_entity_uuid, entity_version.dwh_version_num, action_entity_oper.operation_date_time, action_entity_oper.oper_address_data_index
  • Rows out: Avg 13786.7 rows x 108 workers. Max 14070 rows (seg91) with 9146 ms to first row, 9225 ms to end.
5. 0.000 0.000 ↓ 0.0

Sort (cost=0.00..1,836.50 rows=1 width=53) (actual rows= loops=)

  • Sort Key: action_entity_oper.dwh_entity_id, action_entity_oper.dwh_entity_uuid, entity_version.dwh_version_num, action_entity_oper.operation_date_time, action_entity_oper.oper_address_data_index
  • Sort Method: quicksort Max Memory: 35833KB Avg Memory: 35833KB (108 segments)
  • Rows out: Avg 99398.8 rows x 108 workers. Max 105306 rows (seg48) with 9308 ms to first row, 9336 ms to end.
  • Executor memory: 35833K bytes avg, 35833K bytes max (seg0).
  • Work_mem used: 35833K bytes avg, 35833K bytes max (seg0). Workfile: (0 spilling)
6. 0.000 0.000 ↓ 0.0

Result (cost=0.00..1,836.50 rows=1 width=53) (actual rows= loops=)

  • Filter: stage.entity_2ap2bp_tmp.dwh_entity_uuid IS NULL
  • Rows out: Avg 99398.8 rows x 108 workers. Max 105306 rows (seg48) with 4768 ms to first row, 9129 ms to end.
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=0.00..1,836.50 rows=1 width=69) (actual rows= loops=)

  • Hash Cond: action_entity_oper.dwh_entity_uuid = stage.entity_2ap2bp_tmp.dwh_entity_uuid AND action_entity_oper.oper_address_data_index::text = stage.entity_2ap2bp_tmp.oper_address_data_index::text AND date(action_entity_oper.operation_date_time) = stage.entity_2ap2bp_tmp.operation_date
  • Rows out: Avg 99398.8 rows x 108 workers. Max 105306 rows (seg48) with 4768 ms to first row, 9115 ms to end.
8. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.00..1,405.50 rows=1 width=53) (actual rows= loops=)

  • Hash Cond: action_entity_oper.dwh_entity_uuid = entity_version.dwh_entity_uuid
  • Join Filter: action_entity_oper.operation_date_time >= entity_version.begin_date AND action_entity_oper.operation_date_time <= entity_version.end_date
  • Rows out: Avg 99398.8 rows x 108 workers. Max 105306 rows (seg48) with 4764 ms to first row, 9081 ms to end.
  • Executor memory: 114555K bytes avg, 114555K bytes max (seg0).
  • Work_mem used: 114555K bytes avg, 114555K bytes max (seg0). Workfile: (108 spilling)
  • Work_mem wanted: 368923K bytes avg, 370274K bytes max (seg36) to lessen workfile I/O affecting 108 workers.
  • (seg36) Initial batch 0:
  • (seg36) Wrote 225680K bytes to inner workfile.
  • (seg36) Wrote 784K bytes to outer workfile.
  • (seg36) Overflow batches 1..3:
  • (seg36) Read 271959K bytes from inner workfile: 90653K avg x 3 nonempty batches, 121332K max.
  • (seg36) Wrote 46336K bytes to inner workfile.
  • (seg36) Read 809K bytes from outer workfile: 270K avg x 3 nonempty batches, 277K max.
  • (seg36) Hash chain length 8.5 avg, 104 max, using 698109 of 1048576 buckets.
  • (seg48) Initial batch 0:
  • (seg48) Wrote 225136K bytes to inner workfile.
  • (seg48) Wrote 768K bytes to outer workfile.
  • (seg48) Overflow batches 1..3:
  • (seg48) Read 271511K bytes from inner workfile: 90504K avg x 3 nonempty batches, 121640K max.
  • (seg48) Wrote 46416K bytes to inner workfile.
  • (seg48) Read 803K bytes from outer workfile: 268K avg x 3 nonempty batches, 270K max.
  • (seg48) Hash chain length 8.5 avg, 547 max, using 697028 of 1048576 buckets.
9. 0.000 0.000 ↓ 0.0

Table Scan on action_entity_oper (cost=0.00..959.23 rows=33,062 width=45) (actual rows= loops=)

  • Filter: dwh_proc_id = 1
  • Rows out: Avg 16261.3 rows x 108 workers. Max 16563 rows (seg39) with 1.893 ms to first row, 2257 ms to end.
10. 0.000 0.000 ↓ 0.0

Hash (cost=431.00..431.00 rows=1 width=40) (actual rows= loops=)

  • Rows in: Avg 1475536.7 rows x 108 workers. Max 1485359 rows (seg56) with 4754 ms to end, start offset by 24 ms.
11. 0.000 0.000 ↓ 0.0

Table Scan on entity_version (cost=0.00..431.00 rows=1 width=40) (actual rows= loops=)

  • Rows out: Avg 5902761.6 rows x 108 workers. Max 5924370 rows (seg36) with 0.179 ms to first row, 1436 ms to end.
12. 0.000 0.000 ↓ 0.0

Hash (cost=431.00..431.00 rows=1 width=28) (actual rows= loops=)

  • Rows in: 0 rows (seg0) with 497 ms to end, start offset by 23 ms.
13. 0.000 0.000 ↓ 0.0

Table Scan on entity_2ap2bp_tmp (cost=0.00..431.00 rows=1 width=28) (actual rows= loops=)

  • Rows out: 0 rows (seg0) with 497 ms to end.