explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8jTw

Settings
# exclusive inclusive rows x rows loops node
1. 0.452 158,491.983 ↓ 59.0 59 1

Sort (cost=6,584.02..6,584.02 rows=1 width=665) (actual time=158,491.886..158,491.983 rows=59 loops=1)

  • Sort Key: (COALESCE(((evndirection.evn_disdt)::date)::timestamp without time zone, ((evndirection.evn_setdt)::date)::timestamp without time zone)) DESC
  • Sort Method: quicksort Memory: 55kB
2. 1.434 158,491.531 ↓ 59.0 59 1

Nested Loop (cost=132.61..6,584.01 rows=1 width=665) (actual time=962.537..158,491.531 rows=59 loops=1)

3. 9.687 158,477.567 ↓ 70.0 70 1

Nested Loop (cost=132.47..6,583.80 rows=1 width=363) (actual time=962.364..158,477.567 rows=70 loops=1)

4. 41.812 158,467.049 ↓ 277.0 277 1

Nested Loop Left Join (cost=132.18..6,581.29 rows=1 width=363) (actual time=231.346..158,467.049 rows=277 loops=1)

  • Join Filter: (dirtype.dirtype_id = evndirection.dirtype_id)
  • Rows Removed by Join Filter: 6371
5. 36.302 158,383.410 ↓ 277.0 277 1

Nested Loop Left Join (cost=132.18..6,579.75 rows=1 width=315) (actual time=231.323..158,383.410 rows=277 loops=1)

6. 24.105 158,346.277 ↓ 277.0 277 1

Nested Loop Left Join (cost=132.04..6,577.36 rows=1 width=213) (actual time=231.302..158,346.277 rows=277 loops=1)

  • Filter: (((COALESCE(diag.diag_code, ''::character varying))::text <> ALL ('{R76.2,Z20.2,R75.}'::text[])) AND (((COALESCE(diag.diag_code, ''::character varying))::text < 'F00.0'::text) OR ((COALESCE(diag.diag_code, ''::character varying))::text > 'F99.'::text)) AND (((COALESCE(diag.diag_code, ''::character varying))::text < 'A50.0'::text) OR ((COALESCE(diag.diag_code, ''::character varying))::text > 'A66.1'::text)) AND (((COALESCE(diag.diag_code, ''::character varying))::text < 'B85.0'::text) OR ((COALESCE(diag.diag_code, ''::character varying))::text > 'B85.4'::text)) AND (((COALESCE(diag.diag_code, ''::character varying))::text < 'B20.0'::text) OR ((COALESCE(diag.diag_code, ''::character varying))::text > 'B24.'::text)))
7. 2.260 158,321.064 ↓ 277.0 277 1

Nested Loop (cost=131.75..6,574.82 rows=1 width=221) (actual time=231.275..158,321.064 rows=277 loops=1)

8. 1,506.302 158,159.806 ↓ 277.0 277 1

Nested Loop (cost=131.60..6,572.81 rows=1 width=139) (actual time=231.232..158,159.806 rows=277 loops=1)

  • Join Filter: (evndirection.lpu_id = lpu.lpu_id)
  • Rows Removed by Join Filter: 180604
9. 1.271 3.910 ↓ 277.0 277 1

Merge Join (cost=125.52..126.39 rows=1 width=95) (actual time=2.136..3.910 rows=277 loops=1)

  • Merge Cond: (evndirection.evnclass_id = evnclass.evnclass_id)
10. 1.178 2.593 ↓ 277.0 277 1

Sort (cost=125.38..125.38 rows=1 width=87) (actual time=2.079..2.593 rows=277 loops=1)

  • Sort Key: evndirection.evnclass_id
  • Sort Method: quicksort Memory: 63kB
11. 1.415 1.415 ↓ 277.0 277 1

