explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DSHI

Settings
# exclusive inclusive rows x rows loops node
1. 6.422 1,078.279 ↑ 20.0 1 1

HashAggregate (cost=10,344.93..10,345.18 rows=20 width=44) (actual time=1,078.278..1,078.279 rows=1 loops=1)

  • Group Key: v.yil, v.ay
2. 15.333 1,071.857 ↓ 1.0 6,250 1

Hash Left Join (cost=7,605.78..10,222.33 rows=6,130 width=1,245) (actual time=1,011.461..1,071.857 rows=6,250 loops=1)

  • Hash Cond: ((rvm.makine_id = wrvmp.makine_id) AND (rvm.vardiya_id = wrvmp.vardiya_id))
3.          

CTE w_randiman_vrd_makine_personel

4. 167.209 952.830 ↓ 485.6 22,337 1

GroupAggregate (cost=7,556.55..7,560.46 rows=46 width=60) (actual time=782.713..952.830 rows=22,337 loops=1)

  • Group Key: vrvmp.vardiya_id, vrvmp.makine_id
5. 37.482 785.621 ↓ 487.8 22,440 1

Sort (cost=7,556.55..7,556.67 rows=46 width=144) (actual time=782.686..785.621 rows=22,440 loops=1)

  • Sort Key: vrvmp.vardiya_id, vrvmp.makine_id
  • Sort Method: quicksort Memory: 3741kB
6. 11.651 748.139 ↓ 487.8 22,440 1

Subquery Scan on vrvmp (cost=7,006.43..7,555.28 rows=46 width=144) (actual time=148.928..748.139 rows=22,440 loops=1)

7. 41.938 736.488 ↓ 487.8 22,440 1

Nested Loop (cost=7,006.43..7,554.82 rows=46 width=1,224) (actual time=148.927..736.488 rows=22,440 loops=1)

  • Join Filter: (p.personel_gorev_id = pg.personel_gorev_id)
  • Rows Removed by Join Filter: 2851
8.          

CTE w_randiman_vrd_makine_personel

9. 188.090 353.336 ↑ 1.0 22,440 1

GroupAggregate (cost=4,563.68..6,998.21 rows=23,186 width=216) (actual time=148.256..353.336 rows=22,440 loops=1)

  • Group Key: rvmp.vardiya_id, vvm.makine_id, rvmp.personel_id, rvmp.personel_vardiya_grup_id, rvmp.sayac_birim_id, rvmp.birim_id
10. 81.610 165.246 ↑ 1.0 23,186 1

Sort (cost=4,563.68..4,621.65 rows=23,186 width=109) (actual time=148.218..165.246 rows=23,186 loops=1)

  • Sort Key: rvmp.vardiya_id, vvm.makine_id, rvmp.personel_id, rvmp.personel_vardiya_grup_id, rvmp.sayac_birim_id, rvmp.birim_id
  • Sort Method: external merge Disk: 2744kB
11. 38.801 83.636 ↑ 1.0 23,186 1

Hash Join (cost=1,825.86..2,882.58 rows=23,186 width=109) (actual time=39.434..83.636 rows=23,186 loops=1)

  • Hash Cond: (rvmp.veri_vrd_makine_id = vvm.veri_vrd_makine_id)
12. 5.944 5.944 ↑ 1.0 23,186 1

Seq Scan on randiman_vrd_makine_personel rvmp (cost=0.00..995.86 rows=23,186 width=109) (actual time=0.020..5.944 rows=23,186 loops=1)

13. 19.653 38.891 ↑ 1.0 56,397 1

