explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cvJY

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 1,216.442 ↑ 4.0 1 1

Append (cost=2,223.12..128,497.39 rows=4 width=108) (actual time=1,214.471..1,216.442 rows=1 loops=1)

  • Buffers: shared hit=77 read=2129
  • I/O Timings: read=1196.145
2. 0.028 1,214.472 ↑ 1.0 1 1

Subquery Scan on q (cost=2,223.12..4,148.91 rows=1 width=108) (actual time=1,214.470..1,214.472 rows=1 loops=1)

  • Buffers: shared hit=66 read=2125
  • I/O Timings: read=1194.334
3. 0.046 1,214.350 ↑ 1.0 1 1

GroupAggregate (cost=2,223.12..4,148.90 rows=1 width=53) (actual time=1,214.350..1,214.350 rows=1 loops=1)

  • Group Key: c.challengeid, c.donationoffset, c.donoroffset, c.studentsoffset
  • Buffers: shared hit=66 read=2125
  • I/O Timings: read=1194.334
4. 0.038 1,212.369 ↑ 201.0 1 1

Sort (cost=2,223.12..2,223.62 rows=201 width=53) (actual time=1,212.369..1,212.369 rows=1 loops=1)

  • Sort Key: c.challengeid, c.donationoffset, c.donoroffset, c.studentsoffset
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=54 read=2121
  • I/O Timings: read=1192.576
5. 0.022 1,212.331 ↑ 201.0 1 1

Hash Anti Join (cost=14.63..2,215.43 rows=201 width=53) (actual time=1,212.326..1,212.331 rows=1 loops=1)

  • Hash Cond: (d.donationid = p.donationid)
  • Buffers: shared hit=48 read=2121
  • I/O Timings: read=1192.576
6. 0.003 4.859 ↑ 201.0 1 1

Nested Loop Left Join (cost=1.29..2,199.55 rows=201 width=57) (actual time=4.855..4.859 rows=1 loops=1)

  • Buffers: shared read=7
  • I/O Timings: read=4.746
7. 2.534 4.855 ↑ 201.0 1 1

Nested Loop Left Join (cost=0.86..524.09 rows=201 width=52) (actual time=4.852..4.855 rows=1 loops=1)

  • Join Filter: (c.challengeid = d.challengeid)
  • Buffers: shared read=7
  • I/O Timings: read=4.746
  • -> Index Scan using dcdonation_challengeid_fk on dcdonation d (cost=0.43..513.13 rows=201 width=29) (actual time=2.529..2.529 rows=0 loo
8. 2.321 2.321 ↑ 1.0 1 1

Index Scan using pk_challenge on challenge c (cost=0.43..8.45 rows=1 width=27) (actual time=2.319..2.321 rows=1 loops=1)

  • Index Cond: (challengeid = 21483068)
  • Filter: (challengetype = 'Challenge'::text)
  • Buffers: shared read=4
  • I/O Timings: read=2.257
  • Index Cond: (challengeid = 21483068)
  • Buffers: shared read=3
  • I/O Timings: read=2.489
9. 0.001 0.001 ↓ 0.0 0 1

Index Scan using pk_donationrefund on donationrefund r (cost=0.43..8.33 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (d.donationid = donationid)
10. 2.983 1,207.450 ↓ 53.0 3,548 1

Hash (cost=12.51..12.51 rows=67 width=4) (actual time=1,207.450..1,207.450 rows=3,548 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 157kB
  • Buffers: shared hit=48 read=2114
  • I/O Timings: read=1187.830
11. 1,204.467 1,204.467 ↓ 64.1 4,297 1

Index Scan using payment_unreceived_checks on payment p (cost=0.28..12.51 rows=67 width=4) (actual time=0.114..1,204.467 rows=4,297 loops=1)

  • Buffers: shared hit=48 read=2114
  • I/O Timings: read=1187.830
12.          

SubPlan (forGroupAggregate)

13. 0.007 1.831 ↑ 1.0 1 1

Aggregate (cost=480.18..480.19 rows=1 width=5) (actual time=1.831..1.831 rows=1 loops=1)

  • Buffers: shared read=4
  • I/O Timings: read=1.758
14. 0.010 1.824 ↓ 0.0 0 1

Bitmap Heap Scan on giftpurchase giftpurchase_2 (cost=5.51..479.87 rows=122 width=5) (actual time=1.824..1.824 rows=0 loops=1)

  • Recheck Cond: (batchname = ('DCDONORACCOUNTCREDIT_GIVINGPAGE_'::text (c.challengeid)::text))
  • Buffers: shared read=4
  • I/O Timings: read=1.758
15. 1.814 1.814 ↓ 0.0 0 1

Bitmap Index Scan on giftpurchase_batchname (cost=0.00..5.48 rows=122 width=0) (actual time=1.814..1.814 rows=0 loops=1)

  • Index Cond: (batchname = ('DCDONORACCOUNTCREDIT_GIVINGPAGE_'::text (c.challengeid)::text))
  • Buffers: shared read=4
  • I/O Timings: read=1.758
16. 0.010 0.042 ↑ 1.0 1 1

Aggregate (cost=480.17..480.18 rows=1 width=4) (actual time=0.042..0.042 rows=1 loops=1)

  • Buffers: shared hit=4
17. 0.005 0.032 ↓ 0.0 0 1

Bitmap Heap Scan on giftpurchase giftpurchase_3 (cost=5.51..479.87 rows=122 width=4) (actual time=0.032..0.032 rows=0 loops=1)

  • Recheck Cond: (batchname = ('DCDONORACCOUNTCREDIT_GIVINGPAGE_'::text (c.challengeid)::text))
  • Buffers: shared hit=4
18. 0.027 0.027 ↓ 0.0 0 1

Bitmap Index Scan on giftpurchase_batchname (cost=0.00..5.48 rows=122 width=0) (actual time=0.027..0.027 rows=0 loops=1)

  • Index Cond: (batchname = ('DCDONORACCOUNTCREDIT_GIVINGPAGE_'::text (c.challengeid)::text))
  • Buffers: shared hit=4
19. 0.003 0.033 ↑ 1.0 1 1

Aggregate (cost=480.17..480.18 rows=1 width=8) (actual time=0.032..0.033 rows=1 loops=1)

  • Buffers: shared hit=4
20. 0.005 0.030 ↓ 0.0 0 1

Bitmap Heap Scan on giftpurchase giftpurchase_4 (cost=5.51..479.87 rows=122 width=8) (actual time=0.030..0.030 rows=0 loops=1)

  • Recheck Cond: (batchname = ('DCDONORACCOUNTCREDIT_GIVINGPAGE_'::text (c.challengeid)::text))
  • Buffers: shared hit=4
21. 0.025 0.025 ↓ 0.0 0 1

Bitmap Index Scan on giftpurchase_batchname (cost=0.00..5.48 rows=122 width=0) (actual time=0.025..0.025 rows=0 loops=1)

  • Index Cond: (batchname = ('DCDONORACCOUNTCREDIT_GIVINGPAGE_'::text (c.challengeid)::text))
  • Buffers: shared hit=4
22. 0.002 0.029 ↑ 1.0 1 1

Aggregate (cost=480.17..480.18 rows=1 width=8) (actual time=0.029..0.029 rows=1 loops=1)

  • Buffers: shared hit=4
23. 0.003 0.027 ↓ 0.0 0 1

Bitmap Heap Scan on giftpurchase giftpurchase_5 (cost=5.51..479.87 rows=122 width=8) (actual time=0.027..0.027 rows=0 loops=1)

  • Recheck Cond: (batchname = ('DCDONORACCOUNTCREDIT_GIVINGPAGE_'::text (c.challengeid)::text))
  • Buffers: shared hit=4
24. 0.024 0.024 ↓ 0.0 0 1

Bitmap Index Scan on giftpurchase_batchname (cost=0.00..5.48 rows=122 width=0) (actual time=0.024..0.024 rows=0 loops=1)

  • Index Cond: (batchname = ('DCDONORACCOUNTCREDIT_GIVINGPAGE_'::text (c.challengeid)::text))
  • Buffers: shared hit=4
25.          

SubPlan (forSubquery Scan)

26. 0.001 0.024 ↑ 1.0 1 1

Aggregate (cost=3,569.91..3,569.93 rows=1 width=4) (actual time=0.024..0.024 rows=1 loops=1)

27. 0.004 0.023 ↓ 0.0 0 1

Nested Loop (cost=12.94..1,702.50 rows=746,964 width=4) (actual time=0.023..0.023 rows=0 loops=1)

28. 0.007 0.018 ↑ 200.0 1 1

HashAggregate (cost=12.51..14.51 rows=200 width=4) (actual time=0.018..0.018 rows=1 loops=1)

  • Group Key: (q.proposalidarray)[i.i]
29. 0.011 0.011 ↑ 1,000.0 1 1

Function Scan on generate_series i (cost=0.01..10.01 rows=1,000 width=4) (actual time=0.011..0.011 rows=1 loops=1)

30. 0.001 0.001 ↓ 0.0 0 1

Index Scan using pk_dcproposal on dcproposal p_1 (cost=0.43..8.43 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (proposalid = (q.proposalidarray)[i.i])
  • Filter: fullyfunded
31. 0.008 0.028 ↑ 1.0 1 1

Aggregate (cost=3,569.91..3,569.93 rows=1 width=4) (actual time=0.027..0.028 rows=1 loops=1)

32. 0.003 0.020 ↓ 0.0 0 1

Nested Loop (cost=12.94..1,702.50 rows=746,964 width=4) (actual time=0.020..0.020 rows=0 loops=1)

33. 0.004 0.017 ↑ 200.0 1 1

HashAggregate (cost=12.51..14.51 rows=200 width=4) (actual time=0.016..0.017 rows=1 loops=1)

  • Group Key: (q.proposalidarray)[i_1.i]
34. 0.013 0.013 ↑ 1,000.0 1 1

Function Scan on generate_series i_1 (cost=0.01..10.01 rows=1,000 width=4) (actual time=0.012..0.013 rows=1 loops=1)

35. 0.000 0.000 ↓ 0.0 0 1

Index Scan using pk_dcproposal on dcproposal p_2 (cost=0.43..8.43 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: (proposalid = (q.proposalidarray)[i_1.i])
  • Filter: fullyfunded
36. 0.007 0.019 ↑ 1.0 1 1

Aggregate (cost=3,569.91..3,569.93 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=1)

37. 0.001 0.012 ↓ 0.0 0 1

Nested Loop (cost=12.94..1,702.50 rows=746,964 width=4) (actual time=0.012..0.012 rows=0 loops=1)

38. 0.005 0.010 ↑ 200.0 1 1

HashAggregate (cost=12.51..14.51 rows=200 width=4) (actual time=0.009..0.010 rows=1 loops=1)

  • Group Key: (q.proposalidarray)[i_2.i]
39. 0.005 0.005 ↑ 1,000.0 1 1

Function Scan on generate_series i_2 (cost=0.01..10.01 rows=1,000 width=4) (actual time=0.005..0.005 rows=1 loops=1)

40. 0.001 0.001 ↓ 0.0 0 1

Index Scan using pk_dcproposal on dcproposal p_3 (cost=0.43..8.43 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (proposalid = (q.proposalidarray)[i_2.i])
  • Filter: fullyfunded
41. 0.003 0.023 ↑ 1.0 1 1

Aggregate (cost=3,569.91..3,569.93 rows=1 width=4) (actual time=0.023..0.023 rows=1 loops=1)

42. 0.004 0.020 ↓ 0.0 0 1

Nested Loop (cost=12.94..1,702.50 rows=746,964 width=4) (actual time=0.020..0.020 rows=0 loops=1)

43. 0.007 0.016 ↑ 200.0 1 1

HashAggregate (cost=12.51..14.51 rows=200 width=4) (actual time=0.014..0.016 rows=1 loops=1)

  • Group Key: (q.proposalidarray)[i_3.i]
44. 0.009 0.009 ↑ 1,000.0 1 1

Function Scan on generate_series i_3 (cost=0.01..10.01 rows=1,000 width=4) (actual time=0.008..0.009 rows=1 loops=1)

45. 0.000 0.000 ↓ 0.0 0 1

Index Scan using pk_dcproposal on dcproposal p_4 (cost=0.43..8.43 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: (proposalid = (q.proposalidarray)[i_3.i])
  • Filter: fullyfunded
46. 0.001 0.034 ↓ 0.0 0 1

Subquery Scan on matchingchallengemetrics (cost=19,083.57..79,591.32 rows=1 width=108) (actual time=0.034..0.034 rows=0 loops=1)

  • Buffers: shared hit=4
47. 0.002 0.033 ↓ 0.0 0 1

GroupAggregate (cost=19,083.57..79,591.31 rows=1 width=47) (actual time=0.033..0.033 rows=0 loops=1)

  • Group Key: challenge.challengeid, challenge.donoroffset, challenge.studentsoffset, challenge.teachersoffset, challenge.schoolsoffset, challenge.donationoffset, ch
  • Buffers: shared hit=4
48. 0.009 0.031 ↓ 0.0 0 1

Sort (cost=19,083.57..19,083.60 rows=15 width=47) (actual time=0.031..0.031 rows=0 loops=1)

  • Sort Key: challenge.challengeid, challenge.donoroffset, challenge.studentsoffset, challenge.teachersoffset, challenge.schoolsoffset, challenge.donationoffse
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4
49. 0.002 0.022 ↓ 0.0 0 1

Nested Loop (cost=3.46..19,083.27 rows=15 width=47) (actual time=0.022..0.022 rows=0 loops=1)

  • Buffers: shared hit=4
50. 0.020 0.020 ↓ 0.0 0 1

Index Scan using pk_challenge on challenge (cost=0.43..8.45 rows=1 width=27) (actual time=0.020..0.020 rows=0 loops=1)

  • Index Cond: (challengeid = 21483068)
  • Filter: (challengetype = 'MatchingChallenge'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4
51. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.03..19,074.67 rows=15 width=24) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.60..19,045.47 rows=44 width=16) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.16..19,015.60 rows=57 width=8) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.73..18,895.24 rows=201 width=8) (never executed)

  • -> Index Scan using pix_creditlotcomponent_livecomponents on creditlotcomponent component (cost=0.43..6.06 rows=282 width=12
55. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.29..18,362.41 rows=60 width=12) (never executed)

  • Index Cond: (creditlotid = creditlot.id)
56. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.87..17,884.33 rows=1,040 width=8) (never executed)

  • -> Index Scan using giftpurchase_giftcodepageid on giftpurchase (cost=0.43..796.57 rows=183 width=8) (never exec
  • Index Cond: (giftcodepageid = 21483068)
  • Filter: (batchname !~~ '%DONORACCOUNTCREDITS'::text)
57. 0.000 0.000 ↓ 0.0 0

Index Scan using giftcode_altindex1 on giftcode (cost=0.44..84.41 rows=897 width=8) (never executed)

  • Index Cond: (giftpurchaseid = giftpurchase.id)
58. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_creditlot_giftcode on creditlot (cost=0.43..0.45 rows=1 width=12) (never executed)

  • Index Cond: (giftcodeid = giftcode.id)
59. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_payment on payment (cost=0.43..0.59 rows=1 width=8) (never executed)

  • Index Cond: (paymentid = component.paymentid)
60. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dcdonation on dcdonation donation (cost=0.43..0.51 rows=1 width=16) (never executed)

  • Index Cond: (donationid = payment.donationid)
61. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dcproposal on dcproposal proposal (cost=0.43..0.65 rows=1 width=12) (never executed)

  • Index Cond: ((proposalid = donation.proposalid) AND (proposalid IS NOT NULL))
  • Filter: fullyfunded
62.          

SubPlan (forGroupAggregate)

63. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=19,083.67..19,083.69 rows=1 width=6) (never executed)

64. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.46..19,083.56 rows=44 width=6) (never executed)

65. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_challenge on challenge challenge_3 (cost=0.43..8.45 rows=1 width=4) (never executed)

  • Index Cond: (challengeid = challenge.challengeid)
  • Filter: (challengetype = 'MatchingChallenge'::text)
66. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.03..19,074.67 rows=44 width=10) (never executed)

67. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.60..19,045.47 rows=44 width=14) (never executed)

68. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.16..19,015.60 rows=57 width=14) (never executed)

69. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.73..18,895.24 rows=201 width=14) (never executed)

  • -> Index Scan using pix_creditlotcomponent_livecomponents on creditlotcomponent component_2 (cost=0.43..6.06 rows=282 widt
70. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.29..18,362.41 rows=60 width=12) (never executed)

  • -> Index Scan using ix_creditlot_giftcode on creditlot creditlot_2 (cost=0.43..0.45 rows=1 width=12) (never executed
  • Index Cond: (creditlotid = creditlot_2.id)
71. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.87..17,884.33 rows=1,040 width=8) (never executed)

  • -> Index Scan using giftpurchase_giftcodepageid on giftpurchase giftpurchase_6 (cost=0.43..796.57 rows=183 wid
  • Index Cond: (giftcodepageid = challenge.challengeid)
  • Filter: (batchname !~~ '%DONORACCOUNTCREDITS'::text)
  • -> Index Scan using giftcode_altindex1 on giftcode giftcode_2 (cost=0.44..84.41 rows=897 width=8) (never execu
  • Index Cond: (giftpurchaseid = giftpurchase_6.id)
  • Index Cond: (giftcodeid = giftcode_2.id)
72. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_payment on payment payment_3 (cost=0.43..0.59 rows=1 width=8) (never executed)

  • Index Cond: (paymentid = component_2.paymentid)
73. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dcdonation on dcdonation donation_3 (cost=0.43..0.51 rows=1 width=8) (never executed)

  • Index Cond: (donationid = payment_3.donationid)
74. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_dcproposal on dcproposal proposal_3 (cost=0.43..0.65 rows=1 width=4) (never executed)

  • Index Cond: ((proposalid = donation_3.proposalid) AND (proposalid IS NOT NULL))
  • Heap Fetches: 0
75. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=19,502.97..19,502.98 rows=1 width=4) (never executed)

76.          

Initplan (forAggregate)

77. 0.000 0.000 ↓ 0.0 0

Seq Scan on matching (cost=0.00..390.23 rows=1 width=0) (never executed)

  • Filter: ((challengeid = challenge.challengeid) AND (matchingtype = 3))
78. 0.000 0.000 ↓ 0.0 0

Result (cost=4.32..19,111.37 rows=545 width=4) (never executed)

  • One-Time Filter: (NOT $21)
79. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.32..19,111.37 rows=545 width=4) (never executed)

  • Join Filter: (inspired.donorid <> c_1.donorid)
80. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.89..19,091.87 rows=15 width=12) (never executed)

81. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_challenge on challenge c_1 (cost=0.43..8.45 rows=1 width=4) (never executed)

  • Index Cond: (challengeid = challenge.challengeid)
82. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.46..19,083.27 rows=15 width=8) (never executed)

83. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_challenge on challenge challenge_4 (cost=0.43..8.45 rows=1 width=4) (never executed)

  • Index Cond: (challengeid = challenge.challengeid)
  • Filter: (challengetype = 'MatchingChallenge'::text)
84. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.03..19,074.67 rows=15 width=12) (never executed)

85. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.60..19,045.47 rows=44 width=8) (never executed)

86. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.16..19,015.60 rows=57 width=8) (never executed)

87. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.73..18,895.24 rows=201 width=8) (never executed)

  • -> Index Scan using pix_creditlotcomponent_livecomponents on creditlotcomponent component_3 (cost=0.43..
88. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.29..18,362.41 rows=60 width=12) (never executed)

  • -> Index Scan using ix_creditlot_giftcode on creditlot creditlot_3 (cost=0.43..0.45 rows=1 width=1
  • Index Cond: (creditlotid = creditlot_3.id)
89. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.87..17,884.33 rows=1,040 width=8) (never executed)

  • -> Index Scan using giftpurchase_giftcodepageid on giftpurchase giftpurchase_7 (cost=0.43..7
  • Index Cond: (giftcodepageid = challenge.challengeid)
  • Filter: (batchname !~~ '%DONORACCOUNTCREDITS'::text)
  • -> Index Scan using giftcode_altindex1 on giftcode giftcode_3 (cost=0.44..84.41 rows=897 wid
  • Index Cond: (giftpurchaseid = giftpurchase_7.id)
  • Index Cond: (giftcodeid = giftcode_3.id)
90. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_payment on payment payment_4 (cost=0.43..0.59 rows=1 width=8) (never executed)

  • Index Cond: (paymentid = component_3.paymentid)
91. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dcdonation on dcdonation donation_4 (cost=0.43..0.51 rows=1 width=8) (never executed)

  • Index Cond: (donationid = payment_4.donationid)
92. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dcproposal on dcproposal proposal_4 (cost=0.43..0.65 rows=1 width=4) (never executed)

  • Index Cond: ((proposalid = donation_4.proposalid) AND (proposalid IS NOT NULL))
  • Filter: fullyfunded
93. 0.000 0.000 ↓ 0.0 0

Index Scan using dcdonation_proposalid_fk on dcdonation inspired (cost=0.43..0.98 rows=26 width=8) (never executed)

  • Index Cond: (proposalid = donation_4.proposalid)
94. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2,836.84..2,836.85 rows=1 width=4) (never executed)

95. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..2,834.48 rows=941 width=4) (never executed)

96. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..1,704.96 rows=941 width=4) (never executed)

  • -> Index Scan using matchdonationevent_sponsormatchingid on matchdonationevent matchevent (cost=0.43..1286.54 rows=3190 width=8) (never exec
97. 0.000 0.000 ↓ 0.0 0

Seq Scan on matching matching_1 (cost=0.00..386.52 rows=1 width=4) (never executed)

  • Filter: (challengeid = challenge.challengeid)
  • Index Cond: (sponsormatchingid = matching_1.matchingid)
98. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dcdonation on dcdonation inspired_1 (cost=0.43..1.19 rows=1 width=8) (never executed)

  • Index Cond: (donationid = matchevent.citizendonationid)
99. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=19,083.72..19,083.73 rows=1 width=4) (never executed)

100. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=19,083.38..19,083.53 rows=15 width=12) (never executed)

  • Group Key: proposal_5.proposalid, challenge_5.challengeid
101. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.46..19,083.27 rows=15 width=12) (never executed)

102. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_challenge on challenge challenge_5 (cost=0.43..8.45 rows=1 width=4) (never executed)

  • Index Cond: (challengeid = challenge.challengeid)
  • Filter: (challengetype = 'MatchingChallenge'::text)
103. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3.03..19,074.67 rows=15 width=12) (never executed)

104. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.60..19,045.47 rows=44 width=8) (never executed)

105. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.16..19,015.60 rows=57 width=8) (never executed)

106. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.73..18,895.24 rows=201 width=8) (never executed)

  • -> Index Scan using pix_creditlotcomponent_livecomponents on creditlotcomponent component_4 (cost=0.43..6.06 rows=28
107. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.29..18,362.41 rows=60 width=12) (never executed)

  • -> Index Scan using ix_creditlot_giftcode on creditlot creditlot_4 (cost=0.43..0.45 rows=1 width=12) (never ex
  • Index Cond: (creditlotid = creditlot_4.id)
108. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.87..17,884.33 rows=1,040 width=8) (never executed)

  • -> Index Scan using giftpurchase_giftcodepageid on giftpurchase giftpurchase_8 (cost=0.43..796.57 rows=1
  • Index Cond: (giftcodepageid = challenge.challengeid)
  • Filter: (batchname !~~ '%DONORACCOUNTCREDITS'::text)
  • -> Index Scan using giftcode_altindex1 on giftcode giftcode_4 (cost=0.44..84.41 rows=897 width=8) (never
  • Index Cond: (giftpurchaseid = giftpurchase_8.id)
  • Index Cond: (giftcodeid = giftcode_4.id)
109. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_payment on payment payment_5 (cost=0.43..0.59 rows=1 width=8) (never executed)

  • Index Cond: (paymentid = component_4.paymentid)
110. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dcdonation on dcdonation donation_5 (cost=0.43..0.51 rows=1 width=8) (never executed)

  • Index Cond: (donationid = payment_5.donationid)
111. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dcproposal on dcproposal proposal_5 (cost=0.43..0.65 rows=1 width=8) (never executed)

  • Index Cond: ((proposalid = donation_5.proposalid) AND (proposalid IS NOT NULL))
  • Filter: fullyfunded
112. 0.002 0.027 ↓ 0.0 0 1

Subquery Scan on giftcodechallengemetrics (cost=21,396.58..42,799.60 rows=1 width=108) (actual time=0.027..0.027 rows=0 loops=1)

  • Buffers: shared hit=4
113. 0.001 0.025 ↓ 0.0 0 1

GroupAggregate (cost=21,396.58..42,799.59 rows=1 width=57) (actual time=0.025..0.025 rows=0 loops=1)

  • Group Key: challenge_1.challengeid, challenge_1.donoroffset, challenge_1.studentsoffset, challenge_1.teachersoffset, challenge_1.schoolsoffset, challenge_1.donati
  • Buffers: shared hit=4
114. 0.010 0.024 ↓ 0.0 0 1

Sort (cost=21,396.58..21,397.15 rows=225 width=57) (actual time=0.024..0.024 rows=0 loops=1)

  • Sort Key: challenge_1.challengeid, challenge_1.donoroffset, challenge_1.studentsoffset, challenge_1.teachersoffset, challenge_1.schoolsoffset, challenge_1.d
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4
115. 0.000 0.014 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.46..21,387.79 rows=225 width=57) (actual time=0.014..0.014 rows=0 loops=1)

  • Buffers: shared hit=4
116. 0.002 0.014 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.02..21,239.03 rows=225 width=49) (actual time=0.014..0.014 rows=0 loops=1)

  • Buffers: shared hit=4
117. 0.001 0.012 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.59..21,121.10 rows=225 width=37) (actual time=0.012..0.012 rows=0 loops=1)

  • Buffers: shared hit=4
118. 0.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=2.15..20,986.37 rows=225 width=37) (actual time=0.011..0.011 rows=0 loops=1)

  • Buffers: shared hit=4
  • -> Index Scan using pix_creditlotcomponent_livecomponents on creditlotcomponent component_1 (cost=0.43..6.06 rows=282 width=18) (n
119. 0.011 0.011 ↓ 0.0 0 1

Nested Loop (cost=1.72..20,391.37 rows=67 width=35) (actual time=0.011..0.011 rows=0 loops=1)

  • Buffers: shared hit=4
  • -> Index Scan using pk_challenge on challenge challenge_1 (cost=0.43..8.45 rows=1 width=27) (actual time=0.009..0.009 rows=0
  • Index Cond: (challengeid = 21483068)
  • Filter: (challengetype = 'GiftCodeChallenge'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4
  • Index Cond: (creditlotid = creditlot_1.id)
120. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.29..20,382.26 rows=67 width=12) (never executed)

121. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.87..19,846.71 rows=1,165 width=8) (never executed)

  • -> Index Scan using giftpurchase_giftcodepageid on giftpurchase giftpurchase_1 (cost=0.43..796.06 rows=205 width
  • Index Cond: (giftcodepageid = 21483068)
  • -> Index Scan using giftcode_altindex1 on giftcode giftcode_1 (cost=0.44..83.96 rows=897 width=8) (never execute
  • Index Cond: (giftpurchaseid = giftpurchase_1.id)
122. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_creditlot_giftcode on creditlot creditlot_1 (cost=0.43..0.45 rows=1 width=12) (never executed)

  • Index Cond: (giftcodeid = giftcode_1.id)
123. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_payment on payment payment_1 (cost=0.43..0.59 rows=1 width=8) (never executed)

  • Index Cond: (component_1.paymentid = paymentid)
124. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dcdonation on dcdonation donation_1 (cost=0.43..0.51 rows=1 width=20) (never executed)

  • Index Cond: (payment_1.donationid = donationid)
125. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dcproposal on dcproposal proposal_1 (cost=0.43..0.65 rows=1 width=12) (never executed)

  • Index Cond: (donation_1.proposalid = proposalid)
126.          

SubPlan (forGroupAggregate)

127. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=21,394.54..21,394.55 rows=1 width=4) (never executed)

128. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=21,389.48..21,391.73 rows=225 width=12) (never executed)

  • Group Key: proposal_6.proposalid, challenge_6.challengeid
129. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=3.46..21,387.79 rows=225 width=12) (never executed)

130. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=3.02..21,239.03 rows=225 width=8) (never executed)

131. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=2.59..21,121.10 rows=225 width=8) (never executed)

132. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.15..20,986.37 rows=225 width=8) (never executed)

  • -> Index Scan using pix_creditlotcomponent_livecomponents on creditlotcomponent component_5 (cost=0.43..6.06 rows=282 widt
133. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.72..20,391.37 rows=67 width=12) (never executed)

  • Index Cond: (creditlotid = creditlot_5.id)
134. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_challenge on challenge challenge_6 (cost=0.43..8.45 rows=1 width=4) (never executed)

  • Index Cond: (challengeid = challenge_1.challengeid)
  • Filter: (challengetype = 'GiftCodeChallenge'::text)
135. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.29..20,382.26 rows=67 width=12) (never executed)

  • -> Index Scan using ix_creditlot_giftcode on creditlot creditlot_5 (cost=0.43..0.45 rows=1 width=12) (never ex
136. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.87..19,846.71 rows=1,165 width=8) (never executed)

  • -> Index Scan using giftpurchase_giftcodepageid on giftpurchase giftpurchase_9 (cost=0.43..796.06 rows=2
  • Index Cond: (giftcodepageid = challenge_1.challengeid)
  • -> Index Scan using giftcode_altindex1 on giftcode giftcode_5 (cost=0.44..83.96 rows=897 width=8) (never
  • Index Cond: (giftpurchaseid = giftpurchase_9.id)
  • Index Cond: (giftcodeid = giftcode_5.id)
137. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_payment on payment payment_6 (cost=0.43..0.59 rows=1 width=8) (never executed)

  • Index Cond: (component_5.paymentid = paymentid)
138. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dcdonation on dcdonation donation_6 (cost=0.43..0.51 rows=1 width=8) (never executed)

  • Index Cond: (payment_6.donationid = donationid)
139. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_dcproposal on dcproposal proposal_6 (cost=0.43..0.65 rows=1 width=8) (never executed)

  • Index Cond: (donation_6.proposalid = proposalid)
140. 0.001 1.906 ↓ 0.0 0 1

Subquery Scan on districtchallengemetrics (cost=1,943.05..1,957.56 rows=1 width=108) (actual time=1.906..1.906 rows=0 loops=1)

  • Buffers: shared hit=3 read=4
  • I/O Timings: read=1.811
141. 0.001 1.905 ↓ 0.0 0 1

GroupAggregate (cost=1,943.05..1,957.55 rows=1 width=61) (actual time=1.905..1.905 rows=0 loops=1)

  • Group Key: challenge_2.challengeid, challenge_2.donationoffset, challenge_2.donoroffset, challenge_2.studentsoffset, ('0'::bigint), challenge_2.teachersoffset, ch
  • Buffers: shared hit=3 read=4
  • I/O Timings: read=1.811
142. 0.044 1.904 ↓ 0.0 0 1

Sort (cost=1,943.05..1,943.95 rows=362 width=61) (actual time=1.904..1.904 rows=0 loops=1)

  • Sort Key: challenge_2.challengeid, challenge_2.donationoffset, challenge_2.donoroffset, challenge_2.studentsoffset, ('0'::bigint), challenge_2.teachersoffse
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3 read=4
  • I/O Timings: read=1.811
143. 0.001 1.860 ↓ 0.0 0 1

Hash Anti Join (cost=1,443.79..1,927.66 rows=362 width=61) (actual time=1.860..1.860 rows=0 loops=1)

  • Hash Cond: (donation_2.donationid = payment_2.donationid)
  • Buffers: shared read=4
  • I/O Timings: read=1.811
144. 0.001 1.859 ↓ 0.0 0 1

Nested Loop (cost=1,430.45..1,909.75 rows=362 width=65) (actual time=1.859..1.859 rows=0 loops=1)

  • Buffers: shared read=4
  • I/O Timings: read=1.811
145. 0.001 1.858 ↓ 0.0 0 1

Nested Loop (cost=1,430.01..1,667.06 rows=132 width=44) (actual time=1.858..1.858 rows=0 loops=1)

  • Buffers: shared read=4
  • I/O Timings: read=1.811
146. 0.000 1.857 ↓ 0.0 0 1

Nested Loop (cost=1,429.58..1,447.36 rows=11 width=36) (actual time=1.857..1.857 rows=0 loops=1)

  • Join Filter: (challenge_2.districtid = school.districtid)
  • Buffers: shared read=4
  • I/O Timings: read=1.811
147. 0.002 1.857 ↓ 0.0 0 1

Nested Loop (cost=1,429.16..1,445.80 rows=1 width=39) (actual time=1.857..1.857 rows=0 loops=1)

  • Buffers: shared read=4
  • I/O Timings: read=1.811
148. 1.855 1.855 ↓ 0.0 0 1

Nested Loop (cost=0.71..12.79 rows=1 width=35) (actual time=1.855..1.855 rows=0 loops=1)

  • Buffers: shared read=4
  • I/O Timings: read=1.811
  • -> Index Scan using challenge_challengetype on challenge challenge_2 (cost=0.43..4.48 rows=1 width=31) (actual time=1.
  • Index Cond: (challengetype = 'District'::text)
  • Filter: (active AND (challengeid = 21483068))
  • Rows Removed by Filter: 1
  • Buffers: shared read=4
  • I/O Timings: read=1.811
149. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_district on district (cost=0.29..8.30 rows=1 width=4) (never executed)

  • Index Cond: (districtid = challenge_2.districtid)
  • Heap Fetches: 0
150. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1,428.45..1,432.99 rows=1 width=68) (never executed)

151.          

CTE districtdonationamounts

152. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=17.49..1,406.02 rows=362 width=15) (never executed)

153. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=17.06..1,222.54 rows=362 width=16) (never executed)

  • Join Filter: (school_2.schoolid = school_1.schoolid)
154. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=16.64..1,057.26 rows=362 width=24) (never executed)

  • Join Filter: (donor.donorid = person.personid)
155. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=16.21..878.38 rows=362 width=28) (never executed)

  • -> Index Only Scan using pk_dcdonor on dcdonor donor (cost=0.43..0.50 rows=1 width=4) (never
156. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=15.78..692.99 rows=362 width=24) (never executed)

  • -> Index Only Scan using pk_dcdonation on dcdonation donation_7 (cost=0.43..0.54 rows=
  • Index Cond: (donorid = donation_8.donorid)
  • Heap Fetches: 0
157. 0.000 0.000 ↓ 0.0 0

Hash Anti Join (cost=15.34..494.65 rows=362 width=28) (never executed)

  • Hash Cond: (donation_8.donationid = payment_7.donationid)
  • Index Cond: (donationid = donation_8.donationid)
  • Heap Fetches: 0
158. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.00..476.74 rows=362 width=28) (never executed)

  • -> Index Scan using dcdonation_proposalid_fk on dcdonation donation_8 (cos
159. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.56..234.05 rows=132 width=19) (never executed)

  • -> Index Scan using dcproposal_fullyfunded_schoolid on dcproposal pro
  • Index Cond: (proposalid = proposal_7.proposalid)
160. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.13..14.35 rows=11 width=11) (never executed)

  • Join Filter: (challenge_7.districtid = school_2.districtid)
  • -> Nested Loop (cost=0.71..12.79 rows=1 width=15) (never execu
  • -> Index Scan using challenge_challengetype on challenge
  • Index Cond: (challengetype = 'District'::text)
  • Filter: active
  • -> Index Scan using pk_district on district district_1 (
  • Index Cond: (districtid = challenge_7.districtid)
  • -> Index Scan using school_altindex4 on school school_2 (cost=
  • Index Cond: (districtid = district_1.districtid)
  • Index Cond: ((fullyfunded = true) AND (schoolid = school_2.schoo
  • Filter: fullyfunded
161. 0.000 0.000 ↓ 0.0 0

Hash (cost=12.51..12.51 rows=67 width=4) (never executed)

  • -> Index Scan using payment_unreceived_checks on payment payment_7 (cost=0
162. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_person on person (cost=0.43..0.48 rows=1 width=8) (never executed)

  • Index Cond: (personid = donation_8.donorid)
163. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_school on school school_1 (cost=0.42..0.44 rows=1 width=4) (never executed)

  • Index Cond: (schoolid = proposal_7.schoolid)
  • Heap Fetches: 0
164. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_address on address donoraddress (cost=0.43..0.49 rows=1 width=7) (never executed)

  • Index Cond: (person.addressid = addressid)
165.          

CTE instatefunding

166. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=9.97..9.99 rows=2 width=20) (never executed)

  • Group Key: districtdonationamounts.challengeid
167. 0.000 0.000 ↓ 0.0 0

CTE Scan on districtdonationamounts (cost=0.00..9.96 rows=2 width=20) (never executed)

  • Filter: ((donorstate <> ''::text) AND (donorstate = (districtstate)::text))
168.          

CTE totalfunding

169. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=9.95..12.43 rows=199 width=20) (never executed)

  • Group Key: districtdonationamounts_1.challengeid
  • -> CTE Scan on districtdonationamounts districtdonationamounts_1 (cost=0.00..8.14 rows=360 width=20) (never ex
  • Filter: (donorstate <> ''::text)
170. 0.000 0.000 ↓ 0.0 0

CTE Scan on instatefunding (cost=0.00..0.04 rows=1 width=36) (never executed)

  • Filter: (challengeid = 21483068)
171. 0.000 0.000 ↓ 0.0 0

CTE Scan on totalfunding (cost=0.00..4.48 rows=1 width=36) (never executed)

  • Filter: (challengeid = 21483068)
172. 0.000 0.000 ↓ 0.0 0

Index Scan using school_altindex4 on school (cost=0.42..1.42 rows=11 width=9) (never executed)

  • Index Cond: (districtid = district.districtid)
173. 0.000 0.000 ↓ 0.0 0

Index Scan using dcproposal_fullyfunded_schoolid on dcproposal proposal_2 (cost=0.43..19.58 rows=39 width=12) (never executed)

  • Index Cond: ((fullyfunded = true) AND (schoolid = school.schoolid))
  • Filter: fullyfunded
174. 0.000 0.000 ↓ 0.0 0

Index Scan using dcdonation_proposalid_fk on dcdonation donation_2 (cost=0.43..1.58 rows=26 width=25) (never executed)

  • Index Cond: (proposalid = proposal_2.proposalid)
175. 0.000 0.000 ↓ 0.0 0

Hash (cost=12.51..12.51 rows=67 width=4) (never executed)

176. 0.000 0.000 ↓ 0.0 0

Index Scan using payment_unreceived_checks on payment payment_2 (cost=0.28..12.51 rows=67 width=4) (never executed)