explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hV8s : 8s

Settings
# exclusive inclusive rows x rows loops node
1. 0.050 8,574.503 ↑ 28.6 7 1

Sort (cost=1,044,305.41..1,044,305.91 rows=200 width=64) (actual time=8,574.502..8,574.503 rows=7 loops=1)

  • Sort Key: a.level
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=571,482
  • Functions: 167
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 26.794 ms, Inlining 85.049 ms, Optimization 1236.068 ms, Emission 755.320 ms, Total 2103.231 ms
2.          

CTE cte_dat

3. 18.123 8,572.374 ↑ 8.0 138 1

GroupAggregate (cost=122,360.94..122,464.59 rows=1,106 width=68) (actual time=8,548.572..8,572.374 rows=138 loops=1)

  • Group Key: (date_trunc('week'::text, s.datum)), s.stav, ((s.pj)::text), (((date_trunc('week'::text, to_timestamp('2018-12-31 00:00:00'::text, 'YYYY-MM-DD HH24:mi:ss'::text)) - '5 mons'::interval) + '1 day'::interval)), (date_trunc('week'::text, to_timestamp('2019-01-06 23:59:59'::text, 'YYYY-MM-DD HH24:mi:ss'::text)))
  • Buffers: shared hit=571,475
4. 61.090 8,554.251 ↓ 17.4 73,009 1

Sort (cost=122,360.94..122,371.40 rows=4,185 width=68) (actual time=8,548.375..8,554.251 rows=73,009 loops=1)

  • Sort Key: (date_trunc('week'::text, s.datum)), ((s.pj)::text)
  • Sort Method: quicksort Memory: 10,796kB
  • Buffers: shared hit=571,475
5. 197.782 8,493.161 ↓ 17.4 73,009 1

Subquery Scan on s (cost=121,754.96..122,109.19 rows=4,185 width=68) (actual time=8,193.077..8,493.161 rows=73,009 loops=1)

  • Filter: ((s.stav = 100) AND ((s.stav = ANY ('{102,307,308}'::integer[])) OR ((s.stav <> 102) AND (s.min_stav <> 102))))
  • Rows Removed by Filter: 25,999
  • Buffers: shared hit=571,472
6. 90.380 8,295.379 ↓ 14.5 99,008 1

WindowAgg (cost=121,754.96..121,891.20 rows=6,812 width=1,097) (actual time=8,193.026..8,295.379 rows=99,008 loops=1)

  • Buffers: shared hit=571,472
7. 71.777 8,204.999 ↓ 14.5 99,008 1

Sort (cost=121,754.96..121,771.99 rows=6,812 width=34) (actual time=8,192.961..8,204.999 rows=99,008 loops=1)

  • Sort Key: s_1.zacatek, s_1.kod_smeny
  • Sort Method: quicksort Memory: 10,808kB
  • Buffers: shared hit=571,472
8. 28.886 8,133.222 ↓ 14.5 99,008 1

Nested Loop Left Join (cost=89,366.31..121,321.24 rows=6,812 width=34) (actual time=5,874.683..8,133.222 rows=99,008 loops=1)

  • Buffers: shared hit=571,469
9. 435.820 8,104.336 ↓ 14.5 99,008 1

Nested Loop Left Join (cost=89,366.31..121,235.08 rows=6,812 width=34) (actual time=5,874.665..8,104.336 rows=99,008 loops=1)

  • Join Filter: ((s_1.zacatek >= o.obdobi_cas_od) AND (s_1.zacatek <= o.obdobi_cas_do))
  • Rows Removed by Join Filter: 5,742,464
  • Buffers: shared hit=571,468
10. 32.236 7,371.492 ↓ 93.7 99,008 1

Nested Loop Left Join (cost=89,366.31..120,160.50 rows=1,057 width=34) (actual time=5,874.613..7,371.492 rows=99,008 loops=1)

  • Buffers: shared hit=571,467
11. 79.796 6,844.216 ↓ 93.7 99,008 1

Nested Loop Left Join (cost=89,366.02..119,781.36 rows=1,057 width=38) (actual time=5,874.572..6,844.216 rows=99,008 loops=1)

  • Buffers: shared hit=339,267
12. 29.076 6,170.372 ↓ 93.7 99,008 1

Hash Left Join (cost=89,365.73..119,404.87 rows=1,057 width=42) (actual time=5,874.474..6,170.372 rows=99,008 loops=1)

  • Hash Cond: (s_1.org_jednotka_id_vykazani_smeny = oj2.klic)
  • Buffers: shared hit=41,239
13. 25.581 6,141.251 ↓ 93.7 99,008 1

