explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pUT3

Settings
# exclusive inclusive rows x rows loops node
1. 783.636 2,329.833 ↓ 108.4 231,474 1

HashAggregate (cost=2,081.49..2,102.85 rows=2,136 width=34) (actual time=2,155.400..2,329.833 rows=231,474 loops=1)

  • Group Key: pred.tpe_prev_year_count, pred.tjaga_type_id, pred.dor_prip, pred.park_type_id, iby1.tpe_prev_year_count, iby2.tpe_prev_year_count
2.          

CTE predrasch

3. 2.680 371.629 ↑ 1.2 1,742 1

Append (cost=762.60..1,673.38 rows=2,136 width=82) (actual time=183.021..371.629 rows=1,742 loops=1)

4. 17.200 213.418 ↑ 1.0 1,031 1

GroupAggregate (cost=762.60..860.05 rows=1,068 width=82) (actual time=183.019..213.418 rows=1,031 loops=1)

  • Group Key: (((date_part('year'::text, erp.p_date) + ((erp.life_loco)::integer)::double precision) + ((erp.life_loco_pss)::integer)::double precision)), (CASE WHEN ((nlsv.kod_tjaga = 1) AND (nls.primary_vd = 11)) THEN 4 WHEN ((nlsv.kod_tjaga = 6) AND (nls.primary_vd = 11) AND (nls.kod_ser <> 275) AND (nls.kod_ser <> 273)) THEN 5 WHEN ((nls.kod_ser = 275) OR (nls.kod_ser = 273)) THEN 6 WHEN ((nlsv.kod_tjaga = 9) OR ((nlsv.kod_tjaga = 2) AND (nls.primary_vd = 11))) THEN 9 WHEN ((nlsv.kod_tjaga = 1) AND (nls.primary_vd = 21)) THEN 2 WHEN ((nlsv.kod_tjaga = 6) AND (nls.primary_vd = 21)) THEN 3 WHEN ((nlsv.kod_tjaga = 9) OR ((nlsv.kod_tjaga = 2) AND (nls.primary_vd = 21))) THEN 8 WHEN ((nlsv.kod_tjaga = 9) OR ((nlsv.kod_tjaga = 2) AND (nls.primary_vd = 50))) THEN 10 ELSE NULL::integer END), erp.dor_prip
  • Group Key: (((date_part('year'::text, erp.p_date) + ((erp.life_loco)::integer)::double precision) + ((erp.life_loco_pss)::integer)::double precision)), (CASE WHEN ((nlsv.kod_tjaga = 1) AND (nls.primary_vd = 11)) THEN 4 WHEN ((nlsv.kod_tjaga = 6) AND (nls.primary_vd = 11) AND (nls.kod_ser <> 275) AND (nls.kod_ser <> 273)) THEN 5 WHEN ((nls.kod_ser = 275) OR (nls.kod_ser = 273)) THEN 6 WHEN ((nlsv.kod_tjaga = 9) OR ((nlsv.kod_tjaga = 2) AND (nls.primary_vd = 11))) THEN 9 WHEN ((nlsv.kod_tjaga = 1) AND (nls.primary_vd = 21)) THEN 2 WHEN ((nlsv.kod_tjaga = 6) AND (nls.primary_vd = 21)) THEN 3 WHEN ((nlsv.kod_tjaga = 9) OR ((nlsv.kod_tjaga = 2) AND (nls.primary_vd = 21))) THEN 8 WHEN ((nlsv.kod_tjaga = 9) OR ((nlsv.kod_tjaga = 2) AND (nls.primary_vd = 50))) THEN 10 ELSE NULL::integer END)
5. 50.566 196.218 ↓ 26.8 14,289 1

Sort (cost=762.60..763.93 rows=534 width=14) (actual time=182.997..196.218 rows=14,289 loops=1)

  • Sort Key: (((date_part('year'::text, erp.p_date) + ((erp.life_loco)::integer)::double precision) + ((erp.life_loco_pss)::integer)::double precision)), (CASE WHEN ((nlsv.kod_tjaga = 1) AND (nls.primary_vd = 11)) THEN 4 WHEN ((nlsv.kod_tjaga = 6) AND (nls.primary_vd = 11) AND (nls.kod_ser <> 275) AND (nls.kod_ser <> 273)) THEN 5 WHEN ((nls.kod_ser = 275) OR (nls.kod_ser = 273)) THEN 6 WHEN ((nlsv.kod_tjaga = 9) OR ((nlsv.kod_tjaga = 2) AND (nls.primary_vd = 11))) THEN 9 WHEN ((nlsv.kod_tjaga = 1) AND (nls.primary_vd = 21)) THEN 2 WHEN ((nlsv.kod_tjaga = 6) AND (nls.primary_vd = 21)) THEN 3 WHEN ((nlsv.kod_tjaga = 9) OR ((nlsv.kod_tjaga = 2) AND (nls.primary_vd = 21))) THEN 8 WHEN ((nlsv.kod_tjaga = 9) OR ((nlsv.kod_tjaga = 2) AND (nls.primary_vd = 50))) THEN 10 ELSE NULL::integer END), erp.dor_prip
  • Sort Method: quicksort Memory: 1,054kB
