explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r2Wd : DEF RATE

Settings
# exclusive inclusive rows x rows loops node
1. 4,990.235 4,990.235 ↓ 0.0 0 1

Gather Motion 20:1 (slice10; segments: 20) (cost=0.00..6,567.50 rows=1 width=9) (actual time=4,990.235..4,990.235 rows=0 loops=1)

  • (slice0) Executor memory: 670K bytes.
  • (slice1) Executor memory: 134K bytes avg x 20 workers, 134K bytes max (seg0).
  • (slice2) Executor memory: 62K bytes avg x 20 workers, 62K bytes max (seg0).
  • (slice3) Executor memory: 394K bytes (entry db).
  • (slice4) Executor memory: 10095K bytes avg x 20 workers, 10095K bytes max (seg0).
  • (slice5) Executor memory: 62K bytes avg x 20 workers, 62K bytes max (seg0).
  • (slice6) Executor memory: 394K bytes (entry db).
  • (slice7) Executor memory: 65K bytes avg x 20 workers, 65K bytes max (seg0).* (slice8) Executor memory: 35065K bytes avg x 20 workers, 35066K bytes max (seg0). Work_mem: 15818K bytes max, 91142K 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.
  • (slice11)
  • (slice12) Memory used: 98304kBMemory wanted: 550246kBOptimizer: Pivotal Optimizer (GPORCA) version 3.88.0
2. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..6,567.50 rows=1 width=9) (never executed)

3. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.00..6,567.50 rows=1 width=18) (never executed)

  • 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.000 0.000 ↓ 0.0 0

Sort (cost=0.00..6,567.50 rows=1 width=53) (never executed)

  • 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: 660kB
5. 0.000 0.000 ↓ 0.0 0

Redistribute Motion 20:20 (slice9; segments: 20) (cost=0.00..6,567.50 rows=1 width=53) (never executed)

  • 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.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.00..6,567.50 rows=1 width=53) (never executed)

  • 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.000 0.000 ↓ 0.0 0

Sort (cost=0.00..6,567.50 rows=1 width=53) (never executed)

  • 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: 660kB
8. 0.000 0.000 ↓ 0.0 0

Redistribute Motion 20:20 (slice8; segments: 20) (cost=0.00..6,567.50 rows=1 width=53) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=0.00..6,567.50 rows=1 width=53) (never executed)

  • Hash Cond: (container.barcode = container_operation_1.barcode)
10. 0.000 0.000 ↓ 0.0 0

Hash Anti Join (cost=0.00..4,314.48 rows=1 width=52) (never executed)

  • Hash Cond: (container.barcode = container_operation.barcode)
  • Extra Text: (seg14) Initial batch 0:(seg14) Wrote 59809K bytes to inner workfile.(seg14) Wrote 3K bytes to outer workfile.(seg14) Initial batch 1:(seg14) Read 20395K bytes from inner workfile.(seg14) Wrote 11861K bytes to inner workfile.(seg14) Read 1K bytes from outer workfile.(seg14) Overflow batches 2..7:(seg14) Read 60156K bytes from inner workfile: 10026K avg x 6 nonempty batches, 14452K max.(seg14) Wrote 8882K bytes to inner workfile: 4441K avg x 2 overflowing batches, 5933K max.(seg14) Read 2K bytes from outer workfile: 1K avg x 5 nonempty batches, 1K max.(seg14) Hash chain length 2.2 avg, 12 max, using 883276 of 1048576 buckets.
11. 0.000 0.126 ↓ 44.0 44 1

Redistribute Motion 20:20 (slice1; segments: 20) (cost=0.00..6.12 rows=1 width=52) (actual time=0.037..0.126 rows=44 loops=1)

  • Hash Key: container.barcode
12. 0.862 0.862 ↓ 43.0 43 1

