explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iTJW

Settings
# exclusive inclusive rows x rows loops node
1. 192.558 4,439.929 ↓ 19.7 39,430 1

Sort (cost=65,929.31..65,934.32 rows=2,001 width=10,762) (actual time=4,439.134..4,439.929 rows=39,430 loops=1)

  • Sort Key: (upper((((md.code)::text || ' '::text) || (md.name)::text)))
  • Sort Method: quicksort Memory: 52335kB
  • Buffers: shared hit=9586716 read=25857
2. 67.224 4,247.371 ↓ 19.7 39,430 1

Nested Loop Left Join (cost=84.42..65,819.59 rows=2,001 width=10,762) (actual time=0.798..4,247.371 rows=39,430 loops=1)

  • Buffers: shared hit=9586716 read=25857
3. 37.823 4,101.287 ↓ 19.7 39,430 1

Hash Left Join (cost=83.71..52,534.71 rows=2,001 width=10,338) (actual time=0.783..4,101.287 rows=39,430 loops=1)

  • Hash Cond: (ssr.id = ssr1.service_id)
  • Buffers: shared hit=9487281 read=25777
4. 47.446 4,063.275 ↓ 19.7 39,386 1

Nested Loop Left Join (cost=69.40..52,487.88 rows=2,001 width=10,166) (actual time=0.587..4,063.275 rows=39,386 loops=1)

  • Join Filter: (ssr.duration_measure_unit_id = ssdu.id)
  • Rows Removed by Join Filter: 118002
  • Buffers: shared hit=9487277 read=25776
5. 33.184 4,015.829 ↓ 19.7 39,386 1

Hash Left Join (cost=68.34..52,388.93 rows=2,001 width=9,475) (actual time=0.515..4,015.829 rows=39,386 loops=1)

  • Hash Cond: (rss.type_id = stv.id)
  • Buffers: shared hit=9487277 read=25771
6. 20.353 3,982.638 ↓ 19.7 39,386 1

Nested Loop Left Join (cost=66.91..52,359.99 rows=2,001 width=8,109) (actual time=0.481..3,982.638 rows=39,386 loops=1)

  • Buffers: shared hit=9487276 read=25771
7. 33.953 3,922.899 ↓ 19.7 39,386 1

Nested Loop Left Join (cost=66.63..51,744.57 rows=2,001 width=8,008) (actual time=0.475..3,922.899 rows=39,386 loops=1)

  • Buffers: shared hit=9444735 read=25771
8. 37.799 3,849.560 ↓ 19.7 39,386 1

Nested Loop Left Join (cost=66.49..51,409.28 rows=2,001 width=6,062) (actual time=0.469..3,849.560 rows=39,386 loops=1)

  • Buffers: shared hit=9368945 read=25771
9. 29.956 3,772.375 ↓ 19.7 39,386 1

Hash Left Join (cost=66.20..50,781.78 rows=2,001 width=5,689) (actual time=0.460..3,772.375 rows=39,386 loops=1)

  • Hash Cond: (rss.id = sc.complex_id)
  • Filter: CASE WHEN (ssr.complex_srv_id IS NOT NULL) THEN (sc.service_id = ss.id) ELSE true END
  • Rows Removed by Filter: 260
  • Buffers: shared hit=9279864 read=25675
10. 35.669 3,742.319 ↓ 9.8 39,391 1

Nested Loop Left Join (cost=49.89..50,700.39 rows=4,002 width=5,597) (actual time=0.350..3,742.319 rows=39,391 loops=1)

  • Buffers: shared hit=9279862 read=25672
11. 31.604 3,706.650 ↓ 9.8 39,391 1

Nested Loop Left Join (cost=49.46..48,744.96 rows=4,002 width=4,830) (actual time=0.348..3,706.650 rows=39,391 loops=1)

  • Buffers: shared hit=9279226 read=25672
12. 29.372 3,675.046 ↓ 9.8 39,391 1

Hash Left Join (cost=49.03..43,520.50 rows=4,002 width=4,670) (actual time=0.346..3,675.046 rows=39,391 loops=1)

  • Hash Cond: (ss.type_id = st.id)
  • Buffers: shared hit=9278590 read=25672
13. 55.798 3,645.660 ↓ 9.8 39,391 1

Nested Loop Left Join (cost=47.60..43,464.05 rows=4,002 width=3,304) (actual time=0.327..3,645.660 rows=39,391 loops=1)

  • Buffers: shared hit=9278590 read=25671
14. 20.211 3,550.471 ↓ 9.8 39,391 1

Hash Left Join (cost=47.31..41,905.78 rows=4,002 width=2,420) (actual time=0.319..3,550.471 rows=39,391 loops=1)

  • Hash Cond: (srg.responsible_id = epr.employee_position_id)
  • Buffers: shared hit=9160161 read=25654
15. 31.510 3,530.011 ↓ 9.8 39,391 1

