explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gv1o : DEF RATE

Settings
# exclusive inclusive rows x rows loops node
1. 11,915.642 11,915.642 ↓ 0.0 0 1

Gather Motion 20:1 (slice8; segments: 20) (cost=0.00..11,980.12 rows=1 width=9) (actual time=11,915.642..11,915.642 rows=0 loops=1)

  • (slice0) Executor memory: 638K bytes.
  • (slice1) Executor memory: 134K bytes avg x 20 workers, 134K bytes max (seg0).
  • (slice2) Executor memory: 2014K bytes avg x 20 workers, 2014K bytes max (seg0).
  • (slice3) Executor memory: 62K bytes avg x 20 workers, 62K bytes max (seg0).
  • (slice4) Executor memory: 330K bytes (entry db).
  • (slice5) Executor memory: 65K bytes avg x 20 workers, 65K bytes max (seg0).* (slice6) Executor memory: 69680K bytes avg x 20 workers, 69680K bytes max (seg0). Work_mem: 67218K bytes max, 102465K bytes wanted.
  • (slice7) Executor memory: 138K bytes avg x 20 workers, 138K bytes max (seg0). Work_mem: 65K bytes max.
  • (slice8) Executor memory: 130K bytes avg x 20 workers, 130K bytes max (seg0). Work_mem: 65K bytes max.
  • (slice9) Memory used: 98304kBMemory wanted: 617785kBOptimizer: Pivotal Optimizer (GPORCA) version 3.88.0
2. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..11,980.12 rows=1 width=9) (never executed)

3. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.00..11,980.12 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_0113
4. 0.000 0.000 ↓ 0.0 0