Index Scan using container_im_destination_impc_code_index on container (cost=0.00..6.12 rows=1 width=52) (actual time=0.354..0.862 rows=43 loops=1)

  • Index Cond: (im_destination_impc_code = 'RUEKAA'::text)
  • Filter: ((im_origin_impc_code = 'HKHKGH'::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. 1,320.073 3,922.265 ↓ 4.1 1,943,702 1

Hash (cost=2,012.21..2,012.21 rows=472,557 width=17) (actual time=3,922.265..3,922.265 rows=1,943,702 loops=1)

14. 294.105 2,602.192 ↓ 4.1 1,943,702 1

Result (cost=0.00..2,012.21 rows=472,557 width=17) (actual time=16.865..2,602.192 rows=1,943,702 loops=1)

15. 0.000 2,308.087 ↓ 4.1 1,943,702 1

Redistribute Motion 20:20 (slice4; segments: 20) (cost=0.00..2,004.17 rows=472,557 width=17) (actual time=16.852..2,308.087 rows=1,943,702 loops=1)

  • Hash Key: container_operation.barcode
16. 225.868 3,011.026 ↓ 4.1 1,943,013 1

Nested Loop (cost=0.00..1,979.03 rows=472,557 width=17) (actual time=11.139..3,011.026 rows=1,943,013 loops=1)

  • Join Filter: true
17. 0.183 1.352 ↑ 1.0 1 1

Assert (cost=0.00..6.00 rows=1 width=4) (actual time=1.340..1.352 rows=1 loops=1)

  • Assert Cond: ((row_number() OVER (?)) = 1)
18. 0.216 1.169 ↑ 20.0 1 1

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

19. 0.005 0.953 ↑ 1.0 1 1

Result (cost=0.00..6.00 rows=1 width=12) (actual time=0.054..0.953 rows=1 loops=1)

20. 0.030 0.948 ↑ 1.0 1 1

WindowAgg (cost=0.00..6.00 rows=1 width=12) (actual time=0.049..0.948 rows=1 loops=1)

21. 0.760 0.918 ↑ 1.0 1 1

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

22. 0.158 0.158 ↑ 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.157..0.158 rows=1 loops=1)

  • Index Cond: (code = 'CONTAINER_FORMING'::text)
23. 157.412 2,783.806 ↓ 4.1 1,943,013 1

Sequence (cost=0.00..1,921.93 rows=472,557 width=17) (actual time=9.488..2,783.806 rows=1,943,013 loops=1)

24. 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)
25. 2,590.014 2,626.394 ↓ 4.1 1,943,013 1

Dynamic Bitmap Heap Scan on container_operation (dynamic scan id: 1) (cost=0.00..1,921.93 rows=472,557 width=17) (actual time=9.434..2,626.394 rows=1,943,013 loops=1)

  • Recheck Cond: (type_id = operation_type.id)
  • Heap Blocks: exact=87320824
26. 36.380 36.380 ↓ 0.0 1 85

Dynamic Bitmap Index Scan on title_bmp_idx (cost=0.00..0.00 rows=0 width=0) (actual time=0.006..0.428 rows=1 loops=85)

  • Index Cond: (type_id = operation_type.id)
27. 0.000 0.000 ↓ 0.0 0

Hash (cost=2,012.68..2,012.68 rows=472,557 width=18) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..2,012.68 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..2,004.17 rows=472,557 width=17) (never executed)

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

Nested Loop (cost=0.00..1,979.03 rows=472,557 width=17) (never executed)

  • Join Filter: true
31. 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)
32. 0.000 0.000 ↓ 0.0 0

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

33. 0.000 0.000 ↓ 0.0 0

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

34. 0.000 0.000 ↓ 0.0 0

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

35. 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)

36. 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)
37. 0.000 0.000 ↓ 0.0 0

Sequence (cost=0.00..1,921.93 rows=472,557 width=17) (never executed)

38. 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)
39. 0.000 0.000 ↓ 0.0 0

Dynamic Bitmap Heap Scan on container_operation container_operation_1 (dynamic scan id: 2) (cost=0.00..1,921.93 rows=472,557 width=17) (never executed)

  • Recheck Cond: (type_id = operation_type_1.id)
  • Heap Blocks: exact=85281144
40. 0.000 0.000 ↓ 0.0 0

Dynamic Bitmap Index Scan on title_bmp_idx (cost=0.00..0.00 rows=0 width=0) (never executed)

  • Index Cond: (type_id = operation_type_1.id)
Planning time : 206.024 ms
Execution time : 5,087.238 ms