explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Awcx : DEF RATE

Settings
# exclusive inclusive rows x rows loops node
1. 1.828 13,557.180 ↓ 587.0 587 1

Gather Motion 20:1 (slice10; segments: 20) (cost=0.00..28,239.04 rows=1 width=9) (actual time=13,556.716..13,557.180 rows=587 loops=1)

  • (slice0) Executor memory: 637K bytes.
  • (slice1) Executor memory: 125K bytes avg x 20 workers, 125K bytes max (seg0).
  • (slice2) Executor memory: 62K bytes avg x 20 workers, 62K bytes max (seg0).
  • (slice3) Executor memory: 602K bytes (entry db).
  • (slice4) Executor memory: 4770K bytes avg x 20 workers, 4770K bytes max (seg0). Work_mem: 1K bytes max.
  • (slice5) Executor memory: 62K bytes avg x 20 workers, 62K bytes max (seg0).
  • (slice6) Executor memory: 362K bytes (entry db).
  • (slice7) Executor memory: 1106K bytes avg x 20 workers, 1106K bytes max (seg0).* (slice8) Executor memory: 43186K bytes avg x 20 workers, 43186K bytes max (seg0). Work_mem: 24277K bytes max, 52906K bytes wanted.
  • (slice9) Executor memory: 138K bytes avg x 20 workers, 138K bytes max (seg0). Work_mem: 65K bytes max.
  • (slice10) Executor memory: 130K bytes avg x 20 workers, 130K bytes max (seg0). Work_mem: 65K bytes max.Memory used: 98304kBMemory wanted: 426443kBOptimizer: Pivotal Optimizer (GPORCA) version 3.88.0
2. 0.016 13,555.352 ↓ 41.0 41 1

Result (cost=0.00..28,239.04 rows=1 width=9) (actual time=13,555.322..13,555.352 rows=41 loops=1)

3. 0.038 13,555.336 ↓ 41.0 41 1

GroupAggregate (cost=0.00..28,239.04 rows=1 width=18) (actual time=13,555.313..13,555.336 rows=41 loops=1)

  • Group Key: container.im_formation_date, container.barcode, container.im_category, container.im_origin_impc_code, container.im_destination_impc_code, container.barcode_suffix, container.ctid, container.gp_segment_id, ""outer"".ColRef_0124
4. 0.718 13,555.298 ↓ 41.0 41 1

Sort (cost=0.00..28,239.04 rows=1 width=53) (actual time=13,555.296..13,555.298 rows=41 loops=1)

  • Sort Key: container.im_formation_date, container.barcode, container.im_category, container.im_origin_impc_code, container.im_destination_impc_code, container.barcode_suffix, container.ctid, container.gp_segment_id, ""outer"".ColRef_0124
  • Sort Method: quicksort Memory: 980kB
5. 0.723 13,554.580 ↓ 41.0 41 1

Redistribute Motion 20:20 (slice9; segments: 20) (cost=0.00..28,239.04 rows=1 width=53) (actual time=13,554.433..13,554.580 rows=41 loops=1)

  • Hash Key: container.im_formation_date, container.barcode, container.im_category, container.im_origin_impc_code, container.im_destination_impc_code, container.barcode_suffix, container.ctid, container.gp_segment_id, ""outer"".ColRef_0124
6. 0.043 13,553.857 ↓ 40.0 40 1

GroupAggregate (cost=0.00..28,239.04 rows=1 width=53) (actual time=13,553.836..13,553.857 rows=40 loops=1)

  • Group Key: container.im_formation_date, container.barcode, container.im_category, container.im_origin_impc_code, container.im_destination_impc_code, container.barcode_suffix, container.ctid, container.gp_segment_id, ""outer"".ColRef_0124
7. 0.595 13,553.814 ↓ 40.0 40 1

Sort (cost=0.00..28,239.04 rows=1 width=53) (actual time=13,553.814..13,553.814 rows=40 loops=1)

  • Sort Key: container.im_formation_date, container.barcode, container.im_category, container.im_origin_impc_code, container.im_destination_impc_code, container.barcode_suffix, container.ctid, container.gp_segment_id, ""outer"".ColRef_0124
  • Sort Method: quicksort Memory: 980kB
8. 33.721 13,553.219 ↓ 40.0 40 1

