explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0Nrc

Settings
# exclusive inclusive rows x rows loops node
1. 310.074 2,021.438 ↓ 44,921.0 44,921 1

Sort (cost=19,466.21..19,466.22 rows=1 width=1,211) (actual time=1,986.230..2,021.438 rows=44,921 loops=1)

  • Sort Key: (COALESCE("CustomerBankers"."FullName", ''::character varying))
  • Sort Method: external merge Disk: 30216kB
2. 113.905 1,711.364 ↓ 44,921.0 44,921 1

Nested Loop Left Join (cost=1,002.27..19,466.20 rows=1 width=1,211) (actual time=1.748..1,711.364 rows=44,921 loops=1)

3. 68.096 1,372.854 ↓ 44,921.0 44,921 1

Nested Loop Left Join (cost=1,001.98..19,455.42 rows=1 width=617) (actual time=1.720..1,372.854 rows=44,921 loops=1)

4. 73.295 1,125.074 ↓ 44,921.0 44,921 1

Nested Loop Left Join (cost=1,001.69..19,454.95 rows=1 width=481) (actual time=1.699..1,125.074 rows=44,921 loops=1)

5. 53.218 1,006.858 ↓ 44,921.0 44,921 1

Nested Loop Left Join (cost=1,001.55..19,453.49 rows=1 width=442) (actual time=1.694..1,006.858 rows=44,921 loops=1)

6. 56.329 908.719 ↓ 44,921.0 44,921 1

Nested Loop Left Join (cost=1,001.42..19,451.65 rows=1 width=437) (actual time=1.687..908.719 rows=44,921 loops=1)

7. 89.935 807.469 ↓ 44,921.0 44,921 1

Nested Loop Left Join (cost=1,001.28..19,451.50 rows=1 width=390) (actual time=1.681..807.469 rows=44,921 loops=1)

  • Filter: (((length(('{""}'::text[])[1]) = 0) OR (("Customers"."Status")::text = ANY ('{""}'::text[]))) AND ((('{""}'::text[])[1] = ''::text) OR (("Branches"."RegionId")::text = ANY ('{""}'::text[]))) AND ((('{""}'::text[])[1] = ''::text) OR ((COALESCE((SubPlan 3), 0) + COALESCE((SubPlan 4), 0)) >= 1)) AND ((('{""}'::text[])[1] = ''::text) OR ((COALESCE((SubPlan 1), 0) + COALESCE((SubPlan 2), 0)) >= 1)))
8. 81.162 358.166 ↓ 44,921.0 44,921 1

Gather (cost=1,000.86..19,358.51 rows=1 width=84) (actual time=1.659..358.166 rows=44,921 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 46.126 277.004 ↓ 14,974.0 14,974 3

Nested Loop Left Join (cost=0.85..18,358.41 rows=1 width=84) (actual time=0.182..277.004 rows=14,974 loops=3)

10. 138.432 230.876 ↓ 14,974.0 14,974 3

Nested Loop Left Join (cost=0.71..18,358.09 rows=1 width=54) (actual time=0.160..230.876 rows=14,974 loops=3)

  • Filter: (((('{""}'::text[])[1] = ''::text) OR (("OnboardingActions"."BranchId")::text = ANY ('{""}'::text[]))) AND ((('{""}'::text[])[1] = ''::text) OR (("OnboardingActions"."BankerId")::text = ANY ('{""}'::text[]))) AND ((('{""}'::text[])[1] = ''::text) OR (("OnboardingActions"."OnboardingTemplateId")::text = ANY ('{""}'::text[]))) AND ((('{""}'::text[])[1] = ''::text) OR (("OnboardingActions"."CampaignId")::text = ANY ('{""}'::text[]))))
11. 92.438 92.438 ↓ 157.6 14,974 3

Parallel Index Scan using ix_outcomes_onboardingtemplateid on "Outcomes" (cost=0.29..17,605.82 rows=95 width=50) (actual time=0.104..92.438 rows=14,974 loops=3)

  • Filter: (("BankId" = 1) AND (upper(("ChannelType")::text) = 'EMAIL'::text) AND ((length(('{delivered}'::text[])[1]) = 0) OR (("OutcomeType")::text = ANY ('{delivered}'::text[]))))
  • Rows Removed by Filter: 15148
12. 0.006 0.006 ↑ 1.0 1 44,921

Index Scan using "PK_OnboardingActionId" on "OnboardingActions" (cost=0.42..7.87 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=44,921)

  • Index Cond: ("OnboardingActionId" = "Outcomes"."OnboardingActionId")
13. 0.002 0.002 ↑ 1.0 1 44,921

Index Scan using "PK_OnboardingTemplateId" on "OnboardingTemplates" (cost=0.14..0.32 rows=1 width=38) (actual time=0.002..0.002 rows=1 loops=44,921)

  • Index Cond: ("OnboardingTemplateId" = "Outcomes"."OnboardingTemplateId")
14. 44.921 359.368 ↑ 1.0 1 44,921

Nested Loop Left Join (cost=0.42..6.56 rows=1 width=330) (actual time=0.008..0.008 rows=1 loops=44,921)

15. 224.605 224.605 ↑ 1.0 1 44,921

Index Scan using "PK_CustomerId" on "Customers" (cost=0.29..6.40 rows=1 width=311) (actual time=0.004..0.005 rows=1 loops=44,921)

  • Index Cond: ("CustomerId" = "Outcomes"."CustomerId")
16. 89.842 89.842 ↑ 1.0 1 44,921

Index Scan using "PK_BranchId" on "Branches" (cost=0.14..0.15 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=44,921)

  • Index Cond: ("BranchId" = "Customers"."BranchId")
  • Filter: "IsActive
17.          

SubPlan (forNested Loop Left Join)

18. 0.000 0.000 ↓ 0.0 0

Limit (cost=4.45..20.20 rows=1 width=4) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on "CustomerRelationships" "CustomerRelationships_1" (cost=4.45..20.20 rows=1 width=4) (never executed)

  • Recheck Cond: (("CustomerId" = "Customers"."CustomerId") AND (("Status")::text <> ALL ('{Closed,"Charged Off","Paid Off",Unoccupied,Redeemed,Inactive}'::text[])))
  • Filter: (("BankServiceId")::text = ANY ('{""}'::text[]))
20. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_customerrelationships_status_customer_id_active (cost=0.00..4.45 rows=4 width=0) (never executed)

  • Index Cond: ("CustomerId" = "Customers"."CustomerId")
21. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.29..8.31 rows=1 width=4) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_ancillaries_status_customer_id_active on "Ancillaries" "Ancillaries_1" (cost=0.29..8.31 rows=1 width=4) (never executed)

  • Index Cond: ("CustomerId" = "Customers"."CustomerId")
  • Filter: (("BankServiceId")::text = ANY ('{""}'::text[]))
23. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.56..35.05 rows=1 width=4) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.56..35.05 rows=1 width=4) (never executed)

  • Join Filter: ("CustomerRelationships"."BankServiceId" = "BankServiceGroupings"."BankServiceId")
25. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_customerrelationships_status_customer_id_active on "CustomerRelationships" (cost=0.42..20.49 rows=4 width=4) (never executed)

  • Index Cond: ("CustomerId" = "Customers"."CustomerId")
26. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.14..14.51 rows=1 width=4) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_bankservicegroupings_bankserviceid on "BankServiceGroupings" (cost=0.14..14.50 rows=1 width=4) (never executed)

  • Filter: (("BankServiceGroupId")::text = ANY ('{""}'::text[]))
28. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.43..22.82 rows=1 width=4) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.43..22.82 rows=1 width=4) (never executed)

  • Join Filter: ("Ancillaries"."BankServiceId" = "BankServiceGroupings_1"."BankServiceId")
30. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_ancillaries_status_customer_id_active on "Ancillaries" (cost=0.29..8.30 rows=1 width=4) (never executed)

  • Index Cond: ("CustomerId" = "Customers"."CustomerId")
31. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_bankservicegroupings_bankserviceid on "BankServiceGroupings" "BankServiceGroupings_1" (cost=0.14..14.50 rows=1 width=4) (never executed)

  • Filter: (("BankServiceGroupId")::text = ANY ('{""}'::text[]))
32. 44.921 44.921 ↑ 1.0 1 44,921

Index Scan using ix_branches_bankerid on "Bankers" "CustomerBankers" (cost=0.14..0.16 rows=1 width=51) (actual time=0.001..0.001 rows=1 loops=44,921)

  • Index Cond: ("BankerId" = "OnboardingActions"."BankerId")
33. 44.921 44.921 ↑ 1.0 1 44,921

Index Scan using "PK_RegionId" on "Regions" (cost=0.13..1.35 rows=1 width=9) (actual time=0.001..0.001 rows=1 loops=44,921)

  • Index Cond: ("RegionId" = "Branches"."RegionId")
34. 44.921 44.921 ↑ 1.0 1 44,921

Index Scan using ix_branches_bankerid on "Bankers" "BranchManagers" (cost=0.14..1.36 rows=1 width=43) (actual time=0.001..0.001 rows=1 loops=44,921)

  • Index Cond: ("Branches"."BankerId" = "BankerId")
35. 179.684 179.684 ↑ 1.0 1 44,921

Index Scan using ix_customeraddresses_customerid_addressindex_zero on "CustomerAddresses" (cost=0.29..0.45 rows=1 width=140) (actual time=0.004..0.004 rows=1 loops=44,921)

  • Index Cond: ("CustomerId" = "Customers"."CustomerId")
36. 224.605 224.605 ↓ 0.0 0 44,921

Index Scan using ix_outcomes_onboardingactionid on "Outcomes" outcomes2 (cost=0.29..10.73 rows=1 width=18) (actual time=0.005..0.005 rows=0 loops=44,921)

  • Index Cond: ("Outcomes"."OnboardingActionId" = "OnboardingActionId")
  • Filter: (("OutcomeId" < "Outcomes"."OutcomeId") AND (("Outcomes"."OutcomeType")::text = ("OutcomeType")::text) AND (("Outcomes"."Url")::text = ("Url")::text))
  • Rows Removed by Filter: 2