6. 58.770 145.652 ↓ 26.8 14,289 1

Nested Loop (cost=4.79..738.40 rows=534 width=14) (actual time=0.478..145.652 rows=14,289 loops=1)

7. 0.744 2.127 ↓ 17.6 335 1

Hash Join (cost=4.38..71.80 rows=19 width=10) (actual time=0.091..2.127 rows=335 loops=1)

  • Hash Cond: (nls.primary_vd = vdv.kod_vdvig)
8. 0.719 1.344 ↓ 17.6 335 1

Hash Join (cost=2.13..69.29 rows=19 width=10) (actual time=0.043..1.344 rows=335 loops=1)

  • Hash Cond: (nls.kod_tjaga = nlsv.kod_tjaga)
9. 0.603 0.603 ↑ 1.0 336 1

Seq Scan on nsi_loc_ser nls (cost=0.00..65.71 rows=336 width=10) (actual time=0.010..0.603 rows=336 loops=1)

  • Filter: (primary_vd = ANY ('{11,21,50}'::integer[]))
  • Rows Removed by Filter: 225
10. 0.007 0.022 ↑ 1.0 4 1

Hash (cost=2.08..2.08 rows=4 width=2) (actual time=0.022..0.022 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.015 0.015 ↑ 1.0 4 1

Seq Scan on nsi_loc_vtjaga nlsv (cost=0.00..2.08 rows=4 width=2) (actual time=0.008..0.015 rows=4 loops=1)

  • Filter: (kod_tjaga = ANY ('{1,6,9,2}'::integer[]))
  • Rows Removed by Filter: 7
12. 0.018 0.039 ↑ 1.0 11 1

Hash (cost=2.11..2.11 rows=11 width=4) (actual time=0.039..0.039 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.021 0.021 ↑ 1.0 11 1

Seq Scan on nsi_loc_vdvig vdv (cost=0.00..2.11 rows=11 width=4) (actual time=0.008..0.021 rows=11 loops=1)

14. 84.755 84.755 ↑ 2.0 43 335

Index Scan using "EsPul_rzd_park_pkey" on espul_rzd_park erp (cost=0.41..32.13 rows=85 width=19) (actual time=0.036..0.253 rows=43 loops=335)

  • Index Cond: (ser_loc = nls.kod_ser)
  • Filter: (((date_part('year'::text, p_date) + ((life_loco)::integer)::double precision) + ((life_loco_pss)::integer)::double precision) >= '2017'::double precision)
  • Rows Removed by Filter: 52
15. 14.941 155.531 ↑ 1.5 711 1

GroupAggregate (cost=739.90..791.97 rows=1,068 width=82) (actual time=129.157..155.531 rows=711 loops=1)

  • Group Key: (((date_part('year'::text, erp_1.p_date) + ((erp_1.life_loco)::integer)::double precision) + ((erp_1.life_loco_pss)::integer)::double precision)), (CASE WHEN ((nlsv_1.kod_tjaga = 1) OR (nlsv_1.kod_tjaga = 6)) THEN 1 WHEN ((nlsv_1.kod_tjaga = 2) OR (nlsv_1.kod_tjaga = 9)) THEN 7 ELSE NULL::integer END), erp_1.dor_prip
  • Group Key: (((date_part('year'::text, erp_1.p_date) + ((erp_1.life_loco)::integer)::double precision) + ((erp_1.life_loco_pss)::integer)::double precision)), (CASE WHEN ((nlsv_1.kod_tjaga = 1) OR (nlsv_1.kod_tjaga = 6)) THEN 1 WHEN ((nlsv_1.kod_tjaga = 2) OR (nlsv_1.kod_tjaga = 9)) THEN 7 ELSE NULL::integer END)
16. 33.193 140.590 ↓ 26.8 14,289 1

Sort (cost=739.90..741.24 rows=534 width=14) (actual time=129.144..140.590 rows=14,289 loops=1)

  • Sort Key: (((date_part('year'::text, erp_1.p_date) + ((erp_1.life_loco)::integer)::double precision) + ((erp_1.life_loco_pss)::integer)::double precision)), (CASE WHEN ((nlsv_1.kod_tjaga = 1) OR (nlsv_1.kod_tjaga = 6)) THEN 1 WHEN ((nlsv_1.kod_tjaga = 2) OR (nlsv_1.kod_tjaga = 9)) THEN 7 ELSE NULL::integer END), erp_1.dor_prip
  • Sort Method: quicksort Memory: 1,054kB
17. 40.508 107.397 ↓ 26.8 14,289 1

Nested Loop (cost=4.79..715.71 rows=534 width=14) (actual time=0.371..107.397 rows=14,289 loops=1)

18. 0.664 2.234 ↓ 17.6 335 1

Hash Join (cost=4.38..71.80 rows=19 width=6) (actual time=0.099..2.234 rows=335 loops=1)

  • Hash Cond: (nls_1.primary_vd = vdv_1.kod_vdvig)
19. 0.967 1.530 ↓ 17.6 335 1

Hash Join (cost=2.13..69.29 rows=19 width=10) (actual time=0.047..1.530 rows=335 loops=1)

  • Hash Cond: (nls_1.kod_tjaga = nlsv_1.kod_tjaga)
20. 0.540 0.540 ↑ 1.0 336 1

Seq Scan on nsi_loc_ser nls_1 (cost=0.00..65.71 rows=336 width=10) (actual time=0.011..0.540 rows=336 loops=1)

  • Filter: (primary_vd = ANY ('{11,21,50}'::integer[]))
  • Rows Removed by Filter: 225
21. 0.007 0.023 ↑ 1.0 4 1

Hash (cost=2.08..2.08 rows=4 width=2) (actual time=0.023..0.023 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 0.016 0.016 ↑ 1.0 4 1

Seq Scan on nsi_loc_vtjaga nlsv_1 (cost=0.00..2.08 rows=4 width=2) (actual time=0.010..0.016 rows=4 loops=1)

  • Filter: (kod_tjaga = ANY ('{1,6,9,2}'::integer[]))
  • Rows Removed by Filter: 7
23. 0.015 0.040 ↑ 1.0 11 1

Hash (cost=2.11..2.11 rows=11 width=4) (actual time=0.040..0.040 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.025 0.025 ↑ 1.0 11 1

Seq Scan on nsi_loc_vdvig vdv_1 (cost=0.00..2.11 rows=11 width=4) (actual time=0.011..0.025 rows=11 loops=1)

25. 64.655 64.655 ↑ 2.0 43 335

Index Scan using "EsPul_rzd_park_pkey" on espul_rzd_park erp_1 (cost=0.41..32.13 rows=85 width=19) (actual time=0.032..0.193 rows=43 loops=335)

  • Index Cond: (ser_loc = nls_1.kod_ser)
  • Filter: (((date_part('year'::text, p_date) + ((life_loco)::integer)::double precision) + ((life_loco_pss)::integer)::double precision) >= '2017'::double precision)
  • Rows Removed by Filter: 52
26. 749.054 1,546.197 ↓ 280.6 599,413 1

Merge Left Join (cost=324.44..376.08 rows=2,136 width=34) (actual time=385.398..1,546.197 rows=599,413 loops=1)

  • Merge Cond: ((pred.tjaga_type_id = iby2.tjaga_type_id) AND (pred.dor_prip = iby2.dor_prip) AND (pred.park_type_id = iby2.park_type_id))
27. 33.187 432.218 ↓ 13.0 27,837 1

Merge Left Join (cost=242.64..271.13 rows=2,136 width=26) (actual time=381.832..432.218 rows=27,837 loops=1)

  • Merge Cond: ((pred.tjaga_type_id = iby1.tjaga_type_id) AND (pred.dor_prip = iby1.dor_prip) AND (pred.park_type_id = iby1.park_type_id))
28. 3.608 379.056 ↑ 1.2 1,742 1

Sort (cost=160.85..166.19 rows=2,136 width=18) (actual time=377.798..379.056 rows=1,742 loops=1)

  • Sort Key: pred.tjaga_type_id, pred.dor_prip, pred.park_type_id
  • Sort Method: quicksort Memory: 175kB
29. 375.448 375.448 ↑ 1.2 1,742 1

CTE Scan on predrasch pred (cost=0.00..42.72 rows=2,136 width=18) (actual time=183.027..375.448 rows=1,742 loops=1)

30. 17.925 19.975 ↓ 39.0 27,792 1

Sort (cost=81.79..83.57 rows=712 width=18) (actual time=4.026..19.975 rows=27,792 loops=1)

  • Sort Key: iby1.tjaga_type_id, iby1.dor_prip, iby1.park_type_id
  • Sort Method: quicksort Memory: 172kB
31. 2.050 2.050 ↓ 2.4 1,700 1

CTE Scan on predrasch iby1 (cost=0.00..48.06 rows=712 width=18) (actual time=0.012..2.050 rows=1,700 loops=1)

  • Filter: (year_istek_srok >= '2018'::double precision)
  • Rows Removed by Filter: 42
32. 363.224 364.925 ↓ 841.8 599,360 1

Sort (cost=81.79..83.57 rows=712 width=18) (actual time=3.561..364.925 rows=599,360 loops=1)

  • Sort Key: iby2.tjaga_type_id, iby2.dor_prip, iby2.park_type_id
  • Sort Method: quicksort Memory: 168kB
33. 1.701 1.701 ↓ 2.3 1,647 1

CTE Scan on predrasch iby2 (cost=0.00..48.06 rows=712 width=18) (actual time=0.018..1.701 rows=1,647 loops=1)

  • Filter: (year_istek_srok >= '2019'::double precision)
  • Rows Removed by Filter: 95
Planning time : 3.105 ms
Execution time : 2,433.220 ms