explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JGk

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 4,645.185 ↑ 1.0 11 1

Limit (cost=65,354.84..65,354.87 rows=11 width=10,762) (actual time=4,645.185..4,645.185 rows=11 loops=1)

  • Buffers: shared hit=9544017 read=26040
2. 181.069 4,645.183 ↑ 181.9 11 1

Sort (cost=65,354.84..65,359.84 rows=2,001 width=10,762) (actual time=4,645.182..4,645.183 rows=11 loops=1)

  • Sort Key: (upper((((md.code)::text || ' '::text) || (md.name)::text)))
  • Sort Method: top-N heapsort Memory: 47kB
  • Buffers: shared hit=9544017 read=26040
3. 72.807 4,464.114 ↓ 19.7 39,430 1

Nested Loop Left Join (cost=163.14..65,310.23 rows=2,001 width=10,762) (actual time=1.374..4,464.114 rows=39,430 loops=1)

  • Buffers: shared hit=9544017 read=26040
4. 27.090 4,351.877 ↓ 19.7 39,430 1

Nested Loop Left Join (cost=162.71..53,020.84 rows=2,001 width=10,428) (actual time=1.363..4,351.877 rows=39,430 loops=1)

  • Buffers: shared hit=9487248 read=25942
5. 42.523 4,285.357 ↓ 19.7 39,430 1

Hash Left Join (cost=162.43..52,025.34 rows=2,001 width=10,338) (actual time=1.353..4,285.357 rows=39,430 loops=1)

  • Hash Cond: (ssr.id = ssr1.service_id)
  • Buffers: shared hit=9444601 read=25941
6. 48.799 4,242.616 ↓ 19.7 39,386 1

Nested Loop Left Join (cost=148.12..51,978.51 rows=2,001 width=10,166) (actual time=1.127..4,242.616 rows=39,386 loops=1)

  • Join Filter: (ssr.duration_measure_unit_id = ssdu.id)
  • Rows Removed by Join Filter: 118002
  • Buffers: shared hit=9444597 read=25940
7. 45.596 4,193.817 ↓ 19.7 39,386 1

Nested Loop Left Join (cost=147.06..51,879.57 rows=2,001 width=9,475) (actual time=1.041..4,193.817 rows=39,386 loops=1)

  • Buffers: shared hit=9444597 read=25935
8. 40.209 4,108.835 ↓ 19.7 39,386 1

Nested Loop Left Join (cost=146.77..51,252.07 rows=2,001 width=9,102) (actual time=1.038..4,108.835 rows=39,386 loops=1)

  • Buffers: shared hit=9355504 read=25851
9. 31.465 4,029.240 ↓ 19.7 39,386 1

Hash Left Join (cost=146.63..50,916.78 rows=2,001 width=7,156) (actual time=1.030..4,029.240 rows=39,386 loops=1)

  • Hash Cond: (rss.type_id = stv.id)
  • Buffers: shared hit=9279714 read=25851
10. 42.331 3,997.766 ↓ 19.7 39,386 1

Hash Left Join (cost=145.20..50,887.83 rows=2,001 width=5,790) (actual time=0.991..3,997.766 rows=39,386 loops=1)

  • Hash Cond: (srg.responsible_id = pep.id)
  • Buffers: shared hit=9279713 read=25851
11. 31.880 3,954.938 ↓ 19.7 39,386 1

Hash Left Join (cost=66.20..50,781.78 rows=2,001 width=5,689) (actual time=0.476..3,954.938 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=9279691 read=25848
12. 35.395 3,922.935 ↓ 9.8 39,391 1

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

  • Buffers: shared hit=9279689 read=25845
13. 38.435 3,887.540 ↓ 9.8 39,391 1

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

  • Buffers: shared hit=9279053 read=25845
14. 30.820 3,849.105 ↓ 9.8 39,391 1

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

  • Hash Cond: (ss.type_id = st.id)
  • Buffers: shared hit=9278417 read=25845
15. 22.355 3,818.267 ↓ 9.8 39,391 1

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

  • Buffers: shared hit=9278417 read=25844
16. 20.204 3,717.130 ↓ 9.8 39,391 1

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

  • Hash Cond: (srg.responsible_id = epr.employee_position_id)
  • Buffers: shared hit=9159991 read=25824
17. 33.378 3,696.679 ↓ 9.8 39,391 1

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

  • Merge Cond: (msr.step_id = ms.id)
  • Buffers: shared hit=9159989 read=25817
18. 17.067 3,638.065 ↑ 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.044..3,638.065 rows=39,392 loops=1)

  • Buffers: shared hit=9130800 read=25693
19. 18.873 3,542.214 ↑ 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.036..3,542.214 rows=39,392 loops=1)

  • Buffers: shared hit=8973560 read=25522
20. 843.826 3,444.557 ↑ 4.6 39,392 1

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

  • Buffers: shared hit=8821849 read=25096
21. 496.621 496.621 ↑ 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.014..496.621 rows=2,104,110 loops=1)

  • Buffers: shared hit=394080 read=24935
22. 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=8427769 read=161
23. 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=151711 read=426
24. 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=157240 read=171
25. 25.236 25.236 ↓ 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.005..25.236 rows=49,479 loops=1)

  • Buffers: shared hit=29189 read=124
26. 0.130 0.247 ↑ 1.0 1,285 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 90kB
  • Buffers: shared hit=2 read=7
27. 0.117 0.117 ↑ 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.006..0.117 rows=1,286 loops=1)

  • Buffers: shared hit=2 read=7
28. 78.782 78.782 ↑ 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.002 rows=1 loops=39,391)

  • Index Cond: (ms.case_id = id)
  • Buffers: shared hit=118426 read=20
29. 0.007 0.018 ↑ 1.0 19 1

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

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

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

  • Buffers: shared read=1
31. 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
32. 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
33. 0.060 0.123 ↑ 1.0 503 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
  • Buffers: shared hit=2 read=3
34. 0.063 0.063 ↑ 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.063 rows=503 loops=1)

  • Buffers: shared hit=2 read=3
35. 0.287 0.497 ↑ 1.0 2,400 1

Hash (cost=49.00..49.00 rows=2,400 width=101) (actual time=0.497..0.497 rows=2,400 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 231kB
  • Buffers: shared hit=22 read=3
36. 0.210 0.210 ↑ 1.0 2,400 1

Seq Scan on pim_employee_position pep (cost=0.00..49.00 rows=2,400 width=101) (actual time=0.005..0.210 rows=2,400 loops=1)

  • Buffers: shared hit=22 read=3
37. 0.004 0.009 ↑ 1.0 19 1

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

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

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

  • Buffers: shared hit=1
39. 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
40. 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=89093 read=84
41. 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
42. 0.022 0.077 ↑ 1.0 3 1

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

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

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

  • Buffers: shared read=4
44. 0.004 0.015 ↑ 1.0 3 1

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

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

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

  • Buffers: shared read=1
46. 0.091 0.218 ↑ 1.0 223 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
  • Buffers: shared hit=4 read=1
47. 0.073 0.127 ↑ 1.0 223 1

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

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

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

  • Buffers: shared hit=3 read=1
49. 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
50. 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
51. 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
52. 39.430 39.430 ↓ 0.0 0 39,430

Index Scan using pim_individual_pkey on pim_individual pi (cost=0.43..6.12 rows=1 width=334) (actual time=0.001..0.001 rows=0 loops=39,430)

  • Index Cond: (pe.individual_id = id)
  • Buffers: shared hit=56769 read=98
Planning time : 46.501 ms
Execution time : 4,649.014 ms