explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cDpx : Sampling

Settings
# exclusive inclusive rows x rows loops node
1. 0.043 1,555.968 ↓ 4.0 4 1

Result (cost=0.00..1,361.97 rows=1 width=9) (actual time=1,520.234..1,555.968 rows=4 loops=1)

  • (slice0) Executor memory: 37612K 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.010 1,555.925 ↓ 4.0 4 1

Result (cost=0.00..1,361.97 rows=1 width=16) (actual time=1,520.193..1,555.925 rows=4 loops=1)

3. 0.532 1,555.915 ↓ 4.0 4 1

Hash Left Join (cost=0.00..1,361.97 rows=1 width=25) (actual time=1,520.186..1,555.915 rows=4 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. 35.595 1,554.291 ↓ 4.0 4 1

Hash Anti Join (cost=0.00..924.97 rows=1 width=17) (actual time=1,518.814..1,554.291 rows=4 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.000 0.042 ↓ 4.0 4 1

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

6. 143.692 143.692 ↑ 1.0 1 1

Seq Scan on container (cost=0.00..487.97 rows=1 width=17) (actual time=143.689..143.692 rows=1 loops=1)

  • Filter: ((im_origin_impc_code = 'SDWHFA'::text) AND (im_destination_impc_code = 'UACOPE'::text) AND (im_category = 'C'::text) AND (date_part('year'::text, im_formation_date) = 2019::double precision) AND (im_formation_date >= '2019-01-01 00:00:00+00'::timestamp with time zone) AND (im_formation_date <= '2019-12-31 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. 138.650 1,518.654 ↓ 342,025.0 342,025 1

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

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

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

9. 587.812 1,331.545 ↓ 342,025.0 342,025 1

Hash Join (cost=0.00..437.00 rows=1 width=8) (actual time=16.226..1,331.545 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. 442.429 743.675 ↓ 3,709,621.0 3,709,621 1

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

11. 86.299 301.246 ↓ 931,143.0 931,143 1

Sequence (cost=0.00..431.00 rows=1 width=10) (actual time=0.997..301.246 rows=931,143 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. 214.947 214.947 ↓ 931,143.0 931,143 1

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

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

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

  • Buckets: 131072 Batches: 1 Memory Usage: 1kB
15. 0.023 0.053 ↑ 1.0 1 1

Assert (cost=0.00..6.00 rows=1 width=4) (actual time=0.049..0.053 rows=1 loops=1)

  • Assert Cond: ((row_number() OVER (?)) = 1)
16. 0.017 0.030 ↑ 1.0 1 1

WindowAgg (cost=0.00..6.00 rows=1 width=12) (actual time=0.026..0.030 rows=1 loops=1)

17. 0.000 0.013 ↑ 1.0 1 1

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

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

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

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

  • Buckets: 131072 Batches: 1 Memory Usage: 0kB
20. 0.005 1.090 ↓ 0.0 0 1

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

21. 0.005 1.085 ↓ 0.0 0 1

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

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

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

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

Hash Join (cost=0.00..437.00 rows=1 width=8) (actual time=1.054..1.054 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.091 1.880 ↓ 370.0 370 1

Sequence (cost=0.00..431.00 rows=1 width=10) (actual time=1.654..1.880 rows=370 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.789 1.789 ↓ 370.0 370 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.618..1.789 rows=370 loops=1)

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

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

  • Buckets: 131072 Batches: 1 Memory Usage: 0kB
29. 0.010 0.442 ↓ 0.0 0 1

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

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

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

31. 0.424 0.424 ↓ 0.0 0 1

Gather Motion 4:1 (slice5; segments: 4) (cost=0.00..6.00 rows=1 width=4) (actual time=0.424..0.424 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 : 185.143 ms
Execution time : 1,558.241 ms