explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MAyb : Sampling

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 1,588.782 ↓ 0.0 0 1

Result (cost=0.00..1,361.97 rows=1 width=9) (actual time=1,588.782..1,588.782 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,588.772 ↓ 0.0 0 1

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

3. 0.555 1,588.768 ↓ 0.0 0 1

Hash Left Join (cost=0.00..1,361.97 rows=1 width=25) (actual time=1,588.768..1,588.768 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. 21.814 1,586.259 ↓ 0.0 0 1

Hash Anti Join (cost=0.00..924.97 rows=1 width=17) (actual time=1,586.259..1,586.259 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.019 0.019 ↓ 0.0 0 1

Gather Motion 4:1 (slice1; segments: 4) (cost=0.00..487.97 rows=1 width=17) (actual time=0.019..0.019 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 (date_part('year'::text, im_formation_date) = 2020::double precision) AND (im_category = 'ABCD'::text) 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. 129.131 1,564.426 ↓ 342,025.0 342,025 1

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

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

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

9. 597.730 1,382.368 ↓ 342,025.0 342,025 1

Hash Join (cost=0.00..437.00 rows=1 width=8) (actual time=17.203..1,382.368 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. 517.246 784.601 ↓ 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.042..784.601 rows=3,709,606 loops=1)

11. 79.854 267.355 ↓ 931,139.0 931,139 1

Sequence (cost=0.00..431.00 rows=1 width=10) (actual time=1.085..267.355 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. 187.501 187.501 ↓ 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.063..187.501 rows=931,139 loops=1)

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

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

  • Buckets: 131072 Batches: 1 Memory Usage: 1kB
15. 0.013 0.032 ↑ 1.0 1 1

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

  • Assert Cond: ((row_number() OVER (?)) = 1)
16. 0.009 0.019 ↑ 1.0 1 1

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

17. 0.000 0.010 ↑ 1.0 1 1

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

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

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

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

  • Buckets: 131072 Batches: 1 Memory Usage: 0kB
20. 0.027 1.952 ↓ 0.0 0 1

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

21. 0.008 1.925 ↓ 0.0 0 1

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

  • Group Key: container_operation_1.barcode
22. 0.021 1.917 ↓ 0.0 0 1

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

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

Hash Join (cost=0.00..437.00 rows=1 width=8) (actual time=1.896..1.896 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.176 1.479 ↓ 922.0 922 1

Sequence (cost=0.00..431.00 rows=1 width=10) (actual time=1.203..1.479 rows=922 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.303 1.303 ↓ 922.0 922 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.146..1.303 rows=922 loops=1)

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

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

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

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

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

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

31. 1.280 1.280 ↓ 0.0 0 1

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