Index Scan using idx_evndirection_person_id on evndirection (cost=0.57..125.37 rows=1 width=87) (actual time=0.034..1.415 rows=277 loops=1)

  • Index Cond: (person_id = '5398168'::bigint)
  • Filter: ((evndirection_faildt IS NULL) AND (dirfailtype_id IS NULL) AND ((evn_deleted = 1) OR (evn_deleted IS NULL)) AND (COALESCE(evnstatus_id, '0'::bigint) <> ALL ('{12,13}'::bigint[])) AND (COALESCE(evn_isarchive, '1'::bigint) = 1))
  • Rows Removed by Filter: 23
12. 0.046 0.046 ↑ 3.0 1 1

Index Only Scan using pk_evnclass_id on evnclass (cost=0.14..2.69 rows=3 width=8) (actual time=0.045..0.046 rows=1 loops=1)

  • Index Cond: (evnclass_id = ANY ('{27,117,49}'::bigint[]))
  • Heap Fetches: 0
13. 3,709.861 156,649.594 ↓ 1.2 653 277

Nested Loop Left Join (cost=6.08..6,439.42 rows=560 width=44) (actual time=0.842..565.522 rows=653 loops=277)

14. 5,492.910 137,383.967 ↓ 1.2 653 277

Nested Loop Left Join (cost=4.92..5,775.82 rows=560 width=44) (actual time=0.826..495.971 rows=653 loops=277)

15. 5,602.602 106,025.074 ↓ 1.2 653 277

Nested Loop Left Join (cost=1.43..3,807.26 rows=560 width=44) (actual time=0.696..382.762 rows=653 loops=277)

16. 4,763.015 86,494.635 ↓ 1.2 653 277

Nested Loop Left Join (cost=1.00..2,926.66 rows=560 width=52) (actual time=0.524..312.255 rows=653 loops=277)

17. 5,471.027 51,524.493 ↓ 1.2 653 277

Nested Loop Left Join (cost=0.56..2,046.06 rows=560 width=60) (actual time=0.052..186.009 rows=653 loops=277)

18. 5,118.406 35,200.606 ↓ 1.2 653 277

Nested Loop (cost=0.42..1,329.26 rows=560 width=68) (actual time=0.042..127.078 rows=653 loops=277)

19. 2,950.050 2,950.050 ↓ 1.2 653 277

Seq Scan on lpu (cost=0.00..38.16 rows=560 width=24) (actual time=0.032..10.650 rows=653 loops=277)

  • Filter: ((region_id = 59) OR (region_id IS NULL))
20. 27,132.150 27,132.150 ↑ 1.0 1 180,881

Index Scan using pk_org_id on org (cost=0.42..2.31 rows=1 width=60) (actual time=0.150..0.150 rows=1 loops=180,881)

  • Index Cond: (org_id = lpu.org_id)
21. 5,064.668 10,852.860 ↑ 1.0 1 180,881

Limit (cost=0.14..1.26 rows=1 width=358) (actual time=0.043..0.060 rows=1 loops=180,881)

22. 5,788.192 5,788.192 ↑ 1.0 1 180,881

Index Only Scan using pk_lputype_id on lputype lputype_1 (cost=0.14..1.26 rows=1 width=358) (actual time=0.026..0.032 rows=1 loops=180,881)

  • Index Cond: (lputype_id = lpu.lputype_id)
  • Heap Fetches: 0
23. 5,426.430 30,207.127 ↑ 1.0 1 180,881

Limit (cost=0.43..1.55 rows=1 width=1,324) (actual time=0.147..0.167 rows=1 loops=180,881)

24. 24,780.697 24,780.697 ↑ 1.0 1 180,881

Index Only Scan using pk_address_id on address au_1 (cost=0.43..1.55 rows=1 width=1,324) (actual time=0.132..0.137 rows=1 loops=180,881)

  • Index Cond: (address_id = org.uaddress_id)
  • Heap Fetches: 831
25. 5,788.192 13,927.837 ↑ 1.0 1 180,881

Limit (cost=0.43..1.55 rows=1 width=1,324) (actual time=0.055..0.077 rows=1 loops=180,881)

26. 8,139.645 8,139.645 ↑ 1.0 1 180,881

