explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BcEp : Optimization for: plan #xa9y

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=8,520,187.7..8,520,187.71 rows=1 width=205) (never executed)

  • Sort Key: gs.last_payment_date DESC
2.          

CTE cte_giving_stats_by_merchant

3. 0.000 0.000 ↓ 0.0

WindowAgg (cost=8,520,170.75..8,520,170.78 rows=1 width=112) (never executed)

4. 0.000 0.000 ↓ 0.0

Sort (cost=8,520,170.75..8,520,170.76 rows=1 width=104) (never executed)

  • Sort Key: "Platform_dbo_CommunityMember"."Id", ((sum(CASE WHEN (('1'::double precision - (((('2018'::double precision - date_part('year'::text, (platform_payment_summary.legalgivenondatelocal)::timestamp without time zone)) * '12'::double precision) + ('7'::double precision - date_part('month'::text, (platform_payment_summary.legalgivenondatelocal)::timestamp without time zone))) * '0.25'::double precision)) < '0'::double precision) THEN '0'::double precision ELSE ('1'::double precision - (((('2018'::double precision - date_part('year'::text, (platform_payment_summary.legalgivenondatelocal)::timestamp without time zone)) * '12'::double precision) + ('7'::double precision - date_part('month'::text, (platform_payment_summary.legalgivenondatelocal)::timestamp without time zone))) * '0.25'::double precision)) END) + (sum(CASE WHEN (platform_payment_summary.scheduledpaymentid IS NOT NULL) THEN 0.25 ELSE '0'::numeric END))::double precision)) DESC, (sum(platform_payment_summary.amountusdmonthly)) DESC
5. 0.000 0.000 ↓ 0.0

Aggregate (cost=8,520,170.63..8,520,170.74 rows=1 width=104) (never executed)

6. 0.000 0.000 ↓ 0.0

Sort (cost=8,520,170.63..8,520,170.63 rows=1 width=69) (never executed)

  • Sort Key: "Platform_dbo_CommunityMember"."Id", platform_payment_summary.merchantid
7. 0.000 0.000 ↓ 0.0

Merge Join (cost=7,687,828.77..8,520,170.62 rows=1 width=69) (never executed)

  • Filter: (((COALESCE("Platform_dbo_SplitPaymentComponent_1"."PaymentId", "Platform_dbo_Payment"."Id")) IS NULL) OR (((COALESCE("Platform_dbo_SplitPaymentComponent_1"."PaymentId", "Platform_dbo_Payment"."Id")) IS NOT NULL) AND ((max(CASE WHEN ("Platform_dbo_SplitPaymentComponent"."PaymentId" IS NOT NULL) THEN 'true'::text ELSE NULL::text END)) = 'true'::text) AND (platform_payment_summary.amountusdmonthly <> (sum("Platform_dbo_Payment"."Amount")))))
8. 0.000 0.000 ↓ 0.0

Aggregate (cost=4,379,661.46..5,137,095.75 rows=5,992,565 width=72) (never executed)

9. 0.000 0.000 ↓ 0.0

Gather Merge (cost=4,379,661.46..5,012,250.64 rows=4,993,804 width=72) (never executed)

10. 0.000 0.000 ↓ 0.0

Aggregate (cost=4,378,661.43..4,434,841.73 rows=2,496,902 width=72) (never executed)

11. 0.000 0.000 ↓ 0.0

Sort (cost=4,378,661.43..4,384,903.69 rows=2,496,902 width=21) (never executed)

  • Sort Key: (COALESCE("Platform_dbo_SplitPaymentComponent_1"."PaymentId", "Platform_dbo_Payment"."Id"))
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,862,555.49..4,010,927.28 rows=2,496,902 width=21) (never executed)

13. 0.000 0.000 ↓ 0.0

Merge Join (cost=3,845,575.62..3,962,202 rows=2,290,875 width=13) (never executed)

14. 0.000 0.000 ↓ 0.0

Sort (cost=3,845,543.93..3,858,560.27 rows=5,206,535 width=25) (never executed)

  • Sort Key: "Platform_dbo_Payment"."Currency", ((date_trunc('month'::text, "Platform_dbo_Payment"."CreatedOn"))::date)