Merge Join (cost=9.38..41,813.74 rows=4,002 width=2,364) (actual time=0.060..3,530.011 rows=39,391 loops=1)

  • Merge Cond: (msr.step_id = ms.id)
  • Buffers: shared hit=9160159 read=25647
16. 11.365 3,475.262 ↑ 4.6 39,392 1

Nested Loop Left Join (cost=1.72..1,767,503.02 rows=182,998 width=2,186) (actual time=0.041..3,475.262 rows=39,392 loops=1)

  • Buffers: shared hit=9130969 read=25524
17. 12.942 3,385.113 ↑ 4.6 39,392 1

Nested Loop Left Join (cost=1.29..1,599,641.90 rows=182,998 width=1,419) (actual time=0.028..3,385.113 rows=39,392 loops=1)

  • Buffers: shared hit=8973697 read=25385
18. 721.888 3,293.387 ↑ 4.6 39,392 1

Nested Loop (cost=0.86..1,343,161.83 rows=182,998 width=777) (actual time=0.021..3,293.387 rows=39,392 loops=1)

  • Buffers: shared hit=8821940 read=25005
19. 467.389 467.389 ↑ 1.1 2,104,110 1

Index Scan using md_srv_rendered_step_id_idx on md_srv_rendered msr (cost=0.43..101,906.06 rows=2,355,182 width=617) (actual time=0.011..467.389 rows=2,104,110 loops=1)

  • Buffers: shared hit=394145 read=24870
20. 2,104.110 2,104.110 ↓ 0.0 0 2,104,110

Index Scan using sr_srv_rendered_pkey on sr_srv_rendered ssr (cost=0.43..0.52 rows=1 width=160) (actual time=0.001..0.001 rows=0 loops=2,104,110)

  • Index Cond: (id = msr.id)
  • Filter: is_rendered
  • Rows Removed by Filter: 1
  • Buffers: shared hit=8427795 read=135
21. 78.784 78.784 ↑ 1.0 1 39,392

Index Scan using sr_res_group_pkey on sr_res_group srg (cost=0.43..1.39 rows=1 width=642) (actual time=0.002..0.002 rows=1 loops=39,392)

  • Index Cond: (id = ssr.res_group_id)
  • Buffers: shared hit=151757 read=380
22. 78.784 78.784 ↑ 1.0 1 39,392

Index Scan using sr_service_service_id on sr_service ss (cost=0.42..0.91 rows=1 width=767) (actual time=0.002..0.002 rows=1 loops=39,392)

  • Index Cond: (ssr.service_id = id)
  • Buffers: shared hit=157272 read=139
23. 23.239 23.239 ↓ 1.1 49,479 1

Index Scan using mc_step_pkey on mc_step ms (cost=0.29..3,023.52 rows=45,525 width=178) (actual time=0.013..23.239 rows=49,479 loops=1)

  • Buffers: shared hit=29190 read=123
24. 0.137 0.249 ↑ 1.0 1,285 1