Index Only Scan using pk_address_id on address ap_1 (cost=0.43..1.55 rows=1 width=1,324) (actual time=0.036..0.045 rows=1 loops=180,881)

  • Index Cond: (address_id = org.paddress_id)
  • Heap Fetches: 432
27. 5,788.192 25,865.983 ↑ 1.0 1 180,881

Limit (cost=3.49..3.50 rows=1 width=158) (actual time=0.125..0.143 rows=1 loops=180,881)

28. 6,330.835 20,077.791 ↑ 1.0 1 180,881

Sort (cost=3.49..3.50 rows=1 width=158) (actual time=0.106..0.111 rows=1 loops=180,881)

  • Sort Key: lpuperiodoms_1.lpuperiodoms_begdate DESC
  • Sort Method: quicksort Memory: 25kB
29. 13,746.956 13,746.956 ↑ 1.0 1 180,881

Index Scan using idx_lpuperiodoms_lpu_id on lpuperiodoms lpuperiodoms_1 (cost=0.28..3.48 rows=1 width=158) (actual time=0.059..0.076 rows=1 loops=180,881)

  • Index Cond: (lpu_id = lpu.lpu_id)
  • Filter: ((lpuperiodoms_enddate IS NULL) OR (lpuperiodoms_enddate > (now())::timestamp without time zone))
  • Rows Removed by Filter: 1
30. 3,617.620 15,555.766 ↓ 0.0 0 180,881

Limit (cost=1.16..1.16 rows=1 width=82) (actual time=0.086..0.086 rows=0 loops=180,881)

31. 5,969.073 11,938.146 ↓ 0.0 0 180,881

Sort (cost=1.16..1.16 rows=1 width=82) (actual time=0.066..0.066 rows=0 loops=180,881)

  • Sort Key: lpuperioddms_1.lpuperioddms_begdate DESC
  • Sort Method: quicksort Memory: 25kB
32. 5,969.073 5,969.073 ↓ 0.0 0 180,881

Seq Scan on lpuperioddms lpuperioddms_1 (cost=0.00..1.15 rows=1 width=82) (actual time=0.033..0.033 rows=0 loops=180,881)

  • Filter: (lpu_id = lpu.lpu_id)
  • Rows Removed by Filter: 12
33. 158.998 158.998 ↑ 1.0 1 277

Index Scan using pk_evnclass_id on evnclass ec (cost=0.14..2.00 rows=1 width=98) (actual time=0.574..0.574 rows=1 loops=277)

  • Index Cond: (evnclass_id = evnclass.evnclass_id)
34. 1.108 1.108 ↓ 0.0 0 277

Index Scan using pk_diag_id on diag (cost=0.29..2.51 rows=1 width=13) (actual time=0.004..0.004 rows=0 loops=277)

  • Index Cond: (diag_id = evndirection.diag_id)
  • Filter: ((region_id = 59) OR (region_id IS NULL))
35. 0.831 0.831 ↓ 0.0 0 277

Index Scan using pk_sectionprofile_id on lpusectionprofile (cost=0.14..2.37 rows=1 width=118) (actual time=0.003..0.003 rows=0 loops=277)

  • Index Cond: (lpusectionprofile_id = evndirection.lpusectionprofile_id)
  • Filter: ((region_id = 59) OR (region_id IS NULL))
36. 41.827 41.827 ↑ 1.0 24 277

Seq Scan on v_dirtype dirtype (cost=0.00..1.24 rows=24 width=56) (actual time=0.003..0.151 rows=24 loops=277)

37. 0.831 0.831 ↓ 0.0 0 277

Index Scan using pk_medservice on medservice (cost=0.29..2.50 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=277)

  • Index Cond: (medservice_id = evndirection.medservice_id)
38. 12.530 12.530 ↑ 1.0 1 70

Index Scan using pk_medservicetype on medservicetype (cost=0.14..0.16 rows=1 width=8) (actual time=0.179..0.179 rows=1 loops=70)

  • Index Cond: (medservicetype_id = medservice.medservicetype_id)
  • Filter: ((medservicetype_sysnick)::text = ANY ('{lab,pzm}'::text[]))