explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tLOC

Settings
# exclusive inclusive rows x rows loops node
1. 0.970 81.449 ↓ 16.0 353 1

Sort (cost=3,751.54..3,751.60 rows=22 width=106) (actual time=81.422..81.449 rows=353 loops=1)

  • Sort Key: (CASE WHEN (vlaky.vla_nazev = ''::text) THEN (vlaky.vla_typcislo)::text ELSE (((vlaky.vla_typcislo)::text || ' '::text) || vlaky.vla_nazev) END), (NULL::integer), optkalendar_1.optk_id
  • Sort Method: quicksort Memory: 74kB
2. 0.210 80.479 ↓ 16.0 353 1

Nested Loop Left Join (cost=3,450.36..3,751.05 rows=22 width=106) (actual time=73.339..80.479 rows=353 loops=1)

3. 0.185 74.621 ↓ 16.0 353 1

Nested Loop Left Join (cost=3,449.66..3,587.11 rows=22 width=81) (actual time=73.271..74.621 rows=353 loops=1)

4. 0.500 74.083 ↓ 16.0 353 1

Nested Loop Left Join (cost=3,449.11..3,571.77 rows=22 width=79) (actual time=73.249..74.083 rows=353 loops=1)

5. 1.089 73.583 ↓ 16.0 353 1

HashAggregate (cost=3,448.83..3,449.05 rows=22 width=75) (actual time=73.229..73.583 rows=353 loops=1)

  • Group Key: custsmstrains.csmstr_id, vlaky.vla_id, (CASE WHEN (vlaky.vla_nazev = ''::text) THEN (vlaky.vla_typcislo)::text ELSE (((vlaky.vla_typcislo)::text || ' '::text) || vlaky.vla_nazev) END), optkalendar_1.optk_id, (NULL::integer), (min(platnostopatreni_1.plt_zacatek)), (max(platnostopatreni_1.plt_konec)), custsmsrules.csmsr_id, custsmsrules.csmsr_type, custsmsrules.csmsr_sendsms, (CASE WHEN (custsmsrules.csmsr_type = 101) THEN 101 ELSE 0 END)
6. 0.071 72.494 ↓ 16.0 353 1

Append (cost=1,388.44..3,448.23 rows=22 width=75) (actual time=36.994..72.494 rows=353 loops=1)

7. 0.186 42.815 ↑ 5.7 3 1

Nested Loop Semi Join (cost=1,388.44..1,562.30 rows=17 width=75) (actual time=36.993..42.815 rows=3 loops=1)

  • Join Filter: (optkalendar_1.optk_id = platnostopatreni.plt_fk_optkalendar)
8. 0.521 38.965 ↓ 7.2 229 1

Nested Loop Left Join (cost=1,387.32..1,507.52 rows=32 width=71) (actual time=36.904..38.965 rows=229 loops=1)

9. 0.380 37.757 ↓ 7.2 229 1

Hash Right Join (cost=1,387.04..1,432.27 rows=32 width=39) (actual time=36.859..37.757 rows=229 loops=1)

  • Hash Cond: (optkalendar_3.optk_id = optkalendar_1.optk_id)
10. 2.494 15.288 ↑ 1.1 1,707 1

HashAggregate (cost=483.43..502.34 rows=1,891 width=20) (actual time=14.707..15.288 rows=1,707 loops=1)

  • Group Key: optkalendar_3.optk_id
11. 2.928 12.794 ↓ 1.5 4,485 1

Hash Join (cost=296.08..460.85 rows=3,011 width=20) (actual time=3.121..12.794 rows=4,485 loops=1)

  • Hash Cond: (opatreni_2.opt_fk_vyluka = vyluky_2.vlk_id)
  • Join Filter: LEAST(vyluky_2.vlk_platnost, opatreni_2.opt_platnost, optkalendar_3.optk_platnost, platnostopatreni_1.plt_platnost)
  • Rows Removed by Join Filter: 1565
12. 2.666 9.243 ↓ 1.0 6,050 1

Hash Join (cost=258.13..407.05 rows=6,022 width=27) (actual time=2.431..9.243 rows=6,050 loops=1)

  • Hash Cond: (optkalendar_3.optk_fk_opatreni = opatreni_2.opt_id)