Hash (cost=21.86..21.86 rows=1,286 width=56) (actual time=0.249..0.249 rows=1,285 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 90kB
  • Buffers: shared hit=2 read=7
25. 0.112 0.112 ↑ 1.0 1,286 1

Seq Scan on pim_employee_position_resource epr (cost=0.00..21.86 rows=1,286 width=56) (actual time=0.005..0.112 rows=1,286 loops=1)

  • Buffers: shared hit=2 read=7
26. 39.391 39.391 ↑ 1.0 1 39,391

Index Scan using mc_case_pkey on mc_case mc (cost=0.29..0.38 rows=1 width=884) (actual time=0.001..0.001 rows=1 loops=39,391)

  • Index Cond: (ms.case_id = id)
  • Buffers: shared hit=118429 read=17
27. 0.004 0.014 ↑ 1.0 19 1

Hash (cost=1.19..1.19 rows=19 width=1,366) (actual time=0.014..0.014 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared read=1
28. 0.010 0.010 ↑ 1.0 19 1

Seq Scan on sr_srv_type st (cost=0.00..1.19 rows=19 width=1,366) (actual time=0.008..0.010 rows=19 loops=1)

  • Buffers: shared read=1
29. 0.000 0.000 ↓ 0.0 0 39,391

Index Scan using sr_srv_rendered_pkey on sr_srv_rendered srsr (cost=0.43..1.30 rows=1 width=160) (actual time=0.000..0.000 rows=0 loops=39,391)

  • Index Cond: (id = ssr.complex_srv_id)
  • Buffers: shared hit=636
30. 0.000 0.000 ↓ 0.0 0 39,391

Index Scan using sr_service_service_id on sr_service rss (cost=0.42..0.48 rows=1 width=767) (actual time=0.000..0.000 rows=0 loops=39,391)

  • Index Cond: (id = srsr.service_id)
  • Buffers: shared hit=636
31. 0.056 0.100 ↑ 1.0 503 1

Hash (cost=10.03..10.03 rows=503 width=92) (actual time=0.100..0.100 rows=503 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
  • Buffers: shared hit=2 read=3
32. 0.044 0.044 ↑ 1.0 503 1

Seq Scan on sr_srv_composition sc (cost=0.00..10.03 rows=503 width=92) (actual time=0.003..0.044 rows=503 loops=1)

  • Buffers: shared hit=2 read=3
33. 39.386 39.386 ↑ 1.0 1 39,386

Index Scan using md_diagnosis_pkey on md_diagnosis md (cost=0.29..0.30 rows=1 width=373) (actual time=0.001..0.001 rows=1 loops=39,386)

  • Index Cond: (msr.diagnosis_id = id)
  • Buffers: shared hit=89081 read=96
34. 39.386 39.386 ↑ 1.0 1 39,386

Index Scan using fin_funding_source_type_pkey on fin_funding_source_type ffst (cost=0.14..0.16 rows=1 width=1,946) (actual time=0.001..0.001 rows=1 loops=39,386)

  • Index Cond: (ssr.funding_id = id)
  • Buffers: shared hit=75790
35. 39.386 39.386 ↓ 0.0 0 39,386

Index Scan using pim_employee_position_pkey on pim_employee_position pep (cost=0.28..0.30 rows=1 width=101) (actual time=0.000..0.001 rows=0 loops=39,386)

  • Index Cond: (srg.responsible_id = id)
  • Buffers: shared hit=42541
36. 0.005 0.007 ↑ 1.0 19 1

Hash (cost=1.19..1.19 rows=19 width=1,366) (actual time=0.007..0.007 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
37. 0.002 0.002 ↑ 1.0 19 1

Seq Scan on sr_srv_type stv (cost=0.00..1.19 rows=19 width=1,366) (actual time=0.002..0.002 rows=19 loops=1)

  • Buffers: shared hit=1
38. 0.000 0.000 ↑ 1.0 3 39,386

Materialize (cost=1.07..8.91 rows=3 width=691) (actual time=0.000..0.000 rows=3 loops=39,386)

  • Buffers: shared read=5
39. 0.023 0.065 ↑ 1.0 3 1

Hash Right Join (cost=1.07..8.89 rows=3 width=691) (actual time=0.031..0.065 rows=3 loops=1)

  • Hash Cond: (cm.id = ssdu.measure_id)
  • Buffers: shared read=5
40. 0.030 0.030 ↑ 1.0 276 1

Seq Scan on cmn_measure cm (cost=0.00..6.76 rows=276 width=539) (actual time=0.008..0.030 rows=276 loops=1)

  • Buffers: shared read=4
41. 0.004 0.012 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=152) (actual time=0.012..0.012 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
42. 0.008 0.008 ↑ 1.0 3 1

Seq Scan on sr_srv_duration_unit ssdu (cost=0.00..1.03 rows=3 width=152) (actual time=0.008..0.008 rows=3 loops=1)

  • Buffers: shared read=1
43. 0.086 0.189 ↑ 1.0 223 1

Hash (cost=11.52..11.52 rows=223 width=172) (actual time=0.189..0.189 rows=223 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
  • Buffers: shared hit=4 read=1
44. 0.069 0.103 ↑ 1.0 223 1

Hash Left Join (cost=2.37..11.52 rows=223 width=172) (actual time=0.023..0.103 rows=223 loops=1)

  • Hash Cond: (ssr1.result_type_id = ssrt.id)
  • Buffers: shared hit=4 read=1
45. 0.019 0.019 ↑ 1.0 223 1

Seq Scan on sr_srv_result ssr1 (cost=0.00..6.23 rows=223 width=80) (actual time=0.002..0.019 rows=223 loops=1)

  • Buffers: shared hit=3 read=1
46. 0.010 0.015 ↑ 1.0 61 1

Hash (cost=1.61..1.61 rows=61 width=92) (actual time=0.015..0.015 rows=61 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
  • Buffers: shared hit=1
47. 0.005 0.005 ↑ 1.0 61 1

Seq Scan on sr_srv_result_type ssrt (cost=0.00..1.61 rows=61 width=92) (actual time=0.002..0.005 rows=61 loops=1)

  • Buffers: shared hit=1
48. 11.000 78.860 ↓ 0.0 0 39,430

Nested Loop Left Join (cost=0.71..6.62 rows=1 width=424) (actual time=0.001..0.002 rows=0 loops=39,430)

  • Buffers: shared hit=99435 read=80
49. 39.430 39.430 ↓ 0.0 0 39,430

Index Scan using pim_employee_pkey on pim_employee pe (cost=0.29..0.49 rows=1 width=90) (actual time=0.001..0.001 rows=0 loops=39,430)

  • Index Cond: (pep.employee_id = id)
  • Buffers: shared hit=42647 read=1
50. 28.430 28.430 ↑ 1.0 1 14,215

Index Scan using pim_individual_pkey on pim_individual pi (cost=0.43..6.12 rows=1 width=334) (actual time=0.002..0.002 rows=1 loops=14,215)

  • Index Cond: (pe.individual_id = id)
  • Buffers: shared hit=56788 read=79
Planning time : 17.285 ms
Execution time : 4,522.712 ms