explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FQwC : Sampling

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 4,009.737 ↓ 0.0 0 1

Result (cost=0.00..1,356.39 rows=1 width=9) (actual time=4,009.737..4,009.737 rows=0 loops=1)

  • (slice0) Executor memory: 143453K bytes. Work_mem: 73114K 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: 3676K bytes avg x 4 workers, 3676K bytes max (seg0).
  • (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.007 4,009.711 ↓ 0.0 0 1

Result (cost=0.00..1,356.39 rows=1 width=16) (actual time=4,009.711..4,009.711 rows=0 loops=1)

3. 26.755 4,009.704 ↓ 0.0 0 1

Hash Left Join (cost=0.00..1,356.39 rows=1 width=25) (actual time=4,009.704..4,009.704 rows=0 loops=1)

  • Hash Cond: (container.barcode = container_operation_1.barcode)
  • Extra Text: Hash chain length 2.3 avg, 12 max, using 114224 of 131072 buckets.
4. 22.364 1,547.615 ↓ 0.0 0 1

Hash Anti Join (cost=0.00..919.39 rows=1 width=17) (actual time=1,547.615..1,547.615 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.022 0.022 ↓ 0.0 0 1

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

6. 0.000 0.000 ↓ 0.0 0

Seq Scan on container (cost=0.00..482.39 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 ((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. 141.353 1,525.229 ↓ 342,025.0 342,025 1

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

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

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

9. 581.460 1,327.049 ↓ 342,025.0 342,025 1

Hash Join (cost=0.00..437.00 rows=1 width=8) (actual time=13.834..1,327.049 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. 431.420 745.532 ↓ 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.060..745.532 rows=3,709,606 loops=1)

11. 87.372 314.112 ↓ 931,139.0 931,139 1

Sequence (cost=0.00..431.00 rows=1 width=10) (actual time=2.654..314.112 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. 226.740 226.740 ↓ 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=2.614..226.740 rows=931,139 loops=1)

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

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

  • Buckets: 131072 Batches: 1 Memory Usage: 1kB
15. 0.019 0.055 ↑ 1.0 1 1

Assert (cost=0.00..6.00 rows=1 width=4) (actual time=0.051..0.055 rows=1 loops=1)

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

WindowAgg (cost=0.00..6.00 rows=1 width=12) (actual time=0.032..0.036 rows=1 loops=1)

17. 0.000 0.023 ↑ 1.0 1 1

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

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

  • Index Cond: (code = 'OPENING'::text)
19. 108.555 2,435.334 ↓ 267,112.0 267,112 1

Hash (cost=437.00..437.00 rows=1 width=16) (actual time=2,435.334..2,435.334 rows=267,112 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 15586kB
20. 42.881 2,326.779 ↓ 267,112.0 267,112 1

Result (cost=0.00..437.00 rows=1 width=16) (actual time=2,080.365..2,326.779 rows=267,112 loops=1)

21. 170.319 2,283.898 ↓ 267,112.0 267,112 1

GroupAggregate (cost=0.00..437.00 rows=1 width=16) (actual time=2,080.349..2,283.898 rows=267,112 loops=1)

  • Group Key: container_operation_1.barcode
22. 538.391 2,113.579 ↓ 314,434.0 314,434 1

Sort (cost=0.00..437.00 rows=1 width=8) (actual time=2,080.313..2,113.579 rows=314,434 loops=1)

  • Sort Key: container_operation_1.barcode
  • Sort Method: quicksort Memory: 73114kB
23. 616.435 1,575.188 ↓ 314,434.0 314,434 1

Hash Join (cost=0.00..437.00 rows=1 width=8) (actual time=3.293..1,575.188 rows=314,434 loops=1)

  • Hash Cond: ((container_operation_1.type_id)::integer = operation_type_1.id)
  • Extra Text: Hash chain length 1.0 avg, 1 max, using 1 of 131072 buckets.
24. 639.880 956.749 ↓ 3,709,606.0 3,709,606 1

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

25. 89.339 316.869 ↓ 931,139.0 931,139 1

Sequence (cost=0.00..431.00 rows=1 width=10) (actual time=3.159..316.869 rows=931,139 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. 227.530 227.530 ↓ 931,139.0 931,139 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=3.121..227.530 rows=931,139 loops=1)

  • Partitions scanned: Avg 85.0 (out of 85) x 4 workers. Max 85 parts (seg0).
28. 0.008 2.004 ↑ 1.0 1 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 1kB
29. 0.025 1.996 ↑ 1.0 1 1

Assert (cost=0.00..6.00 rows=1 width=4) (actual time=0.909..1.996 rows=1 loops=1)

  • Assert Cond: ((row_number() OVER (?)) = 1)
30. 0.041 1.971 ↑ 1.0 1 1

WindowAgg (cost=0.00..6.00 rows=1 width=12) (actual time=0.885..1.971 rows=1 loops=1)

31. 1.726 1.930 ↑ 1.0 1 1

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

32. 0.204 0.204 ↑ 1.0 1 1

Index Scan using operation_type_code_key on operation_type operation_type_1 (cost=0.00..6.00 rows=1 width=4) (actual time=0.203..0.204 rows=1 loops=1)

  • Index Cond: (code = 'CONTAINER_FORMING'::text)
Planning time : 328.901 ms
Execution time : 4,046.519 ms