15. 0.000 0.000 ↓ 0.0

Hash Join (cost=24,453.22..3,015,561.7 rows=5,206,535 width=25) (never executed)

16. 0.000 0.000 ↓ 0.0

Seq Scan on Platform_dbo_Payment Platform_dbo_Payment (cost=0..2,926,565.85 rows=24,587,348 width=33) (never executed)

  • Filter: ("Platform_dbo_Payment"."__IsCurrent" = B'1'::bit(1))
17. 0.000 0.000 ↓ 0.0

Hash (cost=23,597.72..23,597.72 rows=68,440 width=8) (never executed)

18. 0.000 0.000 ↓ 0.0

Seq Scan on Platform_dbo_Fund Platform_dbo_Fund (cost=0..23,597.72 rows=68,440 width=8) (never executed)

  • Filter: (("Platform_dbo_Fund"."__IsCurrent" = B'1'::bit(1)) AND ("Platform_dbo_Fund"."TaxDeductible" = B'0'::bit(1)))
19. 0.000 0.000 ↓ 0.0

Sort (cost=31.69..32.57 rows=352 width=12) (never executed)

  • Sort Key: rateusd.currencyfrom, rateusd.startdate
20. 0.000 0.000 ↓ 0.0

Seq Scan on fxrates_monthly rateusd (cost=0..16.8 rows=352 width=12) (never executed)

  • Filter: (rateusd.currencytocode = 'USD'::bpchar)
21. 0.000 0.000 ↓ 0.0

Hash (cost=14,399.24..14,399.24 rows=148,450 width=16) (never executed)

22. 0.000 0.000 ↓ 0.0

Merge Join (cost=0.84..14,399.24 rows=148,450 width=16) (never executed)

23. 0.000 0.000 ↓ 0.0

Index Scan using "Platform_dbo_SplitPaymentComponent_SplitPaymentId" on Platform_dbo_SplitPaymentComponent Platform_dbo_SplitPaymentComponent_1 (cost=0.42..6,183.7 rows=58,285 width=16) (never executed)

  • Filter: (("Platform_dbo_SplitPaymentComponent_1"."DeletedOn" IS NULL) AND ("Platform_dbo_SplitPaymentComponent_1"."__IsCurrent" = B'1'::bit(1)) AND ("Platform_dbo_SplitPaymentComponent_1"."IsPrimaryPaymentForAggregatedReceipt" = B'1'::"bit"))
24. 0.000 0.000 ↓ 0.0

Materialize (cost=0.42..6,183.58 rows=136,201 width=16) (never executed)

25. 0.000 0.000 ↓ 0.0

Index Scan using "Platform_dbo_SplitPaymentComponent_SplitPaymentId" on Platform_dbo_SplitPaymentComponent Platform_dbo_SplitPaymentComponent (cost=0.42..5,843.08 rows=136,201 width=16) (never executed)

  • Filter: (("Platform_dbo_SplitPaymentComponent"."DeletedOn" IS NULL) AND ("Platform_dbo_SplitPaymentComponent"."__IsCurrent" = B'1'::bit(1)))
26. 0.000 0.000 ↓ 0.0

Sort (cost=3,308,167.31..3,308,167.5 rows=76 width=45) (never executed)

  • Sort Key: platform_payment_summary.id
27. 0.000 0.000 ↓ 0.0

Gather (cost=197,906.83..3,308,164.94 rows=76 width=45) (never executed)

28. 0.000 0.000 ↓ 0.0

Hash Join (cost=196,906.83..3,307,157.34 rows=32 width=45) (never executed)

29. 0.000 0.000 ↓ 0.0

Hash Join (cost=194,806.41..3,304,185.36 rows=166,010 width=53) (never executed)

30. 0.000 0.000 ↓ 0.0

Seq Scan on platform_payment_summary platform_payment_summary (cost=0..2,739,708.34 rows=14,019,448 width=45) (never executed)

  • Filter: ((platform_payment_summary.status = ANY ('{1,4}'::integer[])) AND (platform_payment_summary.legalgivenondatelocal <= '2018-07-31'::date))
31. 0.000 0.000 ↓ 0.0

Hash (cost=194,674.71..194,674.71 rows=10,536 width=24) (never executed)

32. 0.000 0.000 ↓ 0.0

Hash Join (cost=33,870.58..194,674.71 rows=10,536 width=24) (never executed)

33. 0.000 0.000 ↓ 0.0

Seq Scan on Platform_dbo_CommunityMemberMapping Platform_dbo_CommunityMemberMapping (cost=0..149,280.35 rows=4,390,007 width=24) (never executed)

  • Filter: (("Platform_dbo_CommunityMemberMapping"."RemovedOn" IS NULL) AND ("Platform_dbo_CommunityMemberMapping"."__IsCurrent" = B'1'::bit(1)))
34. 0.000 0.000 ↓ 0.0

Hash (cost=33,737.79..33,737.79 rows=10,623 width=8) (never executed)

35. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on Platform_dbo_CommunityMember Platform_dbo_CommunityMember (cost=242.76..33,737.79 rows=10,623 width=8) (never executed)

  • Filter: ("Platform_dbo_CommunityMember"."__IsCurrent" = B'1'::bit(1))
36. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on "Platform_dbo_CommunityMember_OrganizationId" (cost=0..240.11 rows=10,623 width=0) (never executed)

  • Index Cond: ("Platform_dbo_CommunityMember"."OrganizationId" = 301261072)
37. 0.000 0.000 ↓ 0.0

Hash (cost=1,829.1..1,829.1 rows=18,088 width=16) (never executed)

38. 0.000 0.000 ↓ 0.0

Seq Scan on Platform_dbo_Merchant Platform_dbo_Merchant_1 (cost=0..1,829.1 rows=18,088 width=16) (never executed)

  • Filter: ("Platform_dbo_Merchant_1"."__IsCurrent" = B'1'::bit(1))
39.          

CTE cte_giving_stats_by_cmember

40. 0.000 0.000 ↓ 0.0

Aggregate (cost=0.03..0.05 rows=1 width=80) (never executed)

41. 0.000 0.000 ↓ 0.0

CTE Scan on cte_giving_stats_by_merchant gsbm (cost=0..0.02 rows=1 width=56) (never executed)

42. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.72..16.86 rows=1 width=205) (never executed)

43. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..8.52 rows=1 width=143) (never executed)

44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..8.49 rows=1 width=143) (never executed)

45. 0.000 0.000 ↓ 0.0

CTE Scan on cte_giving_stats_by_cmember gs (cost=0..0.03 rows=1 width=80) (never executed)

  • Filter: ((gs.first_payment_date <= '2018-07-31'::date) AND (gs.first_payment_date >= date_trunc('month'::text, ('2018-07-31'::date)::timestamp with time zone)))
46. 0.000 0.000 ↓ 0.0

Index Scan using "Platform_CommunityMember_PII_Id" on Platform_CommunityMember_PII Platform_CommunityMember_PII (cost=0.43..8.46 rows=1 width=63) (never executed)

  • Index Cond: ("Platform_CommunityMember_PII"."Id" = gs.cmid)
  • Filter: (("Platform_CommunityMember_PII"."__IsCurrent" = B'1'::bit(1)) AND ("Platform_CommunityMember_PII"."OrganizationId" = 301261072))
47. 0.000 0.000 ↓ 0.0

CTE Scan on cte_giving_stats_by_merchant gsm (cost=0..0.02 rows=1 width=16) (never executed)

  • Filter: (gsm.merchant_rank = 1)
48. 0.000 0.000 ↓ 0.0

Index Scan using "Platform_dbo_Merchant_Id" on Platform_dbo_Merchant Platform_dbo_Merchant (cost=0.29..8.31 rows=1 width=30) (never executed)

  • Index Cond: ("Platform_dbo_Merchant"."Id" = gsm.merchantid)
  • Filter: ("Platform_dbo_Merchant"."__IsCurrent" = B'1'::bit(1))