Redistribute Motion 20:20 (slice8; segments: 20) (cost=0.00..28,239.04 rows=1 width=53) (actual time=13,516.114..13,553.219 rows=40 loops=1)

9. 3.612 13,519.498 ↓ 44.0 44 1

Hash Left Join (cost=0.00..28,239.04 rows=1 width=53) (actual time=13,047.375..13,519.498 rows=44 loops=1)

  • Hash Cond: (container.barcode = container_operation_1.barcode)
  • Extra Text: (seg6) Hash chain length 0.0 avg, 0 max, using 0 of 131072 buckets.Initial batch 0:
10. 472.926 13,515.886 ↓ 44.0 44 1

Hash Anti Join (cost=0.00..16,099.29 rows=1 width=52) (actual time=13,044.383..13,515.886 rows=44 loops=1)

  • Hash Cond: (container.barcode = container_operation.barcode)
  • Extra Text: (seg6) Initial batch 0:(seg6) Wrote 29712K bytes to inner workfile.(seg6) Wrote 3K bytes to outer workfile.(seg6) Overflow batches 1..3:(seg6) Read 38842K bytes from inner workfile: 12948K avg x 3 nonempty batches, 19035K max.(seg6) Wrote 9131K bytes to inner workfile.(seg6) Read 3K bytes from outer workfile: 1K avg x 3 nonempty batches, 1K max.(seg6) Hash chain length 2.4 avg, 12 max, using 462617 of 524288 buckets.
  • Extra Text: (seg7) Initial batch 0:(seg7) Wrote 29746K bytes to inner workfile.(seg7) Wrote 2K bytes to outer workfile.(seg7) Overflow batches 1..3:(seg7) Read 38834K bytes from inner workfile: 12945K avg x 3 nonempty batches, 19010K max.(seg7) Wrote 9088K bytes to inner workfile.(seg7) Read 2K bytes from outer workfile: 1K avg x 3 nonempty batches, 1K max.(seg7) Hash chain length 2.4 avg, 15 max, using 462944 of 524288 buckets.Initial batch 0:
11. 0.000 0.094 ↓ 44.0 44 1

Redistribute Motion 20:20 (slice1; segments: 20) (cost=0.00..1,904.19 rows=1 width=52) (actual time=0.023..0.094 rows=44 loops=1)

  • Hash Key: container.barcode
12. 2,290.379 2,290.379 ↓ 43.0 43 1

Seq Scan on container (cost=0.00..1,904.19 rows=1 width=52) (actual time=112.624..2,290.379 rows=43 loops=1)

  • Filter: ((im_origin_impc_code = 'HKHKGH'::text) AND (im_destination_impc_code = 'RUEKAA'::text) AND (date_part('year'::text, im_formation_date) = 2020::double precision) AND (im_category = 'C'::text) AND (im_formation_date >= '2019-05-21 11:37:08.421+00'::timestamp with time zone) AND (im_formation_date < '2021-05-21 11:37:08.423+00'::timestamp with time zone))
13. 832.285 13,042.866 ↓ 2.4 1,128,648 1

Hash (cost=11,898.94..11,898.94 rows=472,557 width=17) (actual time=13,042.866..13,042.866 rows=1,128,648 loops=1)

14. 157.154 12,210.581 ↓ 2.4 1,128,648 1

Result (cost=0.00..11,898.94 rows=472,557 width=17) (actual time=266.633..12,210.581 rows=1,128,648 loops=1)

15. 0.000 12,053.427 ↓ 2.4 1,128,648 1

Redistribute Motion 20:20 (slice4; segments: 20) (cost=0.00..11,890.91 rows=472,557 width=17) (actual time=266.617..12,053.427 rows=1,128,648 loops=1)

  • Hash Key: container_operation.barcode
16. 3,915.299 12,260.436 ↓ 2.4 1,128,687 1

Hash Join (cost=0.00..11,865.76 rows=472,557 width=17) (actual time=6.427..12,260.436 rows=1,128,687 loops=1)

  • Hash Cond: ((container_operation.type_id)::integer = operation_type.id)
  • Extra Text: (seg2) Hash chain length 1.0 avg, 1 max, using 1 of 131072 buckets.
17. 2,425.475 8,344.022 ↓ 1.0 30,773,776 1