Hash (cost=1,118.16..1,118.16 rows=56,616 width=8) (actual time=38.890..38.891 rows=56,397 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2716kB
14. 19.238 19.238 ↑ 1.0 56,397 1

Seq Scan on veri_vrd_makine vvm (cost=0.00..1,118.16 rows=56,616 width=8) (actual time=0.014..19.238 rows=56,397 loops=1)

15. 33.662 694.550 ↓ 487.8 22,440 1

Nested Loop (cost=8.22..551.85 rows=46 width=148) (actual time=148.908..694.550 rows=22,440 loops=1)

  • Join Filter: (v_1.vardiya_zaman_id = vz_1.vardiya_zaman_id)
  • Rows Removed by Join Filter: 22526
16. 41.443 660.888 ↓ 487.8 22,440 1

Nested Loop (cost=8.22..549.08 rows=46 width=152) (actual time=148.881..660.888 rows=22,440 loops=1)

17. 22.933 507.245 ↓ 487.8 22,440 1

Hash Join (cost=7.95..535.41 rows=46 width=148) (actual time=148.856..507.245 rows=22,440 loops=1)

  • Hash Cond: (wrvmp_1.makine_id = m_1.makine_id)
18. 19.688 484.243 ↓ 175.3 22,440 1

Hash Join (cost=3.33..530.44 rows=128 width=148) (actual time=148.392..484.243 rows=22,440 loops=1)

  • Hash Cond: (wrvmp_1.personel_id = p.personel_id)
19. 35.075 464.501 ↓ 48.4 22,440 1

Hash Join (cost=1.09..526.96 rows=464 width=148) (actual time=148.319..464.501 rows=22,440 loops=1)

  • Hash Cond: (wrvmp_1.personel_vardiya_grup_id = pvg.personel_vardiya_grup_id)
20. 429.391 429.391 ↑ 1.0 22,440 1

CTE Scan on w_randiman_vrd_makine_personel wrvmp_1 (cost=0.00..463.72 rows=23,186 width=160) (actual time=148.263..429.391 rows=22,440 loops=1)

21. 0.009 0.035 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=4) (actual time=0.035..0.035 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.026 0.026 ↑ 1.0 4 1

Seq Scan on personel_vardiya_gruplari pvg (cost=0.00..1.04 rows=4 width=4) (actual time=0.023..0.026 rows=4 loops=1)

23. 0.020 0.054 ↑ 1.0 55 1

Hash (cost=1.55..1.55 rows=55 width=8) (actual time=0.053..0.054 rows=55 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
24. 0.034 0.034 ↑ 1.0 55 1

Seq Scan on personeller p (cost=0.00..1.55 rows=55 width=8) (actual time=0.017..0.034 rows=55 loops=1)

25. 0.026 0.069 ↑ 1.0 72 1

Hash (cost=3.72..3.72 rows=72 width=4) (actual time=0.069..0.069 rows=72 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
26. 0.043 0.043 ↑ 1.0 72 1

Seq Scan on makineler m_1 (cost=0.00..3.72 rows=72 width=4) (actual time=0.024..0.043 rows=72 loops=1)

27. 112.200 112.200 ↑ 1.0 1 22,440

Index Scan using pk_vardiyalar on vardiyalar v_1 (cost=0.28..0.30 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=22,440)

  • Index Cond: (vardiya_id = wrvmp_1.vardiya_id)
28. 0.000 0.000 ↑ 1.5 2 22,440

Materialize (cost=0.00..1.04 rows=3 width=4) (actual time=0.000..0.000 rows=2 loops=22,440)

29. 0.013 0.013 ↑ 1.0 3 1

Seq Scan on vardiya_zamanlari vz_1 (cost=0.00..1.03 rows=3 width=4) (actual time=0.012..0.013 rows=3 loops=1)

30. 0.000 0.000 ↑ 6.0 1 22,440

Materialize (cost=0.00..1.09 rows=6 width=4) (actual time=0.000..0.000 rows=1 loops=22,440)

31. 0.017 0.017 ↑ 1.0 6 1

Seq Scan on personel_gorevleri pg (cost=0.00..1.06 rows=6 width=4) (actual time=0.011..0.017 rows=6 loops=1)

32.          

CTE w_devir_hesap_tipleri

33. 0.058 0.240 ↓ 1.0 72 1

Hash Join (cost=2.16..5.00 rows=71 width=20) (actual time=0.092..0.240 rows=72 loops=1)

  • Hash Cond: (pms.birim_id = b_1.birim_id)
34. 0.056 0.149 ↓ 1.0 72 1

Hash Join (cost=1.04..3.54 rows=71 width=17) (actual time=0.046..0.149 rows=72 loops=1)

  • Hash Cond: (pms.devir_hesap_tip_id = dht.devir_hesap_tip_id)
35. 0.073 0.073 ↓ 1.0 72 1

Seq Scan on port_makine_sayac pms (cost=0.00..1.91 rows=71 width=12) (actual time=0.017..0.073 rows=72 loops=1)

  • Filter: (calisiyor_bilgisi_alinacak AND (sayac_tip_id = 1))
  • Rows Removed by Filter: 1
36. 0.010 0.020 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=9) (actual time=0.020..0.020 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.010 0.010 ↑ 1.0 2 1

Seq Scan on devir_hesap_tipleri dht (cost=0.00..1.02 rows=2 width=9) (actual time=0.007..0.010 rows=2 loops=1)

38. 0.017 0.033 ↓ 1.2 6 1

Hash (cost=1.05..1.05 rows=5 width=7) (actual time=0.033..0.033 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.016 0.016 ↓ 1.2 6 1

Seq Scan on birimler b_1 (cost=0.00..1.05 rows=5 width=7) (actual time=0.011..0.016 rows=6 loops=1)

40. 4.419 45.849 ↓ 1.0 6,250 1

Hash Left Join (cost=38.71..2,609.23 rows=6,130 width=21) (actual time=0.764..45.849 rows=6,250 loops=1)

  • Hash Cond: (rvm.makine_id = wdht.makine_id)
41. 4.116 41.056 ↓ 1.0 6,250 1

Hash Join (cost=36.40..2,523.48 rows=6,130 width=21) (actual time=0.379..41.056 rows=6,250 loops=1)

  • Hash Cond: (rvm.makine_id = m.makine_id)
42. 4.165 36.873 ↓ 1.0 6,250 1

Hash Join (cost=31.78..2,501.83 rows=6,130 width=21) (actual time=0.298..36.873 rows=6,250 loops=1)

  • Hash Cond: (v.vardiya_zaman_id = vz.vardiya_zaman_id)
43. 21.016 32.686 ↓ 1.0 6,250 1

Hash Join (cost=30.71..2,461.94 rows=6,130 width=25) (actual time=0.267..32.686 rows=6,250 loops=1)

  • Hash Cond: (rvm.vardiya_id = v.vardiya_id)
44. 11.446 11.446 ↑ 1.0 55,167 1

Seq Scan on randiman_vrd_makine rvm (cost=0.00..2,285.67 rows=55,167 width=25) (actual time=0.013..11.446 rows=55,167 loops=1)

45. 0.048 0.224 ↓ 1.0 93 1

Hash (cost=29.57..29.57 rows=91 width=12) (actual time=0.224..0.224 rows=93 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
46. 0.060 0.176 ↓ 1.0 93 1

Bitmap Heap Scan on vardiyalar v (cost=9.21..29.57 rows=91 width=12) (actual time=0.128..0.176 rows=93 loops=1)

  • Recheck Cond: ((tarih >= '2020-01-01'::date) AND (tarih <= '2020-01-31'::date))
  • Heap Blocks: exact=3
47. 0.116 0.116 ↓ 1.0 93 1

Bitmap Index Scan on "uc_vardiyalar_(tarih|uretim_merkez_id|vardiya_zaman_id)" (cost=0.00..9.19 rows=91 width=0) (actual time=0.116..0.116 rows=93 loops=1)

  • Index Cond: ((tarih >= '2020-01-01'::date) AND (tarih <= '2020-01-31'::date))
48. 0.013 0.022 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=4) (actual time=0.022..0.022 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 0.009 0.009 ↑ 1.0 3 1

Seq Scan on vardiya_zamanlari vz (cost=0.00..1.03 rows=3 width=4) (actual time=0.008..0.009 rows=3 loops=1)

50. 0.027 0.067 ↑ 1.0 72 1

Hash (cost=3.72..3.72 rows=72 width=4) (actual time=0.067..0.067 rows=72 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
51. 0.040 0.040 ↑ 1.0 72 1

Seq Scan on makineler m (cost=0.00..3.72 rows=72 width=4) (actual time=0.020..0.040 rows=72 loops=1)

52. 0.048 0.374 ↓ 1.0 72 1

Hash (cost=1.42..1.42 rows=71 width=4) (actual time=0.374..0.374 rows=72 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
53. 0.326 0.326 ↓ 1.0 72 1

CTE Scan on w_devir_hesap_tipleri wdht (cost=0.00..1.42 rows=71 width=4) (actual time=0.098..0.326 rows=72 loops=1)

54. 10.847 1,010.675 ↓ 485.6 22,337 1

Hash (cost=0.92..0.92 rows=46 width=8) (actual time=1,010.675..1,010.675 rows=22,337 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1129kB
55. 999.828 999.828 ↓ 485.6 22,337 1

CTE Scan on w_randiman_vrd_makine_personel wrvmp (cost=0.00..0.92 rows=46 width=8) (actual time=782.717..999.828 rows=22,337 loops=1)

Planning time : 9.315 ms
Execution time : 1,088.758 ms