explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2HM2 : DEF RATE

Settings
# exclusive inclusive rows x rows loops node
1. 1.500 8.982 ↑ 1.0 1 1

Gather Motion 20:1 (slice7; segments: 20) (cost=0.00..4,847.18 rows=1 width=9) (actual time=8.981..8.982 rows=1 loops=1)

  • (slice0) Executor memory: 602K bytes.
  • (slice1) Executor memory: 3217K bytes avg x 20 workers, 3217K bytes max (seg0).
  • (slice2) Executor memory: 62K bytes avg x 20 workers, 62K bytes max (seg0).
  • (slice3) Executor memory: 314K bytes (entry db).
  • (slice4) Executor memory: 65K bytes avg x 20 workers, 65K bytes max (seg0).
  • (slice5) Executor memory: 618K bytes avg x 20 workers, 618K bytes max (seg0).
  • (slice6) Executor memory: 154K bytes avg x 20 workers, 154K bytes max (seg0). Work_mem: 81K bytes max.
  • (slice7) Executor memory: 106K bytes avg x 20 workers, 106K bytes max (seg0). Work_mem: 33K bytes max.
  • (slice8) Memory used: 98304kBOptimizer: Pivotal Optimizer (GPORCA) version 3.88.0
2. 0.009 7.482 ↑ 1.0 1 1

Result (cost=0.00..4,847.18 rows=1 width=9) (actual time=7.482..7.482 rows=1 loops=1)

3. 0.015 7.473 ↑ 1.0 1 1

GroupAggregate (cost=0.00..4,847.18 rows=1 width=18) (actual time=7.473..7.473 rows=1 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.operations, container.ctid, container.gp_segment_id, ""outer"".ColRef_0089
4. 0.053 7.458 ↑ 1.0 1 1

Sort (cost=0.00..4,847.18 rows=1 width=53) (actual time=7.458..7.458 rows=1 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.operations, container.ctid, container.gp_segment_id, ""outer"".ColRef_0089
  • Sort Method: quicksort Memory: 660kB
5. 1.484 7.405 ↑ 1.0 1 1

Redistribute Motion 20:20 (slice6; segments: 20) (cost=0.00..4,847.18 rows=1 width=53) (actual time=7.296..7.405 rows=1 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.operations, container.ctid, container.gp_segment_id, ""outer"".ColRef_0089
6. 0.014 5.921 ↑ 1.0 1 1

GroupAggregate (cost=0.00..4,847.18 rows=1 width=53) (actual time=5.921..5.921 rows=1 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.operations, container.ctid, container.gp_segment_id, ""outer"".ColRef_0089
7. 0.538 5.907 ↑ 1.0 1 1

Sort (cost=0.00..4,847.18 rows=1 width=53) (actual time=5.907..5.907 rows=1 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.operations, container.ctid, container.gp_segment_id, ""outer"".ColRef_0089
  • Sort Method: quicksort Memory: 980kB
8. 0.000 5.369 ↑ 1.0 1 1

Redistribute Motion 20:20 (slice5; segments: 20) (cost=0.00..4,847.18 rows=1 width=53) (actual time=5.235..5.369 rows=1 loops=1)

9. 1.315 5.463 ↑ 1.0 1 1

Hash Left Join (cost=0.00..4,847.18 rows=1 width=53) (actual time=3.740..5.463 rows=1 loops=1)

  • Hash Cond: (container.barcode = container_operation.barcode)
  • Extra Text: (seg10) Hash chain length 0.0 avg, 0 max, using 0 of 65536 buckets. Skipped 1 empty batches.
10. 0.753 4.148 ↑ 1.0 1 1

Redistribute Motion 20:20 (slice1; segments: 20) (cost=0.00..431.07 rows=1 width=52) (actual time=2.575..4.148 rows=1 loops=1)

  • Hash Key: container.barcode
11. 2.374 3.395 ↑ 1.0 1 1

Bitmap Heap Scan on container (cost=0.00..431.07 rows=1 width=52) (actual time=3.302..3.395 rows=1 loops=1)

  • Recheck Cond: ((im_origin_impc_code = 'HKHKGH'::text) AND (im_category = 'C'::text) AND (im_destination_impc_code = 'RUEKAA'::text))
  • Filter: ((date_part('year'::text, im_formation_date) = 2020::double precision) AND (NOT ((operations -> 'opened'::text) IS NULL)) 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)))
12. 0.001 1.021 ↓ 0.0 1 1

BitmapAnd (cost=0.00..0.00 rows=0 width=0) (actual time=1.021..1.021 rows=1 loops=1)

13. 0.002 0.710 ↓ 0.0 1 1

BitmapAnd (cost=0.00..0.00 rows=0 width=0) (actual time=0.710..0.710 rows=1 loops=1)

14. 0.376 0.376 ↓ 0.0 1 1

Bitmap Index Scan on container_im_origin_impc_code_idx (cost=0.00..0.00 rows=0 width=0) (actual time=0.376..0.376 rows=1 loops=1)

  • Index Cond: (im_origin_impc_code = 'HKHKGH'::text)
15. 0.332 0.332 ↓ 0.0 1 1

Bitmap Index Scan on container_im_category_idx (cost=0.00..0.00 rows=0 width=0) (actual time=0.332..0.332 rows=1 loops=1)

  • Index Cond: (im_category = 'C'::text)
16. 0.310 0.310 ↓ 0.0 1 1

Bitmap Index Scan on container_im_destination_impc_code_idx (cost=0.00..0.00 rows=0 width=0) (actual time=0.310..0.310 rows=1 loops=1)

  • Index Cond: (im_destination_impc_code = 'RUEKAA'::text)
17. 0.000 0.000 ↓ 0.0 0

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

18. 0.000 0.000 ↓ 0.0 0

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

19. 0.000 0.000 ↓ 0.0 0

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

  • Hash Key: container_operation.barcode
20. 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
21. 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)
22. 0.000 0.000 ↓ 0.0 0

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

23. 0.000 0.000 ↓ 0.0 0

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

24. 0.000 0.000 ↓ 0.0 0

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

25. 0.000 0.000 ↓ 0.0 0

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

26. 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)
27. 0.000 0.000 ↓ 0.0 0

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

28. 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)
29. 0.000 0.000 ↓ 0.0 0

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

  • Recheck Cond: (type_id = operation_type.id)
  • Heap Blocks: exact=61540632 lossy=48318792
30. 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 : 125.499 ms
Execution time : 41.766 ms