explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kt7O5 : DEF RATE

Settings
# exclusive inclusive rows x rows loops node
1. 1.202 3,537.889 ↓ 587.0 587 1

Gather Motion 20:1 (slice10; segments: 20) (cost=0.00..6,567.50 rows=1 width=9) (actual time=3,537.306..3,537.889 rows=587 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: 35058K bytes avg x 20 workers, 35058K bytes max (seg0). Work_mem: 15818K 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.
  • (slice11)
  • (slice12) Memory used: 98304kBMemory wanted: 320832kBOptimizer: Pivotal Optimizer (GPORCA) version 3.88.0
2. 0.015 3,536.687 ↓ 41.0 41 1

Result (cost=0.00..6,567.50 rows=1 width=9) (actual time=3,536.655..3,536.687 rows=41 loops=1)

3. 0.040 3,536.672 ↓ 41.0 41 1

GroupAggregate (cost=0.00..6,567.50 rows=1 width=18) (actual time=3,536.647..3,536.672 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.284 3,536.632 ↓ 41.0 41 1

Sort (cost=0.00..6,567.50 rows=1 width=53) (actual time=3,536.631..3,536.632 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. 1.339 3,536.348 ↓ 41.0 41 1

Redistribute Motion 20:20 (slice9; segments: 20) (cost=0.00..6,567.50 rows=1 width=53) (actual time=3,536.150..3,536.348 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.044 3,535.009 ↓ 41.0 41 1

GroupAggregate (cost=0.00..6,567.50 rows=1 width=53) (actual time=3,534.983..3,535.009 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
7. 0.642 3,534.965 ↓ 41.0 41 1

Sort (cost=0.00..6,567.50 rows=1 width=53) (actual time=3,534.962..3,534.965 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
8. 42.571 3,534.323 ↓ 41.0 41 1

Redistribute Motion 20:20 (slice8; segments: 20) (cost=0.00..6,567.50 rows=1 width=53) (actual time=3,483.172..3,534.323 rows=41 loops=1)

9. 2.555 3,491.752 ↓ 44.0 44 1

Hash Left Join (cost=0.00..6,567.50 rows=1 width=53) (actual time=3,098.152..3,491.752 rows=44 loops=1)

  • Hash Cond: (container.barcode = container_operation_1.barcode)
  • Extra Text: (seg6) Initial batch 0:(seg6) Wrote 2K bytes to outer workfile.(seg6) Initial batch 1:(seg6) Read 2K bytes from outer workfile.(seg6) Hash chain length 0.0 avg, 0 max, using 0 of 262144 buckets.Initial batch 0:
10. 393.520 3,489.197 ↓ 44.0 44 1

Hash Anti Join (cost=0.00..4,314.48 rows=1 width=52) (actual time=3,096.779..3,489.197 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) Initial batch 1:(seg6) Read 15835K bytes from inner workfile.(seg6) Wrote 5931K bytes to inner workfile.(seg6) Read 1K bytes from outer workfile.(seg6) Overflow batches 2..3:(seg6) Read 19807K bytes from inner workfile: 9904K avg x 2 nonempty batches, 9922K max.(seg6) Read 2K bytes from outer workfile: 1K avg x 2 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) Initial batch 1:(seg7) Read 15847K bytes from inner workfile.(seg7) Wrote 5926K bytes to inner workfile.(seg7) Read 1K bytes from outer workfile.(seg7) Overflow batches 2..3:(seg7) Read 19825K bytes from inner workfile: 9913K avg x 2 nonempty batches, 9926K max.(seg7) Read 1K bytes from outer workfile: 1K avg x 2 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.205 ↓ 44.0 44 1

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

  • Hash Key: container.barcode
12. 0.409 0.409 ↓ 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.178..0.409 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. 704.105 3,095.472 ↓ 2.4 1,128,648 1

Hash (cost=2,012.21..2,012.21 rows=472,557 width=17) (actual time=3,095.472..3,095.472 rows=1,128,648 loops=1)

14. 156.279 2,391.367 ↓ 2.4 1,128,648 1

Result (cost=0.00..2,012.21 rows=472,557 width=17) (actual time=41.874..2,391.367 rows=1,128,648 loops=1)

15. 0.000 2,235.088 ↓ 2.4 1,128,648 1

Redistribute Motion 20:20 (slice4; segments: 20) (cost=0.00..2,004.17 rows=472,557 width=17) (actual time=41.860..2,235.088 rows=1,128,648 loops=1)

  • Hash Key: container_operation.barcode
16. 131.816 2,535.926 ↓ 2.4 1,128,687 1

Nested Loop (cost=0.00..1,979.03 rows=472,557 width=17) (actual time=7.629..2,535.926 rows=1,128,687 loops=1)

  • Join Filter: true
17. 0.087 0.178 ↑ 1.0 1 1

Assert (cost=0.00..6.00 rows=1 width=4) (actual time=0.168..0.178 rows=1 loops=1)

  • Assert Cond: ((row_number() OVER (?)) = 1)
18. 0.000 0.091 ↑ 20.0 1 1

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

19. 0.005 1.420 ↑ 1.0 1 1

Result (cost=0.00..6.00 rows=1 width=12) (actual time=0.727..1.420 rows=1 loops=1)

20. 0.039 1.415 ↑ 1.0 1 1

WindowAgg (cost=0.00..6.00 rows=1 width=12) (actual time=0.723..1.415 rows=1 loops=1)

21. 1.316 1.376 ↑ 1.0 1 1

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

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

  • Index Cond: (code = 'OPENING'::text)
23. 90.402 2,403.932 ↓ 2.4 1,128,687 1

Sequence (cost=0.00..1,921.93 rows=472,557 width=17) (actual time=7.131..2,403.932 rows=1,128,687 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,299.590 2,313.530 ↓ 2.4 1,128,687 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=7.097..2,313.530 rows=1,128,687 loops=1)

  • Recheck Cond: (type_id = operation_type.id)
  • Heap Blocks: exact=49294680 lossy=206158430256
26. 13.940 13.940 ↓ 0.0 1 85

Dynamic Bitmap Index Scan on title_bmp_idx (cost=0.00..0.00 rows=0 width=0) (actual time=0.005..0.164 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=49263192
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 : 205.848 ms
Execution time : 3,599.726 ms