Sort (cost=0.00..11,980.12 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_0113
  • Sort Method: quicksort Memory: 660kB
5. 0.000 0.000 ↓ 0.0 0

Redistribute Motion 20:20 (slice7; segments: 20) (cost=0.00..11,980.12 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_0113
6. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.00..11,980.12 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_0113
7. 0.000 0.000 ↓ 0.0 0

Sort (cost=0.00..11,980.12 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_0113
  • Sort Method: quicksort Memory: 660kB
8. 0.000 0.000 ↓ 0.0 0

Redistribute Motion 20:20 (slice6; segments: 20) (cost=0.00..11,980.12 rows=1 width=53) (never executed)

9. 0.000 0.000 ↓ 0.0 0

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

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

Result (cost=0.00..9,727.10 rows=1 width=52) (never executed)

  • Filter: (COALESCE((count()), 0::bigint) = 0::bigint)
11. 0.033 11,741.432 ↓ 44.0 44 1

Result (cost=0.00..9,727.10 rows=1 width=75) (actual time=10,942.014..11,741.432 rows=44 loops=1)

12. 810.347 11,741.399 ↓ 44.0 44 1

Hash Left Join (cost=0.00..9,727.10 rows=1 width=60) (actual time=10,941.993..11,741.399 rows=44 loops=1)

  • Hash Cond: (container.barcode = container_operation.barcode)
  • Extra Text: (seg4) Initial batch 0:(seg4) Wrote 58057K bytes to inner workfile.(seg4) Wrote 3K bytes to outer workfile.(seg4) Initial batch 1:(seg4) Read 23430K bytes from inner workfile.(seg4) Wrote 15141K bytes to inner workfile.(seg4) Read 1K bytes from outer workfile.(seg4) Overflow batches 2..7:(seg4) Read 61064K bytes from inner workfile: 10178K avg x 6 nonempty batches, 15776K max.(seg4) Wrote 11296K bytes to inner workfile: 5648K avg x 2 overflowing batches, 7519K max.(seg4) Read 2K bytes from outer workfile: 1K avg x 6 nonempty batches, 1K max.(seg4) Hash chain length 1.9 avg, 10 max, using 807314 of 1048576 buckets.Initial batch 0:
  • Extra Text: (seg6) Initial batch 0:(seg6) Wrote 58037K bytes to inner workfile.(seg6) Wrote 4K bytes to outer workfile.(seg6) Initial batch 1:(seg6) Read 23363K bytes from inner workfile.(seg6) Wrote 15075K bytes to inner workfile.(seg6) Read 1K bytes from outer workfile.(seg6) Overflow batches 2..7:(seg6) Read 61032K bytes from inner workfile: 10172K avg x 6 nonempty batches, 15814K max.(seg6) Wrote 11284K bytes to inner workfile: 5642K avg x 2 overflowing batches, 7522K max.(seg6) Read 4K bytes from outer workfile: 1K avg x 6 nonempty batches, 1K max.(seg6) Hash chain length 1.9 avg, 10 max, using 807611 of 1048576 buckets.1541749 groups total in 32 batches; 1 overflows; 1541749 spill groups.
13. 0.000 0.140 ↓ 44.0 44 1

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

  • Hash Key: container.barcode
14. 0.372 0.372 ↓ 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.187..0.372 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))
15. 983.365 10,930.912 ↓ 2.4 1,543,423 1

Hash (cost=5,443.69..5,443.69 rows=640,792 width=25) (actual time=10,930.912..10,930.912 rows=1,543,423 loops=1)

16. 198.468 9,947.547 ↓ 2.4 1,543,423 1

Result (cost=0.00..5,443.69 rows=640,792 width=25) (actual time=8,999.638..9,947.547 rows=1,543,423 loops=1)

17. 2,718.028 9,749.079 ↓ 2.4 1,543,423 1

HashAggregate (cost=0.00..5,427.67 rows=640,792 width=25) (actual time=8,999.609..9,749.079 rows=1,543,423 loops=1)

  • Group Key: container_operation.barcode
  • Extra Text: (seg6) 1541749 groups total in 32 batches; 1 overflows; 1541749 spill groups.(seg6) Hash chain length 3.4 avg, 16 max, using 752463 of 786432 buckets; total 13 expansions.
18. 0.000 7,031.051 ↓ 1.0 1,543,832 1

Redistribute Motion 20:20 (slice2; segments: 20) (cost=0.00..5,236.04 rows=1,487,347 width=17) (actual time=126.936..7,031.051 rows=1,543,832 loops=1)

  • Hash Key: container_operation.barcode
19. 170.703 7,192.075 ↓ 1.0 1,543,209 1

Result (cost=0.00..5,156.90 rows=1,487,347 width=17) (actual time=8.459..7,192.075 rows=1,543,209 loops=1)

20. 124.516 7,021.372 ↓ 1.0 1,543,209 1

Sequence (cost=0.00..5,156.90 rows=1,487,347 width=27) (actual time=8.451..7,021.372 rows=1,543,209 loops=1)

21. 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: 26 (out of 85)
22. 6,896.856 6,896.856 ↓ 1.0 1,543,209 1

Dynamic Seq Scan on container_operation (dynamic scan id: 1) (cost=0.00..5,156.90 rows=1,487,347 width=27) (actual time=8.392..6,896.856 rows=1,543,209 loops=1)

  • Filter: ((operation_date >= '2019-05-21 11:37:08.421+00'::timestamp with time zone) AND (operation_date <= '2021-05-21 11:37:08.423+00'::timestamp with time zone) AND (type_id = 1))
  • Partitions scanned: Avg 26.0 (out of 85) x 20 workers. Max 26 parts (seg0).
23. 0.000 0.000 ↓ 0.0 0

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

24. 0.000 0.000 ↓ 0.0 0

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

25. 0.000 0.000 ↓ 0.0 0

Redistribute Motion 20:20 (slice5; segments: 20) (cost=0.00..2,004.17 rows=472,557 width=17) (never executed)

  • Hash Key: container_operation_1.barcode
26. 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
27. 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)
28. 0.000 0.000 ↓ 0.0 0

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

29. 0.000 0.000 ↓ 0.0 0

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

30. 0.000 0.000 ↓ 0.0 0

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

31. 0.000 0.000 ↓ 0.0 0

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

32. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (code = 'IM_CONTAINER_RECALCULATING'::text)
33. 0.000 0.000 ↓ 0.0 0

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

34. 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)
35. 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.id)
  • Heap Blocks: exact=88406616 lossy=69851296
36. 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.id)
Planning time : 181.288 ms
Execution time : 11,961.553 ms