explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qffJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.081 1,217.054 ↓ 33.0 33 1

Sort (cost=14,487.10..14,487.11 rows=1 width=296) (actual time=1,217.053..1,217.054 rows=33 loops=1)

  • Sort Key: (to_char(a_1.probendatum, 'YYYY'::text)), (substr((a_1.barcode)::text, 15, 4))
  • Sort Method: quicksort Memory: 29kB
2. 0.143 1,216.973 ↓ 33.0 33 1

Nested Loop Anti Join (cost=12,800.99..14,487.09 rows=1 width=296) (actual time=1,211.933..1,216.973 rows=33 loops=1)

3. 2.971 1,210.197 ↓ 33.0 33 1

Nested Loop (cost=12,800.70..14,476.10 rows=1 width=87) (actual time=1,206.057..1,210.197 rows=33 loops=1)

  • Join Filter: ((x_1.g4_wert > ((avg(x_2.g4_wert) + (stddev(x_2.g4_wert) * (c.faktor)::double precision)))) AND (a.zzid = x_1.zzid) AND ((to_char(x_2.g4_dt, 'iyyy-iw'::text)) = to_char(x_1.g4_dt, 'IYYY-IW'::text)))
  • Rows Removed by Join Filter: 5111
4. 2.547 1,186.650 ↓ 1,286.0 5,144 1

Nested Loop (cost=12,800.41..14,473.88 rows=4 width=127) (actual time=1,127.988..1,186.650 rows=5,144 loops=1)

  • Join Filter: (a.herkzdbcd = b.herkzdbcd)
  • Rows Removed by Join Filter: 15432
5. 0.037 284.583 ↑ 32.1 32 1

Finalize GroupAggregate (cost=10,948.50..11,066.46 rows=1,028 width=67) (actual time=284.175..284.583 rows=32 loops=1)

  • Group Key: (to_char(x_2.g4_dt, 'iyyy-iw'::text)), b.herkzdbcd, c.faktor
6. 0.000 284.546 ↑ 9.5 64 1

Gather Merge (cost=10,948.50..11,033.20 rows=605 width=107) (actual time=284.128..284.546 rows=64 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
7. 0.370 544.550 ↑ 18.9 32 2

Partial GroupAggregate (cost=9,948.49..9,965.13 rows=605 width=107) (actual time=272.056..272.275 rows=32 loops=2)

  • Group Key: (to_char(x_2.g4_dt, 'iyyy-iw'::text)), b.herkzdbcd, c.faktor
8. 14.508 544.180 ↓ 1.3 771 2

Sort (cost=9,948.49..9,950.00 rows=605 width=51) (actual time=272.033..272.090 rows=771 loops=2)

  • Sort Key: (to_char(x_2.g4_dt, 'iyyy-iw'::text)), b.herkzdbcd, c.faktor
  • Sort Method: quicksort Memory: 93kB
  • Worker 0: Sort Method: quicksort Memory: 76kB
9. 33.526 529.672 ↓ 1.3 771 2

Hash Left Join (cost=1.53..9,920.54 rows=605 width=51) (actual time=40.904..264.836 rows=771 loops=2)

  • Hash Cond: (b.herkzdbcd = c.herkzdbcd)
10. 2.166 496.094 ↓ 1.3 771 2

Nested Loop (cost=0.43..9,916.23 rows=605 width=20) (actual time=25.256..248.047 rows=771 loops=2)

11. 77.876 77.876 ↑ 1.0 826 2

Parallel Seq Scan on mvtier_lpa x_2 (cost=0.00..3,016.35 rows=828 width=24) (actual time=2.647..38.938 rows=826 loops=2)

  • Filter: ((g4_dt IS NOT NULL) AND (g4_dt >= (CURRENT_DATE - 160)))
  • Rows Removed by Filter: 36203
12. 416.052 416.052 ↑ 1.0 1 1,651

Index Scan using uq_mvtier_id on mvtier b (cost=0.43..8.33 rows=1 width=8) (actual time=0.252..0.252 rows=1 loops=1,651)

  • Index Cond: (id = x_2.id)
  • Filter: (herkzdbcd IS NOT NULL)
13. 0.012 0.052 ↑ 1.0 4 2

Hash (cost=1.04..1.04 rows=4 width=11) (actual time=0.026..0.026 rows=4 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.040 0.040 ↑ 1.0 4 2

Seq Scan on tprb_lpa_faktor c (cost=0.00..1.04 rows=4 width=11) (actual time=0.019..0.020 rows=4 loops=2)

15. 1.193 899.520 ↓ 643.0 643 32

Materialize (cost=1,851.90..3,381.72 rows=1 width=91) (actual time=26.257..28.110 rows=643 loops=32)

16. 5.473 898.327 ↓ 643.0 643 1

Nested Loop (cost=1,851.90..3,381.71 rows=1 width=91) (actual time=840.225..898.327 rows=643 loops=1)

17. 25.511 63.886 ↓ 14,803.0 14,803 1

Hash Join (cost=1,851.47..3,375.44 rows=1 width=46) (actual time=34.050..63.886 rows=14,803 loops=1)

  • Hash Cond: ((a_1.zzid = a_2.zzid) AND (a_1.barcode = (min(a_2.barcode))))
18. 4.861 4.861 ↑ 1.0 43,670 1

Seq Scan on mvprb a_1 (cost=0.00..1,294.70 rows=43,670 width=42) (actual time=0.422..4.861 rows=43,670 loops=1)

19. 3.038 33.514 ↓ 1.3 14,803 1

Hash (cost=1,685.60..1,685.60 rows=11,058 width=36) (actual time=33.514..33.514 rows=14,803 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 924kB
20. 9.439 30.476 ↓ 1.3 14,803 1

HashAggregate (cost=1,464.44..1,575.02 rows=11,058 width=36) (actual time=27.051..30.476 rows=14,803 loops=1)

  • Group Key: a_2.zzid
21. 21.037 21.037 ↓ 1.2 14,875 1

Seq Scan on mvprb a_2 (cost=0.00..1,403.88 rows=12,113 width=23) (actual time=0.011..21.037 rows=14,875 loops=1)

  • Filter: ((versanddatum IS NULL) AND (probenartcd = ANY ('{2,3}'::integer[])))
  • Rows Removed by Filter: 28795
22. 828.968 828.968 ↓ 0.0 0 14,803

Index Scan using pk_mvtier_zzid on mvtier a (cost=0.43..6.24 rows=1 width=45) (actual time=0.056..0.056 rows=0 loops=14,803)

  • Index Cond: (zzid = a_1.zzid)
  • Filter: ((lpaid IS NOT NULL) AND (abgdt IS NULL) AND (herkzdbcd_vater = ANY ('{1,3}'::integer[])) AND (gebdt <= CURRENT_DATE) AND (gebdt >= (CURRENT_DATE - 160)))
  • Rows Removed by Filter: 1
23. 20.576 20.576 ↑ 1.0 1 5,144

Index Scan using uq_mvtier_lpa_zzid on mvtier_lpa x_1 (cost=0.29..0.54 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=5,144)

  • Index Cond: (zzid = a_1.zzid)
24. 6.633 6.633 ↓ 0.0 0 33

Index Scan using ix_mvprb_zzid on mvprb x (cost=0.29..5.63 rows=1 width=4) (actual time=0.201..0.201 rows=0 loops=33)

  • Index Cond: (zzid = a.zzid)
  • Filter: ((storno IS NULL) AND (labor_adrid = 7452))
  • Rows Removed by Filter: 1