13. 3.159 5.585 ↓ 1.0 6,050 1

Hash Join (cost=204.55..337.61 rows=6,022 width=26) (actual time=1.420..5.585 rows=6,050 loops=1)

  • Hash Cond: (platnostopatreni_1.plt_fk_optkalendar = optkalendar_3.optk_id)
14. 1.043 1.043 ↓ 1.0 6,050 1

Seq Scan on platnostopatreni platnostopatreni_1 (cost=0.00..117.22 rows=6,022 width=21) (actual time=0.010..1.043 rows=6,050 loops=1)

15. 0.504 1.383 ↓ 1.0 1,892 1

Hash (cost=180.91..180.91 rows=1,891 width=9) (actual time=1.383..1.383 rows=1,892 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 92kB
16. 0.879 0.879 ↓ 1.0 1,892 1

Seq Scan on optkalendar optkalendar_3 (cost=0.00..180.91 rows=1,891 width=9) (actual time=0.009..0.879 rows=1,892 loops=1)

17. 0.431 0.992 ↓ 1.0 1,627 1

Hash (cost=33.26..33.26 rows=1,626 width=9) (actual time=0.992..0.992 rows=1,627 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
18. 0.561 0.561 ↓ 1.0 1,627 1

Seq Scan on opatreni opatreni_2 (cost=0.00..33.26 rows=1,626 width=9) (actual time=0.015..0.561 rows=1,627 loops=1)

19. 0.290 0.623 ↓ 1.0 1,110 1

Hash (cost=24.09..24.09 rows=1,109 width=5) (actual time=0.623..0.623 rows=1,110 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 57kB
20. 0.333 0.333 ↓ 1.0 1,110 1

Seq Scan on vyluky vyluky_2 (cost=0.00..24.09 rows=1,109 width=5) (actual time=0.018..0.333 rows=1,110 loops=1)

21. 0.095 22.089 ↓ 7.2 229 1

Hash (cost=903.21..903.21 rows=32 width=23) (actual time=22.089..22.089 rows=229 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
22. 0.255 21.994 ↓ 7.2 229 1

Hash Join (cost=854.44..903.21 rows=32 width=23) (actual time=21.077..21.994 rows=229 loops=1)

  • Hash Cond: (custsmstrains.csmstr_fk_custsmsrules = custsmsrules.csmsr_id)
23. 0.754 0.754 ↑ 1.0 1,109 1

Seq Scan on custsmstrains (cost=0.00..44.29 rows=1,109 width=12) (actual time=0.025..0.754 rows=1,109 loops=1)

  • Filter: csmstr_valid
  • Rows Removed by Filter: 220
24. 1.340 20.985 ↓ 3.9 2,812 1

Hash (cost=845.35..845.35 rows=727 width=15) (actual time=20.985..20.985 rows=2,812 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 164kB
25. 1.982 19.645 ↓ 3.9 2,812 1

Merge Join (cost=284.11..845.35 rows=727 width=15) (actual time=1.384..19.645 rows=2,812 loops=1)

  • Merge Cond: (custsmsrules.csmsr_fk_optkalendar = optkalendar_1.optk_id)
26. 16.071 16.071 ↓ 1.0 2,812 1

Index Scan using ix_csmsr_fk_optkalendar on custsmsrules (cost=0.29..2,055.21 rows=2,749 width=11) (actual time=0.034..16.071 rows=2,812 loops=1)

  • Filter: (csmsr_valid AND (csmsr_type >= 102) AND (csmsr_type <= 198))
  • Rows Removed by Filter: 22719
27. 0.934 1.592 ↓ 1.0 1,892 1

Sort (cost=283.83..288.55 rows=1,891 width=4) (actual time=1.341..1.592 rows=1,892 loops=1)

  • Sort Key: optkalendar_1.optk_id
  • Sort Method: quicksort Memory: 137kB
28. 0.658 0.658 ↓ 1.0 1,892 1

Seq Scan on optkalendar optkalendar_1 (cost=0.00..180.91 rows=1,891 width=4) (actual time=0.008..0.658 rows=1,892 loops=1)

29. 0.687 0.687 ↑ 1.0 1 229

Index Scan using pk_vlaky on vlaky (cost=0.29..2.34 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=229)

  • Index Cond: (custsmstrains.csmstr_fk_vlaky = vla_id)
30. 0.229 3.664 ↓ 0.0 0 229

Nested Loop (cost=1.11..1.70 rows=1 width=8) (actual time=0.016..0.016 rows=0 loops=229)

  • Join Filter: (LEAST(vyluky_1.vlk_platnost, opatreni_1.opt_platnost, optkalendar_2.optk_platnost, platnostopatreni.plt_platnost) AND (optkalendar_2.optk_id = platnostopatreni.plt_fk_optkalendar))
31. 0.458 2.519 ↑ 1.0 1 229

Nested Loop (cost=0.83..1.23 rows=1 width=7) (actual time=0.011..0.011 rows=1 loops=229)

32. 0.458 1.603 ↑ 1.0 1 229

Nested Loop (cost=0.56..0.89 rows=1 width=10) (actual time=0.007..0.007 rows=1 loops=229)

33. 0.687 0.687 ↑ 1.0 1 229

Index Scan using pk_optkalendar on optkalendar optkalendar_2 (cost=0.28..0.54 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=229)

  • Index Cond: (optk_id = custsmsrules.csmsr_fk_optkalendar)
34. 0.458 0.458 ↑ 1.0 1 229

Index Scan using pk_opatreni on opatreni opatreni_1 (cost=0.28..0.35 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=229)

  • Index Cond: (opt_id = optkalendar_2.optk_fk_opatreni)
35. 0.458 0.458 ↑ 1.0 1 229

Index Scan using pk_vyluky on vyluky vyluky_1 (cost=0.28..0.34 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=229)

  • Index Cond: (vlk_id = opatreni_1.opt_fk_vyluka)
36. 0.916 0.916 ↓ 0.0 0 229

Index Scan using ix_plt_fk_optkalendar on platnostopatreni (cost=0.28..0.45 rows=1 width=5) (actual time=0.004..0.004 rows=0 loops=229)

  • Index Cond: (plt_fk_optkalendar = custsmsrules.csmsr_fk_optkalendar)
  • Filter: "overlaps"(plt_zacatek, plt_konec, '2019-08-10 00:23:47'::timestamp without time zone, '2019-08-11 00:23:47'::timestamp without time zone)
  • Rows Removed by Filter: 3
37. 0.608 29.608 ↓ 70.0 350 1

Nested Loop Semi Join (cost=1,397.40..1,885.59 rows=5 width=75) (actual time=16.764..29.608 rows=350 loops=1)

38. 0.941 23.225 ↓ 4.7 385 1

Nested Loop Left Join (cost=1,396.70..1,794.30 rows=82 width=55) (actual time=16.670..23.225 rows=385 loops=1)

39. 0.283 20.744 ↓ 4.7 385 1

Hash Join (cost=1,396.42..1,601.48 rows=82 width=23) (actual time=16.642..20.744 rows=385 loops=1)

  • Hash Cond: (appusers_1.appuser_role = sysroles_1.sysrole_id)
40. 0.248 20.430 ↓ 4.7 385 1

Hash Join (cost=1,395.06..1,599.85 rows=82 width=25) (actual time=16.562..20.430 rows=385 loops=1)

  • Hash Cond: (mimoradnosti_1.mimo_fk_appuser = appusers_1.appuser_id)
41. 0.322 20.099 ↓ 4.7 385 1

Hash Join (cost=1,389.51..1,594.08 rows=82 width=27) (actual time=16.457..20.099 rows=385 loops=1)

  • Hash Cond: (mimoradnosti_1.mimo_fk_mimopricina = mimopriciny_1.mpric_id)
42. 0.576 19.753 ↓ 4.3 385 1

Nested Loop (cost=1,388.06..1,592.35 rows=90 width=31) (actual time=16.408..19.753 rows=385 loops=1)

43. 0.488 17.252 ↓ 1.0 385 1

Hash Join (cost=1,387.78..1,434.98 rows=367 width=19) (actual time=16.357..17.252 rows=385 loops=1)

  • Hash Cond: (custsmstrains_1.csmstr_fk_custsmsrules = custsmsrules_1.csmsr_id)
44. 0.693 0.693 ↑ 1.0 1,109 1

Seq Scan on custsmstrains custsmstrains_1 (cost=0.00..44.29 rows=1,109 width=12) (actual time=0.011..0.693 rows=1,109 loops=1)

  • Filter: csmstr_valid
  • Rows Removed by Filter: 220
45. 3.527 16.071 ↓ 1.3 10,740 1

Hash (cost=1,282.13..1,282.13 rows=8,452 width=11) (actual time=16.071..16.071 rows=10,740 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 590kB
46. 12.544 12.544 ↓ 1.3 10,740 1

Seq Scan on custsmsrules custsmsrules_1 (cost=0.00..1,282.13 rows=8,452 width=11) (actual time=0.019..12.544 rows=10,740 loops=1)

  • Filter: (csmsr_sendsms AND csmsr_valid AND (csmsr_type >= 1) AND (csmsr_type <= 98))
  • Rows Removed by Filter: 14791
47. 1.925 1.925 ↑ 1.0 1 385

Index Scan using pk_mimoradnosti on mimoradnosti mimoradnosti_1 (cost=0.28..0.43 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=385)

  • Index Cond: (mimo_id = custsmsrules_1.csmsr_fk_mimoradnosti)
  • Filter: "overlaps"(mimo_zacatek, CURRENT_TIMESTAMP, ('2019-08-10 00:23:47'::timestamp without time zone)::timestamp with time zone, ('2019-08-11 00:23:47'::timestamp without time zone)::timestamp with time zone)
48. 0.014 0.024 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=4) (actual time=0.024..0.024 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 0.010 0.010 ↑ 1.0 20 1

Seq Scan on mimopriciny mimopriciny_1 (cost=0.00..1.20 rows=20 width=4) (actual time=0.007..0.010 rows=20 loops=1)

50. 0.034 0.083 ↑ 1.0 113 1

Hash (cost=4.13..4.13 rows=113 width=6) (actual time=0.083..0.083 rows=113 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
51. 0.049 0.049 ↑ 1.0 113 1

Seq Scan on appusers appusers_1 (cost=0.00..4.13 rows=113 width=6) (actual time=0.013..0.049 rows=113 loops=1)

52. 0.013 0.031 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=4) (actual time=0.031..0.031 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
53. 0.018 0.018 ↑ 1.0 16 1

Seq Scan on sysroles sysroles_1 (cost=0.00..1.16 rows=16 width=4) (actual time=0.015..0.018 rows=16 loops=1)

54. 1.540 1.540 ↑ 1.0 1 385

Index Scan using pk_vlaky on vlaky vlaky_1 (cost=0.29..2.34 rows=1 width=13) (actual time=0.004..0.004 rows=1 loops=385)

  • Index Cond: (custsmstrains_1.csmstr_fk_vlaky = vla_id)
55. 0.105 5.775 ↑ 1.0 1 385

Nested Loop (cost=0.70..1.10 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=385)

56. 0.840 4.620 ↑ 1.0 1 385

Nested Loop (cost=0.56..0.83 rows=1 width=6) (actual time=0.012..0.012 rows=1 loops=385)

57. 0.840 3.080 ↑ 1.0 1 385

Nested Loop (cost=0.42..0.63 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=385)

58. 1.540 1.540 ↑ 1.0 1 385

Index Scan using pk_mimoradnosti on mimoradnosti mimoradnosti_2 (cost=0.28..0.44 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=385)

  • Index Cond: (mimo_id = custsmsrules_1.csmsr_fk_mimoradnosti)
  • Filter: (mimo_platnost AND (mimo_aktivni OR ((mimo_ukonceno IS NOT NULL) AND (NOT mimo_aktivni) AND (CASE WHEN ((mimo_ukonceno IS NOT NULL) AND (mimo_infoukonceni IS NOT NULL)) THEN (mimo_ukonceno + '02:00:00'::interval) WHEN ((mimo_ukonceno IS NOT NULL) AND (mimo_infoukonceni IS NULL)) THEN (mimo_ukonceno + '00:30:00'::interval) ELSE (mimo_konecplanovany + '1 year'::interval) END >= now()))) AND ((NOT mimo_archivovano) OR ((mimo_ukonceno IS NOT NULL) AND mimo_archivovano AND (CASE WHEN ((mimo_ukonceno IS NOT NULL) AND (mimo_infoukonceni IS NOT NULL)) THEN (mimo_ukonceno + '02:00:00'::interval) WHEN ((mimo_ukonceno IS NOT NULL) AND (mimo_infoukonceni IS NULL)) THEN (mimo_ukonceno + '00:30:00'::interval) ELSE (mimo_konecplanovany + '1 year'::interval) END >= now()))))
  • Rows Removed by Filter: 0
59. 0.700 0.700 ↑ 1.0 1 350

Index Only Scan using pk_mimopriciny on mimopriciny mimopriciny_2 (cost=0.14..0.18 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=350)

  • Index Cond: (mpric_id = mimoradnosti_2.mimo_fk_mimopricina)
  • Heap Fetches: 350
60. 0.700 0.700 ↑ 1.0 1 350

Index Scan using pk_archiveusers on appusers appusers_2 (cost=0.14..0.20 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=350)

  • Index Cond: (appuser_id = mimoradnosti_2.mimo_fk_appuser)
61. 1.050 1.050 ↑ 1.0 1 350

Index Only Scan using pk_sysroles on sysroles sysroles_2 (cost=0.14..0.26 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=350)

  • Index Cond: (sysrole_id = appusers_2.appuser_role)
  • Heap Fetches: 350
62. 0.000 0.000 ↓ 0.0 0 353

Index Scan using pk_optkalendar on optkalendar (cost=0.28..5.57 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=353)

  • Index Cond: (optkalendar_1.optk_id = optk_id)
63. 0.332 0.353 ↓ 0.0 0 353

Nested Loop (cost=0.56..0.69 rows=1 width=6) (actual time=0.001..0.001 rows=0 loops=353)

64. 0.000 0.000 ↓ 0.0 0 353

Index Scan using pk_opatreni on opatreni (cost=0.28..0.35 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=353)

  • Index Cond: (optkalendar.optk_fk_opatreni = opt_id)
65. 0.021 0.021 ↑ 1.0 1 3

Index Scan using pk_vyluky on vyluky (cost=0.28..0.34 rows=1 width=6) (actual time=0.007..0.007 rows=1 loops=3)

  • Index Cond: (vlk_id = opatreni.opt_fk_vyluka)
66. 0.362 5.648 ↑ 1.0 1 353

Nested Loop (cost=0.70..7.44 rows=1 width=29) (actual time=0.015..0.016 rows=1 loops=353)

67. 0.712 4.236 ↑ 1.0 1 353

Nested Loop (cost=0.56..7.17 rows=1 width=31) (actual time=0.011..0.012 rows=1 loops=353)

68. 0.712 2.824 ↑ 1.0 1 353

Nested Loop (cost=0.42..7.00 rows=1 width=33) (actual time=0.008..0.008 rows=1 loops=353)

69. 1.412 1.412 ↑ 1.0 1 353

Index Scan using pk_mimoradnosti on mimoradnosti (cost=0.28..6.85 rows=1 width=37) (actual time=0.004..0.004 rows=1 loops=353)

  • Index Cond: ((NULL::integer) = mimo_id)
70. 0.700 0.700 ↑ 1.0 1 350

Index Only Scan using pk_mimopriciny on mimopriciny (cost=0.14..0.16 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=350)

  • Index Cond: (mpric_id = mimoradnosti.mimo_fk_mimopricina)
  • Heap Fetches: 350
71. 0.700 0.700 ↑ 1.0 1 350

Index Scan using pk_archiveusers on appusers (cost=0.14..0.16 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=350)

  • Index Cond: (appuser_id = mimoradnosti.mimo_fk_appuser)
72. 1.050 1.050 ↑ 1.0 1 350

Index Only Scan using pk_sysroles on sysroles (cost=0.14..0.26 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=350)

  • Index Cond: (sysrole_id = appusers.appuser_role)
  • Heap Fetches: 350
Planning time : 14.316 ms
Execution time : 82.965 ms