explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Grt4

Settings
# exclusive inclusive rows x rows loops node
1. 0.102 2,909.191 ↓ 16.0 16 1

Sort (cost=6,581.72..6,581.72 rows=1 width=665) (actual time=2,909.171..2,909.191 rows=16 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: 33kB
2. 0.427 2,909.089 ↓ 16.0 16 1

Nested Loop (cost=130.31..6,581.71 rows=1 width=665) (actual time=96.577..2,909.089 rows=16 loops=1)

3. 0.329 2,908.566 ↓ 16.0 16 1

Nested Loop (cost=130.17..6,581.50 rows=1 width=363) (actual time=96.536..2,908.566 rows=16 loops=1)

4. 1.970 2,908.081 ↓ 52.0 52 1

Nested Loop Left Join (cost=129.89..6,578.99 rows=1 width=363) (actual time=40.912..2,908.081 rows=52 loops=1)

  • Join Filter: (dirtype.dirtype_id = evndirection.dirtype_id)
  • Rows Removed by Join Filter: 1196
5. 0.402 2,904.447 ↓ 52.0 52 1

Nested Loop Left Join (cost=129.89..6,577.45 rows=1 width=315) (actual time=40.891..2,904.447 rows=52 loops=1)

6. 0.718 2,903.785 ↓ 52.0 52 1

Nested Loop Left Join (cost=129.74..6,575.06 rows=1 width=213) (actual time=40.881..2,903.785 rows=52 loops=1)

  • Filter: (((COALESCE(diag.diag_code, ''::character varying))::text <> ALL ('{R76.2,Z20.2}'::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)))
7. 0.437 2,902.859 ↓ 52.0 52 1

Nested Loop (cost=129.45..6,572.53 rows=1 width=221) (actual time=40.862..2,902.859 rows=52 loops=1)

8. 39.531 2,902.110 ↓ 52.0 52 1

Nested Loop (cost=129.31..6,570.51 rows=1 width=139) (actual time=40.846..2,902.110 rows=52 loops=1)

  • Join Filter: (evndirection.lpu_id = lpu.lpu_id)
  • Rows Removed by Join Filter: 33904
9. 0.264 0.655 ↓ 52.0 52 1

Merge Join (cost=123.23..124.09 rows=1 width=95) (actual time=0.294..0.655 rows=52 loops=1)

  • Merge Cond: (evndirection.evnclass_id = evnclass.evnclass_id)
10. 0.185 0.381 ↓ 52.0 52 1

Sort (cost=123.08..123.09 rows=1 width=87) (actual time=0.279..0.381 rows=52 loops=1)

  • Sort Key: evndirection.evnclass_id
  • Sort Method: quicksort Memory: 32kB
11. 0.196 0.196 ↓ 52.0 52 1

Index Scan using idx_evndirection_person_id on evndirection (cost=0.57..123.07 rows=1 width=87) (actual time=0.022..0.196 rows=52 loops=1)

  • Index Cond: (person_id = '6188943'::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: 1
12. 0.010 0.010 ↑ 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.009..0.010 rows=1 loops=1)

  • Index Cond: (evnclass_id = ANY ('{27,117,49}'::bigint[]))
  • Heap Fetches: 0
13. 132.704 2,861.924 ↓ 1.2 653 52

Nested Loop Left Join (cost=6.08..6,439.42 rows=560 width=44) (actual time=0.073..55.037 rows=653 loops=52)

14. 152.568 2,389.660 ↓ 1.2 653 52

Nested Loop Left Join (cost=4.92..5,775.82 rows=560 width=44) (actual time=0.059..45.955 rows=653 loops=52)

15. 150.228 1,727.752 ↓ 1.2 653 52

Nested Loop Left Join (cost=1.43..3,807.26 rows=560 width=44) (actual time=0.044..33.226 rows=653 loops=52)

16. 168.792 1,271.920 ↓ 1.2 653 52

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

17. 162.864 763.568 ↓ 1.2 653 52

Nested Loop Left Join (cost=0.56..2,046.06 rows=560 width=60) (actual time=0.023..14.684 rows=653 loops=52)

18. 132.652 363.012 ↓ 1.2 653 52

Nested Loop (cost=0.42..1,329.26 rows=560 width=68) (actual time=0.014..6.981 rows=653 loops=52)

19. 60.580 60.580 ↓ 1.2 653 52

Seq Scan on lpu (cost=0.00..38.16 rows=560 width=24) (actual time=0.005..1.165 rows=653 loops=52)

  • Filter: ((region_id = 59) OR (region_id IS NULL))
20. 169.780 169.780 ↑ 1.0 1 33,956

Index Scan using pk_org_id on org (cost=0.42..2.31 rows=1 width=60) (actual time=0.005..0.005 rows=1 loops=33,956)

  • Index Cond: (org_id = lpu.org_id)
21. 135.824 237.692 ↑ 1.0 1 33,956

Limit (cost=0.14..1.26 rows=1 width=358) (actual time=0.005..0.007 rows=1 loops=33,956)

22. 101.868 101.868 ↑ 1.0 1 33,956

Index Only Scan using pk_lputype_id on lputype lputype_1 (cost=0.14..1.26 rows=1 width=358) (actual time=0.002..0.003 rows=1 loops=33,956)

  • Index Cond: (lputype_id = lpu.lputype_id)
  • Heap Fetches: 0
23. 135.824 339.560 ↑ 1.0 1 33,956

Limit (cost=0.43..1.55 rows=1 width=1,324) (actual time=0.008..0.010 rows=1 loops=33,956)

24. 203.736 203.736 ↑ 1.0 1 33,956

Index Only Scan using pk_address_id on address au_1 (cost=0.43..1.55 rows=1 width=1,324) (actual time=0.005..0.006 rows=1 loops=33,956)

  • Index Cond: (address_id = org.uaddress_id)
  • Heap Fetches: 416
25. 169.780 305.604 ↑ 1.0 1 33,956

Limit (cost=0.43..1.55 rows=1 width=1,324) (actual time=0.006..0.009 rows=1 loops=33,956)

26. 135.824 135.824 ↑ 1.0 1 33,956

Index Only Scan using pk_address_id on address ap_1 (cost=0.43..1.55 rows=1 width=1,324) (actual time=0.003..0.004 rows=1 loops=33,956)

  • Index Cond: (address_id = org.paddress_id)
  • Heap Fetches: 364
27. 135.824 509.340 ↑ 1.0 1 33,956

Limit (cost=3.49..3.50 rows=1 width=158) (actual time=0.013..0.015 rows=1 loops=33,956)

28. 169.780 373.516 ↑ 1.0 1 33,956

Sort (cost=3.49..3.50 rows=1 width=158) (actual time=0.010..0.011 rows=1 loops=33,956)

  • Sort Key: lpuperiodoms_1.lpuperiodoms_begdate DESC
  • Sort Method: quicksort Memory: 25kB
29. 203.736 203.736 ↑ 1.0 1 33,956

Index Scan using idx_lpuperiodoms_lpu_id on lpuperiodoms lpuperiodoms_1 (cost=0.28..3.48 rows=1 width=158) (actual time=0.004..0.006 rows=1 loops=33,956)

  • 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. 67.912 339.560 ↓ 0.0 0 33,956

Limit (cost=1.16..1.16 rows=1 width=82) (actual time=0.010..0.010 rows=0 loops=33,956)

31. 169.780 271.648 ↓ 0.0 0 33,956

Sort (cost=1.16..1.16 rows=1 width=82) (actual time=0.008..0.008 rows=0 loops=33,956)

  • Sort Key: lpuperioddms_1.lpuperioddms_begdate DESC
  • Sort Method: quicksort Memory: 25kB
32. 101.868 101.868 ↓ 0.0 0 33,956

Seq Scan on lpuperioddms lpuperioddms_1 (cost=0.00..1.15 rows=1 width=82) (actual time=0.003..0.003 rows=0 loops=33,956)

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

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

  • Index Cond: (evnclass_id = evnclass.evnclass_id)
34. 0.208 0.208 ↓ 0.0 0 52

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=52)

  • Index Cond: (diag_id = evndirection.diag_id)
  • Filter: ((region_id = 59) OR (region_id IS NULL))
35. 0.260 0.260 ↑ 1.0 1 52

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

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

Seq Scan on v_dirtype dirtype (cost=0.00..1.24 rows=24 width=56) (actual time=0.004..0.032 rows=24 loops=52)

37. 0.156 0.156 ↓ 0.0 0 52

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=52)

  • Index Cond: (medservice_id = evndirection.medservice_id)
38. 0.096 0.096 ↑ 1.0 1 16

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

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