Hash Join (cost=89,361.79..119,386.40 rows=1,057 width=32) (actual time=5,874.410..6,141.251 rows=99,008 loops=1)

  • Hash Cond: (m2.org_jednotka_id = oj.klic)
  • Buffers: shared hit=41,237
14. 24.660 4,037.184 ↓ 93.7 99,008 1

Hash Left Join (cost=89,357.86..119,367.94 rows=1,057 width=36) (actual time=3,795.901..4,037.184 rows=99,008 loops=1)

  • Hash Cond: (vss.stav = stavy.stav)
  • Buffers: shared hit=41,235
15. 28.507 4,012.482 ↓ 93.7 99,008 1

Hash Join (cost=89,355.48..119,351.03 rows=1,057 width=36) (actual time=3,795.821..4,012.482 rows=99,008 loops=1)

  • Hash Cond: (s_1.misto_nastupu = m_1.kod)
  • Buffers: shared hit=41,234
16. 369.866 3,975.186 ↓ 93.7 99,008 1

Hash Right Join (cost=89,148.26..119,129.27 rows=1,057 width=60) (actual time=3,786.996..3,975.186 rows=99,008 loops=1)

  • Hash Cond: ((vss.kod_smeny = s_1.kod_smeny) AND (vss.id_vyhodnoceni = s_1.id_vyhodnoceni))
  • Buffers: shared hit=41,119
17. 110.207 110.207 ↑ 1.0 1,129,535 1

Seq Scan on ui_cdc_s1_vyh_smeny_stavy vss (cost=0.00..21,509.35 rows=1,129,535 width=16) (actual time=0.027..110.207 rows=1,129,535 loops=1)

  • Buffers: shared hit=10,214
18. 35.213 3,495.113 ↓ 91.5 96,725 1

Hash (cost=89,132.40..89,132.40 rows=1,057 width=60) (actual time=3,495.113..3,495.113 rows=96,725 loops=1)

  • Buckets: 131,072 (originally 2048) Batches: 1 (originally 1) Memory Usage: 10,092kB
  • Buffers: shared hit=30,905
19. 35.733 3,459.900 ↓ 91.5 96,725 1

Hash Join (cost=1,535.80..89,132.40 rows=1,057 width=60) (actual time=20.019..3,459.900 rows=96,725 loops=1)

  • Hash Cond: (s_1.misto_nastupu = m2.kod)
  • Join Filter: ((s_1.zacatek >= m2.platnost_od) AND (s_1.zacatek < m2.platnost_do))
  • Rows Removed by Join Filter: 71,153
  • Buffers: shared hit=30,905
20. 3,405.100 3,405.100 ↓ 18.4 96,725 1

Seq Scan on ui_cdc_s1_smena s_1 (cost=0.00..87,472.58 rows=5,260 width=40) (actual time=0.900..3,405.100 rows=96,725 loops=1)

  • Filter: ((stav >= 91) AND (date_trunc('week'::text, zacatek) <= date_trunc('week'::text, date_trunc('week'::text, to_timestamp('2019-01-06 23:59:59'::text, 'YYYY-MM-DD HH24:mi:ss'::text)))) AND (date_trunc('week'::text, zacatek) >= date_trunc('week'::text, ((date_trunc('week'::text, to_timestamp('2018-12-31 00:00:00'::text, 'YYYY-MM-DD HH24:mi:ss'::text)) - '5 mons'::interval) + '1 day'::interval))))
  • Rows Removed by Filter: 1,255,171
  • Buffers: shared hit=30,017
21. 1.540 19.067 ↓ 1.0 7,805 1

