explain.depesz.com

PostgreSQL's explain analyze made readable

Result: U8Bw

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

Limit (cost=65,349.84..65,349.87 rows=11 width=10,762) (actual time=4,894.667..4,894.668 rows=11 loops=1)

  • Buffers: shared hit=9544135 read=25922
2. 198.102 4,894.666 ↑ 181.9 11 1

Sort (cost=65,349.84..65,354.84 rows=2,001 width=10,762) (actual time=4,894.665..4,894.666 rows=11 loops=1)

  • Sort Key: ((((md.code)::text || ' '::text) || (md.name)::text))
  • Sort Method: top-N heapsort Memory: 47kB
  • Buffers: shared hit=9544135 read=25922
3. 51.345 4,696.564 ↓ 19.7 39,430 1

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

  • Buffers: shared hit=9544135 read=25922
4. 32.991 4,605.789 ↓ 19.7 39,430 1

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

  • Buffers: shared hit=9487347 read=25843
5. 43.658 4,533.368 ↓ 19.7 39,430 1

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

  • Hash Cond: (ssr.id = ssr1.service_id)
  • Buffers: shared hit=9444700 read=25842
6. 53.313 4,489.431 ↓ 19.7 39,386 1

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

  • Join Filter: (ssr.duration_measure_unit_id = ssdu.id)
  • Rows Removed by Join Filter: 118002
  • Buffers: shared hit=9444696 read=25841
7. 52.512 4,436.118 ↓ 19.7 39,386 1

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

  • Buffers: shared hit=9444696 read=25836
8. 93.517 4,344.220 ↓ 19.7 39,386 1

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

  • Buffers: shared hit=9355598 read=25757
9. 39.641 4,211.317 ↓ 19.7 39,386 1

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

  • Hash Cond: (rss.type_id = stv.id)
  • Buffers: shared hit=9279808 read=25757
10. 37.215 4,171.668 ↓ 19.7 39,386 1

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

  • Hash Cond: (srg.responsible_id = pep.id)
  • Buffers: shared hit=9279807 read=25757
11. 34.810 4,133.737 ↓ 19.7 39,386 1

Hash Left Join (cost=66.20..50,781.78 rows=2,001 width=5,689) (actual time=0.713..4,133.737 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=9279805 read=25734
12. 37.053 4,098.765 ↓ 9.8 39,391 1

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

  • Buffers: shared hit=9279803 read=25731
13. 36.939 4,061.712 ↓ 9.8 39,391 1

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

  • Buffers: shared hit=9279167 read=25731
14. 32.994 4,024.773 ↓ 9.8 39,391 1

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

  • Hash Cond: (ss.type_id = st.id)
  • Buffers: shared hit=9278531 read=25731
15. 28.838 3,991.745 ↓ 9.8 39,391 1

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

  • Buffers: shared hit=9278531 read=25730
16. 21.530 3,884.125 ↓ 9.8 39,391 1

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

  • Hash Cond: (srg.responsible_id = epr.employee_position_id)
  • Buffers: shared hit=9160101 read=25714
17. 35.726 3,862.224 ↓ 9.8 39,391 1

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

  • Merge Cond: (msr.step_id = ms.id)
  • Buffers: shared hit=9160099 read=25707
18. 23.809 3,798.951 ↑ 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.069..3,798.951 rows=39,392 loops=1)

  • Buffers: shared hit=9130909 read=25584
19. 31.942 3,696.358 ↑ 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.059..3,696.358 rows=39,392 loops=1)

  • Buffers: shared hit=8973637 read=25445
20. 859.899 3,585.632 ↑ 4.6 39,392 1

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

  • Buffers: shared hit=8821880 read=25065
21. 621.623 621.623 ↑ 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.033..621.623 rows=2,104,110 loops=1)

  • Buffers: shared hit=394084 read=24931
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=8427796 read=134
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=151757 read=380
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=157272 read=139
25. 27.547 27.547 ↓ 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.007..27.547 rows=49,479 loops=1)

  • Buffers: shared hit=29190 read=123
26. 0.198 0.371 ↑ 1.0 1,285 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 90kB
  • Buffers: shared hit=2 read=7
27. 0.173 0.173 ↑ 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.007..0.173 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.002..0.002 rows=1 loops=39,391)

  • Index Cond: (ms.case_id = id)
  • Buffers: shared hit=118430 read=16
29. 0.008 0.034 ↑ 1.0 19 1

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

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

Seq Scan on sr_srv_type st (cost=0.00..1.19 rows=19 width=1,366) (actual time=0.024..0.026 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.090 0.162 ↑ 1.0 503 1

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

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

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

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 231kB
  • Buffers: shared hit=2 read=23
36. 0.358 0.358 ↑ 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.006..0.358 rows=2,400 loops=1)

  • Buffers: shared hit=2 read=23
37. 0.005 0.008 ↑ 1.0 19 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
38. 0.003 0.003 ↑ 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.003 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=89098 read=79
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.066 0.141 ↑ 1.0 3 1

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

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

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

  • Buffers: shared read=4
44. 0.006 0.022 ↑ 1.0 3 1

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

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

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

  • Buffers: shared read=1
46. 0.116 0.279 ↑ 1.0 223 1

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

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

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

  • Hash Cond: (ssr1.result_type_id = ssrt.id)
  • Buffers: shared hit=4 read=1
48. 0.035 0.035 ↑ 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.035 rows=223 loops=1)

  • Buffers: shared hit=3 read=1
49. 0.017 0.024 ↑ 1.0 61 1

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

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

Seq Scan on sr_srv_result_type ssrt (cost=0.00..1.61 rows=61 width=92) (actual time=0.003..0.007 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=56788 read=79
Planning time : 68.280 ms
Execution time : 4,898.350 ms