Sequence (cost=0.00..4,468.64 rows=30,716,169 width=19) (actual time=4.671..8,344.022 rows=30,773,776 loops=1)

18. 0.000 0.000 ↓ 0.0 0

Partition Selector for container_operation (dynamic scan id: 1) (cost=10.00..100.00 rows=5 width=4) (never executed)

  • Partitions selected: 85 (out of 85)
19. 5,918.547 5,918.547 ↓ 1.0 30,773,776 1

Dynamic Seq Scan on container_operation (dynamic scan id: 1) (cost=0.00..4,468.64 rows=30,716,169 width=19) (actual time=4.619..5,918.547 rows=30,773,776 loops=1)

  • Partitions scanned: Avg 85.0 (out of 85) x 20 workers. Max 85 parts (seg0).
20. 0.004 1.115 ↑ 1.0 1 1

Hash (cost=6.00..6.00 rows=1 width=4) (actual time=1.115..1.115 rows=1 loops=1)

21. 0.091 1.111 ↑ 1.0 1 1

Assert (cost=0.00..6.00 rows=1 width=4) (actual time=1.110..1.111 rows=1 loops=1)

  • Assert Cond: ((row_number() OVER (?)) = 1)
22. 0.000 1.020 ↑ 20.0 1 1

Broadcast Motion 1:20 (slice3) (cost=0.00..6.00 rows=20 width=12) (actual time=1.019..1.020 rows=1 loops=1)

23. 0.004 1.731 ↑ 1.0 1 1

Result (cost=0.00..6.00 rows=1 width=12) (actual time=0.438..1.731 rows=1 loops=1)

24. 0.028 1.727 ↑ 1.0 1 1

WindowAgg (cost=0.00..6.00 rows=1 width=12) (actual time=0.434..1.727 rows=1 loops=1)

25. 1.527 1.699 ↑ 1.0 1 1

Gather Motion 20:1 (slice2; segments: 20) (cost=0.00..6.00 rows=1 width=4) (actual time=0.408..1.699 rows=1 loops=1)

26. 0.172 0.172 ↑ 1.0 1 1

Index Scan using operation_type_code_key on operation_type (cost=0.00..6.00 rows=1 width=4) (actual time=0.171..0.172 rows=1 loops=1)

  • Index Cond: (code = 'OPENING'::text)
27. 0.000 0.000 ↓ 0.0 0

Hash (cost=11,899.41..11,899.41 rows=472,557 width=18) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..11,899.41 rows=472,557 width=18) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Redistribute Motion 20:20 (slice7; segments: 20) (cost=0.00..11,890.91 rows=472,557 width=17) (never executed)

  • Hash Key: container_operation_1.barcode
30. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=0.00..11,865.76 rows=472,557 width=17) (never executed)

  • Hash Cond: ((container_operation_1.type_id)::integer = operation_type_1.id)
31. 0.000 0.000 ↓ 0.0 0

Sequence (cost=0.00..4,468.64 rows=30,716,169 width=19) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Partition Selector for container_operation (dynamic scan id: 2) (cost=10.00..100.00 rows=5 width=4) (never executed)

  • Partitions selected: 85 (out of 85)
33. 0.000 0.000 ↓ 0.0 0

Dynamic Seq Scan on container_operation container_operation_1 (dynamic scan id: 2) (cost=0.00..4,468.64 rows=30,716,169 width=19) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Hash (cost=6.00..6.00 rows=1 width=4) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Assert (cost=0.00..6.00 rows=1 width=4) (never executed)

  • Assert Cond: ((row_number() OVER (?)) = 1)
36. 0.000 0.000 ↓ 0.0 0

Broadcast Motion 1:20 (slice6) (cost=0.00..6.00 rows=20 width=12) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..6.00 rows=1 width=12) (never executed)

38. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=0.00..6.00 rows=1 width=12) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Gather Motion 20:1 (slice5; segments: 20) (cost=0.00..6.00 rows=1 width=4) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Index Scan using operation_type_code_key on operation_type operation_type_1 (cost=0.00..6.00 rows=1 width=4) (never executed)

  • Index Cond: (code = 'IM_CONTAINER_RECALCULATING'::text)
Planning time : 290.831 ms
Execution time : 13,695.463 ms