Hash (cost=1,441.20..1,441.20 rows=7,568 width=28) (actual time=19.067..19.067 rows=7,805 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 552kB
  • Buffers: shared hit=888
22. 6.561 17.527 ↓ 1.0 7,805 1

Hash Join (cost=207.23..1,441.20 rows=7,568 width=28) (actual time=1.164..17.527 rows=7,805 loops=1)

  • Hash Cond: (m2.kod = m.kod)
  • Buffers: shared hit=888
23. 9.847 9.847 ↑ 1.2 22,541 1

Seq Scan on ui_cdc_mista_h m2 (cost=0.00..1,053.21 rows=28,021 width=20) (actual time=0.011..9.847 rows=22,541 loops=1)

  • Buffers: shared hit=773
24. 0.559 1.119 ↑ 1.0 4,099 1

Hash (cost=155.99..155.99 rows=4,099 width=8) (actual time=1.119..1.119 rows=4,099 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 225kB
  • Buffers: shared hit=115
25. 0.560 0.560 ↑ 1.0 4,099 1

Seq Scan on ui_cdc_mista m (cost=0.00..155.99 rows=4,099 width=8) (actual time=0.012..0.560 rows=4,099 loops=1)

  • Buffers: shared hit=115
26. 0.580 8.789 ↑ 1.0 4,099 1

Hash (cost=155.99..155.99 rows=4,099 width=8) (actual time=8.789..8.789 rows=4,099 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 225kB
  • Buffers: shared hit=115
27. 8.209 8.209 ↑ 1.0 4,099 1

Seq Scan on ui_cdc_mista m_1 (cost=0.00..155.99 rows=4,099 width=8) (actual time=0.026..8.209 rows=4,099 loops=1)

  • Buffers: shared hit=115
28. 0.017 0.042 ↑ 1.0 61 1

Hash (cost=1.61..1.61 rows=61 width=4) (actual time=0.042..0.042 rows=61 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
29. 0.025 0.025 ↑ 1.0 61 1

Seq Scan on ui_cdc_s1_stavy_vyhodnoceni stavy (cost=0.00..1.61 rows=61 width=4) (actual time=0.019..0.025 rows=61 loops=1)

  • Buffers: shared hit=1
30. 0.040 2,078.486 ↑ 1.0 86 1

Hash (cost=2.86..2.86 rows=86 width=4) (actual time=2,078.486..2,078.486 rows=86 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=2
31. 2,078.446 2,078.446 ↑ 1.0 86 1

Seq Scan on ui_cdc_org_jednotky oj (cost=0.00..2.86 rows=86 width=4) (actual time=2,078.413..2,078.446 rows=86 loops=1)

  • Buffers: shared hit=2
32. 0.024 0.045 ↑ 1.0 86 1

Hash (cost=2.86..2.86 rows=86 width=18) (actual time=0.045..0.045 rows=86 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=2
33. 0.021 0.021 ↑ 1.0 86 1

Seq Scan on ui_cdc_org_jednotky oj2 (cost=0.00..2.86 rows=86 width=18) (actual time=0.011..0.021 rows=86 loops=1)

  • Buffers: shared hit=2
34. 594.048 594.048 ↑ 1.0 1 99,008

Index Only Scan using cdc_imp_zamestnanci_crew_sap_no_idx on cdc_imp_zamestnanci z (cost=0.29..0.35 rows=1 width=9) (actual time=0.006..0.006 rows=1 loops=99,008)

  • Index Cond: (crew_sap_no = (s_1.id_zamestnance)::text)
  • Heap Fetches: 99,008
  • Buffers: shared hit=298,028
35. 495.040 495.040 ↑ 1.0 1 99,008

Index Only Scan using cdc_imp_zamestnanci_crew_sap_no_idx on cdc_imp_zamestnanci z2 (cost=0.29..0.35 rows=1 width=9) (actual time=0.005..0.005 rows=1 loops=99,008)

  • Index Cond: (crew_sap_no = CASE WHEN (s_1.id_zamestnance_validator = '-1'::integer) THEN NULL::text ELSE (s_1.id_zamestnance_validator)::text END)
  • Heap Fetches: 77,400
  • Buffers: shared hit=232,200
36. 296.991 297.024 ↓ 1.0 59 99,008

Materialize (cost=0.00..1.87 rows=58 width=16) (actual time=0.000..0.003 rows=59 loops=99,008)

  • Buffers: shared hit=1
37. 0.033 0.033 ↓ 1.0 59 1

Seq Scan on cdc_s1_obdobi o (cost=0.00..1.58 rows=58 width=16) (actual time=0.028..0.033 rows=59 loops=1)

  • Buffers: shared hit=1
38. 0.000 0.000 ↓ 0.0 0 99,008

Materialize (cost=0.00..1.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=99,008)

  • Buffers: shared hit=1
39. 0.012 0.012 ↓ 0.0 0 1

Seq Scan on cdc_anonymizace_dat ad (cost=0.00..1.01 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=1)

  • Filter: povoleno
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
40.          

CTE cte_minmax_date

41. 0.101 8,572.569 ↑ 200.0 1 1

HashAggregate (cost=33.18..38.18 rows=200 width=32) (actual time=8,572.569..8,572.569 rows=1 loops=1)

  • Group Key: s_2.datum_od, s_2.datum_do
  • Buffers: shared hit=571,475
42. 8,572.468 8,572.468 ↑ 8.0 138 1

CTE Scan on cte_dat s_2 (cost=0.00..22.12 rows=1,106 width=24) (actual time=8,548.575..8,572.468 rows=138 loops=1)

  • Buffers: shared hit=571,475
43.          

CTE cte_tydny

44. 0.098 8,573.301 ↑ 1,739.1 506 1

Unique (cost=617,187.36..672,187.36 rows=880,000 width=28) (actual time=8,573.173..8,573.301 rows=506 loops=1)

  • Buffers: shared hit=571,476
45. 0.282 8,573.203 ↑ 8,695.7 506 1

Sort (cost=617,187.36..628,187.36 rows=4,400,000 width=28) (actual time=8,573.172..8,573.203 rows=506 loops=1)

  • Sort Key: mmd.mesic_od, mmd.mesic_do, (date_trunc('week'::text, tyden.tyden)), cis.stav
  • Sort Method: quicksort Memory: 64kB
  • Buffers: shared hit=571,476
46. 0.202 8,572.921 ↑ 8,695.7 506 1

Nested Loop (cost=0.00..70,005.82 rows=4,400,000 width=28) (actual time=8,572.671..8,572.921 rows=506 loops=1)

  • Buffers: shared hit=571,476
47. 0.012 8,572.627 ↑ 8,695.7 23 1

Nested Loop (cost=0.00..4,004.00 rows=200,000 width=24) (actual time=8,572.620..8,572.627 rows=23 loops=1)

  • Buffers: shared hit=571,475
48. 8,572.576 8,572.576 ↑ 200.0 1 1

CTE Scan on cte_minmax_date mmd (cost=0.00..4.00 rows=200 width=16) (actual time=8,572.575..8,572.576 rows=1 loops=1)

  • Buffers: shared hit=571,475
49. 0.039 0.039 ↑ 43.5 23 1

Function Scan on generate_series tyden (cost=0.00..10.00 rows=1,000 width=8) (actual time=0.037..0.039 rows=23 loops=1)

50. 0.044 0.092 ↑ 1.0 22 23

Materialize (cost=0.00..1.87 rows=22 width=4) (actual time=0.002..0.004 rows=22 loops=23)

  • Buffers: shared hit=1
51. 0.048 0.048 ↑ 1.0 22 1

Seq Scan on cdc_stavy_vyhodnoceni cis (cost=0.00..1.76 rows=22 width=4) (actual time=0.043..0.048 rows=22 loops=1)

  • Filter: (id_scenare = 1)
  • Rows Removed by Filter: 39
  • Buffers: shared hit=1
52.          

CTE cte_agg

53. 0.318 8,574.147 ↑ 1,417.1 621 1

Sort (cost=225,405.14..227,605.14 rows=880,000 width=48) (actual time=8,574.105..8,574.147 rows=621 loops=1)

  • Sort Key: s_3.level, t.tyden
  • Sort Method: quicksort Memory: 58kB
  • Buffers: shared hit=571,479
54. 0.196 8,573.829 ↑ 1,417.1 621 1

Merge Left Join (cost=113,374.32..122,660.96 rows=880,000 width=48) (actual time=8,573.617..8,573.829 rows=621 loops=1)

  • Merge Cond: ((t.tyden = (date_trunc('week'::text, s_3.mesic))) AND (t.stav = s_3.stav))
  • Buffers: shared hit=571,476
55. 0.113 8,573.535 ↑ 1,739.1 506 1

Sort (cost=113,296.28..115,496.28 rows=880,000 width=12) (actual time=8,573.504..8,573.535 rows=506 loops=1)

  • Sort Key: t.tyden, t.stav
  • Sort Method: quicksort Memory: 48kB
  • Buffers: shared hit=571,476
56. 8,573.422 8,573.422 ↑ 1,739.1 506 1

CTE Scan on cte_tydny t (cost=0.00..17,600.00 rows=880,000 width=12) (actual time=8,573.181..8,573.422 rows=506 loops=1)

  • Buffers: shared hit=571,476
57. 0.053 0.098 ↑ 8.0 138 1

Sort (cost=78.03..80.80 rows=1,106 width=52) (actual time=0.089..0.098 rows=138 loops=1)

  • Sort Key: (date_trunc('week'::text, s_3.mesic)), s_3.stav
  • Sort Method: quicksort Memory: 40kB
58. 0.045 0.045 ↑ 8.0 138 1

CTE Scan on cte_dat s_3 (cost=0.00..22.12 rows=1,106 width=52) (actual time=0.005..0.045 rows=138 loops=1)

59. 0.181 8,574.453 ↑ 28.6 7 1

HashAggregate (cost=22,000.00..22,002.50 rows=200 width=64) (actual time=8,574.449..8,574.453 rows=7 loops=1)

  • Group Key: a.level
  • Buffers: shared hit=571,479
60. 8,574.272 8,574.272 ↑ 1,417.1 621 1

CTE Scan on cte_agg a (cost=0.00..17,600.00 rows=880,000 width=40) (actual time=8,574.107..8,574.272 rows=621 loops=1)

  • Buffers: shared hit=571,479JIT: