explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iHoJ : Sampling

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 1,332.650 ↓ 0.0 0 1

Result (cost=0.00..1,361.97 rows=1 width=9) (actual time=1,332.650..1,332.650 rows=0 loops=1)

  • (slice0) Executor memory: 37604K bytes. Work_mem: 16033K bytes max.
  • (slice1) Executor memory: 123K bytes avg x 4 workers, 123K bytes max (seg0).
  • (slice2) Executor memory: 3676K bytes avg x 4 workers, 3676K bytes max (seg0).
  • (slice3) Executor memory: 60K bytes avg x 4 workers, 60K bytes max (seg0).
  • (slice4) Executor memory: 460K bytes avg x 4 workers, 460K bytes max (seg3).
  • (slice5) Executor memory: 60K bytes avg x 4 workers, 60K bytes max (seg0).Memory used: 126976kBOptimizer: Pivotal Optimizer (GPORCA) version 3.88.0
2. 0.004 1,332.639 ↓ 0.0 0 1

Result (cost=0.00..1,361.97 rows=1 width=16) (actual time=1,332.639..1,332.639 rows=0 loops=1)

3. 0.565 1,332.635 ↓ 0.0 0 1

Hash Left Join (cost=0.00..1,361.97 rows=1 width=25) (actual time=1,332.635..1,332.635 rows=0 loops=1)

  • Hash Cond: (container.barcode = container_operation_1.barcode)
  • Extra Text: Hash chain length 0.0 avg, 0 max, using 0 of 131072 buckets.
4. 20.408 1,328.581 ↓ 0.0 0 1

Hash Anti Join (cost=0.00..924.97 rows=1 width=17) (actual time=1,328.581..1,328.581 rows=0 loops=1)

  • Hash Cond: (container.barcode = container_operation.barcode)
  • Extra Text: Hash chain length 2.8 avg, 12 max, using 121026 of 131072 buckets.
5. 0.020 0.020 ↓ 0.0 0 1

Gather Motion 4:1 (slice1; segments: 4) (cost=0.00..487.97 rows=1 width=17) (actual time=0.020..0.020 rows=0 loops=1)

6. 0.000 0.000 ↓ 0.0 0

Seq Scan on container (cost=0.00..487.97 rows=1 width=17) (never executed)

  • Filter: ((im_origin_impc_code = 'A'::text) AND (im_destination_impc_code = 'ABCD'::text) AND (im_category = 'ABCD'::text) AND (date_part('year'::text, im_formation_date) = 2020::double precision) AND (im_formation_date >= '2020-01-01 00:00:00+00'::timestamp with time zone) AND (im_formation_date <= '2020-06-01 00:00:00+00'::timestamp with time zone) AND ((im_format ~~* 'X'::text) OR (im_format ~~* 'P'::text) OR (im_format ~~* 'S'::text) OR (im_format ~~* 'G'::text) OR (im_format ~~* 'E'::text)))
7. 89.974 1,308.153 ↓ 342,025.0 342,025 1

Hash (cost=437.00..437.00 rows=1 width=8) (actual time=1,308.153..1,308.153 rows=342,025 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 16033kB
8. 49.421 1,218.179 ↓ 342,025.0 342,025 1

Result (cost=0.00..437.00 rows=1 width=8) (actual time=12.300..1,218.179 rows=342,025 loops=1)

9. 505.401 1,168.758 ↓ 342,025.0 342,025 1

Hash Join (cost=0.00..437.00 rows=1 width=8) (actual time=12.295..1,168.758 rows=342,025 loops=1)

  • Hash Cond: ((container_operation.type_id)::integer = operation_type.id)
  • Extra Text: Hash chain length 1.0 avg, 1 max, using 1 of 131072 buckets.
10. 352.752 663.319 ↓ 3,709,606.0 3,709,606 1

Gather Motion 4:1 (slice2; segments: 4) (cost=0.00..431.00 rows=1 width=10) (actual time=0.039..663.319 rows=3,709,606 loops=1)

11. 89.238 310.567 ↓ 931,139.0 931,139 1

Sequence (cost=0.00..431.00 rows=1 width=10) (actual time=1.881..310.567 rows=931,139 loops=1)

12. 0.000 0.000 ↓ 0.0 0

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

  • Partitions selected: 85 (out of 85)
13. 221.329 221.329 ↓ 931,139.0 931,139 1

Dynamic Seq Scan on container_operation (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=10) (actual time=1.837..221.329 rows=931,139 loops=1)

  • Partitions scanned: Avg 85.0 (out of 85) x 4 workers. Max 85 parts (seg0).
14. 0.002 0.038 ↑ 1.0 1 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 1kB
15. 0.014 0.036 ↑ 1.0 1 1

Assert (cost=0.00..6.00 rows=1 width=4) (actual time=0.032..0.036 rows=1 loops=1)

  • Assert Cond: ((row_number() OVER (?)) = 1)
16. 0.013 0.022 ↑ 1.0 1 1

WindowAgg (cost=0.00..6.00 rows=1 width=12) (actual time=0.019..0.022 rows=1 loops=1)

17. 0.000 0.009 ↑ 1.0 1 1

Gather Motion 4:1 (slice3; segments: 4) (cost=0.00..6.00 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=1)

18. 0.042 0.042 ↑ 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.041..0.042 rows=1 loops=1)

  • Index Cond: (code = 'OPENING'::text)
19. 0.001 3.489 ↓ 0.0 0 1

Hash (cost=437.00..437.00 rows=1 width=16) (actual time=3.489..3.489 rows=0 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 0kB
20. 0.006 3.488 ↓ 0.0 0 1

Result (cost=0.00..437.00 rows=1 width=16) (actual time=3.488..3.488 rows=0 loops=1)

21. 0.003 3.482 ↓ 0.0 0 1

GroupAggregate (cost=0.00..437.00 rows=1 width=16) (actual time=3.482..3.482 rows=0 loops=1)

  • Group Key: container_operation_1.barcode
22. 0.026 3.479 ↓ 0.0 0 1

Sort (cost=0.00..437.00 rows=1 width=8) (actual time=3.479..3.479 rows=0 loops=1)

  • Sort Key: container_operation_1.barcode
  • Sort Method: quicksort Memory: 33kB
23. 0.417 3.453 ↓ 0.0 0 1

Hash Join (cost=0.00..437.00 rows=1 width=8) (actual time=3.453..3.453 rows=0 loops=1)

  • Hash Cond: ((container_operation_1.type_id)::integer = operation_type_1.id)
  • Extra Text: Hash chain length 0.0 avg, 0 max, using 0 of 131072 buckets.
24. 0.000 0.000 ↓ 0.0 0

Gather Motion 4:1 (slice4; segments: 4) (cost=0.00..431.00 rows=1 width=10) (never executed)

25. 0.141 1.833 ↓ 1,106.0 1,106 1

Sequence (cost=0.00..431.00 rows=1 width=10) (actual time=1.478..1.833 rows=1,106 loops=1)

26. 0.000 0.000 ↓ 0.0 0

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

  • Partitions selected: 85 (out of 85)
27. 1.692 1.692 ↓ 1,106.0 1,106 1

Dynamic Seq Scan on container_operation container_operation_1 (dynamic scan id: 2) (cost=0.00..431.00 rows=1 width=10) (actual time=1.453..1.692 rows=1,106 loops=1)

  • Partitions scanned: Avg 2.0 (out of 85) x 4 workers. Max 2 parts (seg0).
28. 0.002 3.036 ↓ 0.0 0 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 0kB
29. 0.011 3.034 ↓ 0.0 0 1

Assert (cost=0.00..6.00 rows=1 width=4) (actual time=3.034..3.034 rows=0 loops=1)

  • Assert Cond: ((row_number() OVER (?)) = 1)
30. 0.009 3.023 ↓ 0.0 0 1

WindowAgg (cost=0.00..6.00 rows=1 width=12) (actual time=3.023..3.023 rows=0 loops=1)

31. 3.014 3.014 ↓ 0.0 0 1

Gather Motion 4:1 (slice5; segments: 4) (cost=0.00..6.00 rows=1 width=4) (actual time=3.014..3.014 rows=0 loops=1)

32. 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 : 181.208 ms
Execution time : 1,376.799 ms