explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TQVX : only since last year

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 4,890.071 ↑ 1.0 10 1

Limit (cost=72,055.39..72,055.42 rows=10 width=44) (actual time=4,890.037..4,890.071 rows=10 loops=1)

  • Buffers: shared hit=20027 read=15376 dirtied=2
  • I/O Timings: read=4827.399
2. 0.185 4,890.037 ↑ 17.1 10 1

Sort (cost=72,055.39..72,055.82 rows=171 width=44) (actual time=4,890.035..4,890.037 rows=10 loops=1)

  • Sort Key: d.created DESC, d.donationid DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=20027 read=15376 dirtied=2
  • I/O Timings: read=4827.399
3. 0.536 4,889.852 ↓ 7.0 1,201 1

HashAggregate (cost=72,048.28..72,049.99 rows=171 width=44) (actual time=4,889.670..4,889.852 rows=1,201 loops=1)

  • Group Key: ('donation'::text), d.donationid, d.created
  • Buffers: shared hit=20021 read=15376 dirtied=2
  • I/O Timings: read=4827.399
4.          

CTE instantfundraisingpromomatchgpsreturnedtograntaccount

5. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=6.49..986.89 rows=11 width=4) (never executed)

  • Join Filter: (accountcredit.donorid <> dcproposal_1.createduserid)
6. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=6.06..963.93 rows=43 width=12) (never executed)

7. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.62..246.80 rows=8 width=12) (never executed)

8. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.19..241.89 rows=8 width=12) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on giftpurchase giftpurchase_3 (cost=4.75..174.17 rows=8 width=12) (never executed)

  • Recheck Cond: (purchasedonorid = 3511331)
  • Filter: (createddate > '2018-12-31 00:00:00'::timestamp without time zone)
10. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on giftpurchase_purchasedonorid (cost=0.00..4.75 rows=43 width=0) (never executed)

  • Index Cond: (purchasedonorid = 3511331)
11. 0.000 0.000 ↓ 0.0 0

Index Scan using giving_cart_item_pkey on giving_cart_item (cost=0.44..8.46 rows=1 width=8) (never executed)

  • Index Cond: (id = giftpurchase_3.cart_item)
12. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (proposalid = giving_cart_item.intended_project)
13. 0.000 0.000 ↓ 0.0 0

Index Scan using giftcode_altindex1 on giftcode giftcode_3 (cost=0.44..80.95 rows=869 width=8) (never executed)

  • Index Cond: (giftpurchaseid = giftpurchase_3.promomatchgpid)
14. 0.000 0.000 ↓ 0.0 0

Index Scan using accountcredit_giftcodeid on accountcredit (cost=0.43..0.51 rows=2 width=8) (never executed)

  • Index Cond: (giftcodeid = giftcode_3.id)
  • Filter: (credittype = 0)
15.          

CTE instantfundraisinggiftpurchaseandpromomatchids

16. 0.000 1.110 ↓ 0.0 0 1

Unique (cost=424.56..424.57 rows=2 width=4) (actual time=1.110..1.110 rows=0 loops=1)

  • Buffers: shared hit=12 read=2
  • I/O Timings: read=1.062
17. 0.012 1.110 ↓ 0.0 0 1

Sort (cost=424.56..424.56 rows=2 width=4) (actual time=1.110..1.110 rows=0 loops=1)

  • Sort Key: giftpurchase_4.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=12 read=2
  • I/O Timings: read=1.062
18. 0.000 1.098 ↓ 0.0 0 1

Append (cost=5.19..424.55 rows=2 width=4) (actual time=1.098..1.098 rows=0 loops=1)

  • Buffers: shared hit=12 read=2
  • I/O Timings: read=1.062
19. 0.003 1.087 ↓ 0.0 0 1

Nested Loop (cost=5.19..241.89 rows=1 width=4) (actual time=1.087..1.087 rows=0 loops=1)

  • Buffers: shared hit=7 read=2
  • I/O Timings: read=1.062
20. 0.006 0.012 ↑ 8.0 1 1

Bitmap Heap Scan on giftpurchase giftpurchase_4 (cost=4.75..174.17 rows=8 width=8) (actual time=0.011..0.012 rows=1 loops=1)

  • Recheck Cond: (purchasedonorid = 3511331)
  • Filter: (createddate > '2018-12-31 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=2
  • Buffers: shared hit=5
21. 0.006 0.006 ↑ 21.5 2 1

Bitmap Index Scan on giftpurchase_purchasedonorid (cost=0.00..4.75 rows=43 width=0) (actual time=0.006..0.006 rows=2 loops=1)

  • Index Cond: (purchasedonorid = 3511331)
  • Buffers: shared hit=3
22. 1.072 1.072 ↓ 0.0 0 1

Index Scan using giving_cart_item_pkey on giving_cart_item giving_cart_item_1 (cost=0.44..8.46 rows=1 width=4) (actual time=1.072..1.072 rows=0 loops=1)

  • Index Cond: (id = giftpurchase_4.cart_item)
  • Filter: (intended_project IS NOT NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=1.062
23. 0.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=5.19..182.64 rows=1 width=4) (actual time=0.011..0.011 rows=0 loops=1)

  • Buffers: shared hit=5
24. 0.003 0.011 ↓ 0.0 0 1

Bitmap Heap Scan on giftpurchase giftpurchase_5 (cost=4.75..174.17 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=1)

  • Recheck Cond: (purchasedonorid = 3511331)
  • Filter: ((promomatchgpid IS NOT NULL) AND (createddate > '2018-12-31 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=2
  • Buffers: shared hit=5
25. 0.008 0.008 ↑ 21.5 2 1

Bitmap Index Scan on giftpurchase_purchasedonorid (cost=0.00..4.75 rows=43 width=0) (actual time=0.008..0.008 rows=2 loops=1)

  • Index Cond: (purchasedonorid = 3511331)
  • Buffers: shared hit=3
26. 0.000 0.000 ↓ 0.0 0

Index Scan using giving_cart_item_pkey on giving_cart_item giving_cart_item_2 (cost=0.44..8.46 rows=1 width=4) (never executed)

  • Index Cond: (id = giftpurchase_5.cart_item)
  • Filter: (intended_project IS NOT NULL)
27. 0.092 4,889.316 ↓ 7.0 1,201 1

Append (cost=444.15..70,635.54 rows=171 width=44) (actual time=919.189..4,889.316 rows=1,201 loops=1)

  • Buffers: shared hit=20021 read=15376 dirtied=2
  • I/O Timings: read=4827.399
28. 0.170 919.464 ↓ 1,200.0 1,200 1

Unique (cost=444.15..444.16 rows=1 width=44) (actual time=919.189..919.464 rows=1,200 loops=1)

  • Buffers: shared hit=7161 read=3710 dirtied=2
  • I/O Timings: read=902.188
29. 0.718 919.294 ↓ 1,200.0 1,200 1

Sort (cost=444.15..444.16 rows=1 width=44) (actual time=919.188..919.294 rows=1,200 loops=1)

  • Sort Key: d.donationid, d.created
  • Sort Method: quicksort Memory: 142kB
  • Buffers: shared hit=7161 read=3710 dirtied=2
  • I/O Timings: read=902.188
30. 0.528 918.576 ↓ 1,200.0 1,200 1

Nested Loop Left Join (cost=4.47..444.14 rows=1 width=44) (actual time=1.901..918.576 rows=1,200 loops=1)

  • Filter: ((promoproposal.createduserid IS NULL) OR (promogp.purchasedonorid = 3511331))
  • Buffers: shared hit=7161 read=3710 dirtied=2
  • I/O Timings: read=902.188
31. 0.392 918.048 ↓ 1,200.0 1,200 1

Nested Loop Left Join (cost=4.04..443.53 rows=1 width=20) (actual time=1.900..918.048 rows=1,200 loops=1)

  • Buffers: shared hit=7161 read=3710 dirtied=2
  • I/O Timings: read=902.188
32. 0.422 917.656 ↓ 1,200.0 1,200 1

Nested Loop Left Join (cost=3.60..442.68 rows=1 width=20) (actual time=1.899..917.656 rows=1,200 loops=1)

  • Buffers: shared hit=7161 read=3710 dirtied=2
  • I/O Timings: read=902.188
33. 0.973 916.034 ↓ 1,200.0 1,200 1

Nested Loop Left Join (cost=3.17..442.21 rows=1 width=16) (actual time=1.897..916.034 rows=1,200 loops=1)

  • Filter: ((dcproposal.createduserid IS NULL) OR (giftpurchase.purchasedonorid = 3511331))
  • Buffers: shared hit=7155 read=3707 dirtied=2
  • I/O Timings: read=901.185
34. 0.229 640.261 ↓ 48.0 1,200 1

Nested Loop Left Join (cost=1.00..285.75 rows=25 width=16) (actual time=1.254..640.261 rows=1,200 loops=1)

  • Buffers: shared hit=3157 read=2845 dirtied=2
  • I/O Timings: read=631.313
35. 34.032 34.032 ↓ 48.0 1,200 1

Index Scan using dcdonation_donorid_created on dcdonation d (cost=0.56..74.07 rows=25 width=12) (actual time=0.023..34.032 rows=1,200 loops=1)

  • Index Cond: ((donorid = 3511331) AND (created > '2018-12-31 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=191 read=999 dirtied=2
  • I/O Timings: read=31.676
36. 606.000 606.000 ↑ 1.0 1 1,200

Index Scan using giving_cart_item_donation on giving_cart_item donationitem (cost=0.44..8.46 rows=1 width=8) (actual time=0.503..0.505 rows=1 loops=1,200)

  • Index Cond: (donation = d.donationid)
  • Filter: ((type)::text = 'proposal_donation'::text)
  • Buffers: shared hit=2966 read=1846
  • I/O Timings: read=599.637
37. 0.000 274.800 ↓ 0.0 0 1,200

Nested Loop Left Join (cost=2.17..6.23 rows=2 width=16) (actual time=0.229..0.229 rows=0 loops=1,200)

  • Buffers: shared hit=3998 read=862
  • I/O Timings: read=269.872
38. 0.000 274.800 ↓ 0.0 0 1,200

Nested Loop Left Join (cost=1.74..5.00 rows=2 width=16) (actual time=0.229..0.229 rows=0 loops=1,200)

  • Buffers: shared hit=3998 read=862
  • I/O Timings: read=269.872
39. 0.003 274.800 ↓ 0.0 0 1,200

Nested Loop Left Join (cost=1.30..3.31 rows=2 width=16) (actual time=0.229..0.229 rows=0 loops=1,200)

  • Buffers: shared hit=3998 read=862
  • I/O Timings: read=269.872
40. 0.036 271.200 ↓ 0.0 0 1,200

Nested Loop Left Join (cost=0.88..2.37 rows=2 width=8) (actual time=0.226..0.226 rows=0 loops=1,200)

  • Buffers: shared hit=3995 read=853
  • I/O Timings: read=266.301
41. 267.600 267.600 ↓ 0.0 0 1,200

Index Scan using giving_cart_item_cart on giving_cart_item redemptionitem (cost=0.44..0.77 rows=2 width=8) (actual time=0.223..0.223 rows=0 loops=1,200)

  • Index Cond: (cart = donationitem.cart)
  • Filter: ((type)::text = 'gc_redemption'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=3991 read=845
  • I/O Timings: read=262.763
42. 3.564 3.564 ↑ 1.0 1 3

Index Scan using pk_giftcode on giftcode (cost=0.44..0.79 rows=1 width=8) (actual time=1.188..1.188 rows=1 loops=3)

  • Index Cond: (id = redemptionitem.gift_code)
  • Buffers: shared hit=4 read=8
  • I/O Timings: read=3.538
43. 3.597 3.597 ↑ 1.0 1 3

Index Scan using pk_giftpurchase on giftpurchase (cost=0.43..0.46 rows=1 width=12) (actual time=1.199..1.199 rows=1 loops=3)

  • Index Cond: (id = giftcode.giftpurchaseid)
  • Buffers: shared hit=3 read=9
  • I/O Timings: read=3.571
44. 0.000 0.000 ↓ 0.0 0 3

Index Scan using giving_cart_item_pkey on giving_cart_item gpitem (cost=0.44..0.83 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=3)

  • Index Cond: (id = giftpurchase.cart_item)
45. 0.000 0.000 ↓ 0.0 0 3

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

  • Index Cond: (gpitem.intended_project = proposalid)
  • Filter: (createduserid = 3511331)
46. 1.200 1.200 ↓ 0.0 0 1,200

Index Scan using uix_giftpurchase_promomatchgpid on giftpurchase promogp (cost=0.43..0.46 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=1,200)

  • Index Cond: (promomatchgpid = giftcode.giftpurchaseid)
  • Buffers: shared hit=6 read=3
  • I/O Timings: read=1.003
47. 0.000 0.000 ↓ 0.0 0 1,200

Index Scan using giving_cart_item_pkey on giving_cart_item promoitem (cost=0.44..0.83 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1,200)

  • Index Cond: (id = promogp.cart_item)
48. 0.000 0.000 ↓ 0.0 0 1,200

Index Scan using pk_dcproposal on dcproposal promoproposal (cost=0.43..0.61 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1,200)

  • Index Cond: (promoitem.intended_project = proposalid)
  • Filter: (createduserid = 3511331)
49. 0.000 4.974 ↓ 0.0 0 1

Unique (cost=612.67..613.00 rows=44 width=44) (actual time=4.974..4.974 rows=0 loops=1)

  • Buffers: shared hit=16 read=10
  • I/O Timings: read=4.849
50. 0.009 4.974 ↓ 0.0 0 1

Sort (cost=612.67..612.78 rows=44 width=44) (actual time=4.974..4.974 rows=0 loops=1)

  • Sort Key: donationitem_1.donation, donationitem_1.create_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=16 read=10
  • I/O Timings: read=4.849
51. 0.000 4.965 ↓ 0.0 0 1

Nested Loop (cost=6.11..611.46 rows=44 width=44) (actual time=4.965..4.965 rows=0 loops=1)

  • Buffers: shared hit=16 read=10
  • I/O Timings: read=4.849
52. 0.003 4.965 ↓ 0.0 0 1

Nested Loop (cost=5.67..606.96 rows=4 width=4) (actual time=4.965..4.965 rows=0 loops=1)

  • Buffers: shared hit=16 read=10
  • I/O Timings: read=4.849
53. 0.002 4.254 ↑ 21.0 1 1

Nested Loop (cost=5.24..533.29 rows=21 width=4) (actual time=4.254..4.254 rows=1 loops=1)

  • Buffers: shared hit=14 read=9
  • I/O Timings: read=4.148
54. 0.941 3.596 ↑ 4.0 1 1

Bitmap Heap Scan on giftpurchase giftpurchase_1 (cost=4.80..174.32 rows=4 width=4) (actual time=3.596..3.596 rows=1 loops=1)

  • Recheck Cond: (purchasedonorid = 3511331)
  • Filter: ((sponsorgiftpurchaseid IS NULL) AND (createddate > '2018-12-31 00:00:00'::timestamp without time zone) AND (NOT (hashed SubPlan 8)))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=2
  • Buffers: shared hit=12 read=7
  • I/O Timings: read=3.501
55. 1.543 1.543 ↑ 21.5 2 1

Bitmap Index Scan on giftpurchase_purchasedonorid (cost=0.00..4.75 rows=43 width=0) (actual time=1.543..1.543 rows=2 loops=1)

  • Index Cond: (purchasedonorid = 3511331)
  • Buffers: shared read=3
  • I/O Timings: read=1.528
56.          

SubPlan (for Bitmap Heap Scan)

57. 1.112 1.112 ↓ 0.0 0 1

CTE Scan on instantfundraisinggiftpurchaseandpromomatchids instantfundraisinggiftpurchaseandpromomatchids_1 (cost=0.00..0.04 rows=2 width=4) (actual time=1.112..1.112 rows=0 loops=1)

  • Buffers: shared hit=12 read=2
  • I/O Timings: read=1.062
58. 0.656 0.656 ↑ 869.0 1 1

Index Scan using giftcode_altindex1 on giftcode giftcode_1 (cost=0.44..81.05 rows=869 width=8) (actual time=0.656..0.656 rows=1 loops=1)

  • Index Cond: (giftpurchaseid = giftpurchase_1.id)
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=0.647
59. 0.708 0.708 ↓ 0.0 0 1

Index Scan using giving_cart_item_gift_code on giving_cart_item redemptionitem_1 (cost=0.44..3.23 rows=28 width=8) (actual time=0.708..0.708 rows=0 loops=1)

  • Index Cond: (gift_code = giftcode_1.id)
  • Filter: ((type)::text = 'gc_redemption'::text)
  • Buffers: shared hit=2 read=1
  • I/O Timings: read=0.701
60. 0.000 0.000 ↓ 0.0 0

Index Scan using giving_cart_item_cart on giving_cart_item donationitem_1 (cost=0.44..1.06 rows=7 width=16) (never executed)

  • Index Cond: (cart = redemptionitem_1.cart)
  • Filter: ((type)::text = 'proposal_donation'::text)
61. 0.000 0.037 ↓ 0.0 0 1

Unique (cost=645.89..646.22 rows=44 width=44) (actual time=0.037..0.037 rows=0 loops=1)

  • Buffers: shared hit=5
62. 0.008 0.037 ↓ 0.0 0 1

Sort (cost=645.89..646.00 rows=44 width=44) (actual time=0.037..0.037 rows=0 loops=1)

  • Sort Key: donationitem_2.donation, donationitem_2.create_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=5
63. 0.001 0.029 ↓ 0.0 0 1

Nested Loop (cost=6.39..644.68 rows=44 width=44) (actual time=0.028..0.029 rows=0 loops=1)

  • Buffers: shared hit=5
64. 0.000 0.028 ↓ 0.0 0 1

Nested Loop (cost=5.95..640.18 rows=4 width=4) (actual time=0.028..0.028 rows=0 loops=1)

  • Buffers: shared hit=5
65. 0.000 0.028 ↓ 0.0 0 1

Nested Loop (cost=5.51..566.51 rows=21 width=4) (actual time=0.028..0.028 rows=0 loops=1)

  • Buffers: shared hit=5
66. 0.004 0.028 ↓ 0.0 0 1

Nested Loop (cost=5.08..207.54 rows=4 width=4) (actual time=0.028..0.028 rows=0 loops=1)

  • Buffers: shared hit=5
67. 0.016 0.023 ↑ 4.0 1 1

Bitmap Heap Scan on giftpurchase giftpurchase_2 (cost=4.80..174.32 rows=4 width=8) (actual time=0.023..0.023 rows=1 loops=1)

  • Recheck Cond: (purchasedonorid = 3511331)
  • Filter: ((createddate > '2018-12-31 00:00:00'::timestamp without time zone) AND (NOT (hashed SubPlan 7)))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=2
  • Buffers: shared hit=5
68. 0.007 0.007 ↑ 21.5 2 1

Bitmap Index Scan on giftpurchase_purchasedonorid (cost=0.00..4.75 rows=43 width=0) (actual time=0.007..0.007 rows=2 loops=1)

  • Index Cond: (purchasedonorid = 3511331)
  • Buffers: shared hit=3
69.          

SubPlan (for Bitmap Heap Scan)

70. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on instantfundraisinggiftpurchaseandpromomatchids (cost=0.00..0.04 rows=2 width=4) (actual time=0.000..0.000 rows=0 loops=1)

71. 0.001 0.001 ↓ 0.0 0 1

Index Only Scan using matching_pkey on matching (cost=0.28..8.29 rows=1 width=4) (actual time=0.000..0.001 rows=0 loops=1)

  • Index Cond: (matchingid = giftpurchase_2.sponsormatchingid)
  • Heap Fetches: 0
72. 0.000 0.000 ↓ 0.0 0

Index Scan using giftcode_altindex1 on giftcode giftcode_2 (cost=0.44..81.05 rows=869 width=8) (never executed)

  • Index Cond: (giftpurchaseid = giftpurchase_2.id)
73. 0.000 0.000 ↓ 0.0 0

Index Scan using giving_cart_item_gift_code on giving_cart_item redemptionitem_2 (cost=0.44..3.23 rows=28 width=8) (never executed)

  • Index Cond: (gift_code = giftcode_2.id)
  • Filter: ((type)::text = 'gc_redemption'::text)
74. 0.000 0.000 ↓ 0.0 0

Index Scan using giving_cart_item_cart on giving_cart_item donationitem_2 (cost=0.44..1.06 rows=7 width=16) (never executed)

  • Index Cond: (cart = redemptionitem_2.cart)
  • Filter: ((type)::text = 'proposal_donation'::text)
75. 0.000 1.251 ↓ 0.0 0 1

Nested Loop (cost=175.32..392.07 rows=6 width=44) (actual time=1.251..1.251 rows=0 loops=1)

  • Buffers: shared hit=10 read=3
  • I/O Timings: read=1.198
76.          

Initplan (for Nested Loop)

77. 0.012 0.023 ↑ 1.0 1 1

Aggregate (cost=174.19..174.20 rows=1 width=32) (actual time=0.023..0.023 rows=1 loops=1)

  • Buffers: shared hit=5
78. 0.005 0.011 ↑ 8.0 1 1

Bitmap Heap Scan on giftpurchase gp_3 (cost=4.75..174.17 rows=8 width=4) (actual time=0.011..0.011 rows=1 loops=1)

  • Recheck Cond: (purchasedonorid = 3511331)
  • Filter: (createddate > '2018-12-31 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=2
  • Buffers: shared hit=5
79. 0.006 0.006 ↑ 21.5 2 1

Bitmap Index Scan on giftpurchase_purchasedonorid (cost=0.00..4.75 rows=43 width=0) (actual time=0.006..0.006 rows=2 loops=1)

  • Index Cond: (purchasedonorid = 3511331)
  • Buffers: shared hit=3
80. 0.918 0.918 ↑ 19.0 1 1

Index Scan using payment_altindex2 on payment (cost=0.44..52.33 rows=19 width=4) (actual time=0.917..0.918 rows=1 loops=1)

  • Index Cond: (giftpurchaseid = ANY ($10))
  • Filter: ((paymenttype)::text <> 'debit'::text)
  • Buffers: shared hit=7 read=2
  • I/O Timings: read=0.875
81. 0.331 0.331 ↓ 0.0 0 1

Index Scan using pk_giftpurchase on giftpurchase gp (cost=0.68..8.70 rows=1 width=12) (actual time=0.331..0.331 rows=0 loops=1)

  • Index Cond: (id = payment.giftpurchaseid)
  • Filter: ((cart_item IS NULL) AND (credittype <> 3) AND (amount > '0'::numeric) AND (NOT (hashed SubPlan 6)))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=3 read=1
  • I/O Timings: read=0.323
82.          

SubPlan (for Index Scan)

83. 0.000 0.000 ↓ 0.0 0

CTE Scan on instantfundraisingpromomatchgpsreturnedtograntaccount instantfundraisingpromomatchgpsreturnedtograntaccount_1 (cost=0.00..0.22 rows=11 width=4) (never executed)

84. 0.000 1.609 ↓ 0.0 0 1

Nested Loop (cost=175.31..487.97 rows=1 width=44) (actual time=1.609..1.609 rows=0 loops=1)

  • Buffers: shared hit=15 read=5
  • I/O Timings: read=1.544
85.          

Initplan (for Nested Loop)

86. 0.007 0.017 ↑ 1.0 1 1

Aggregate (cost=174.18..174.19 rows=1 width=32) (actual time=0.017..0.017 rows=1 loops=1)

  • Buffers: shared hit=5
87. 0.002 0.010 ↑ 21.5 2 1

Bitmap Heap Scan on giftpurchase gp_2 (cost=4.76..174.07 rows=43 width=4) (actual time=0.010..0.010 rows=2 loops=1)

  • Recheck Cond: (purchasedonorid = 3511331)
  • Heap Blocks: exact=2
  • Buffers: shared hit=5
88. 0.008 0.008 ↑ 21.5 2 1

Bitmap Index Scan on giftpurchase_purchasedonorid (cost=0.00..4.75 rows=43 width=0) (actual time=0.008..0.008 rows=2 loops=1)

  • Index Cond: (purchasedonorid = 3511331)
  • Buffers: shared hit=3
89. 0.684 0.684 ↑ 30.0 1 1

Index Scan using payment_altindex2 on payment payment_1 (cost=0.44..52.33 rows=30 width=4) (actual time=0.681..0.684 rows=1 loops=1)

  • Index Cond: (giftpurchaseid = ANY ($8))
  • Filter: ((paymenttype)::text = 'debit'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=13 read=3
  • I/O Timings: read=0.628
90. 0.923 0.923 ↓ 0.0 0 1

Index Scan using pk_giftpurchase on giftpurchase gp_1 (cost=0.68..8.71 rows=1 width=12) (actual time=0.923..0.923 rows=0 loops=1)

  • Index Cond: (id = payment_1.giftpurchaseid)
  • Filter: ((cart_item IS NULL) AND (sponsormatchingid IS NOT NULL) AND (createddate > '2018-12-31 00:00:00'::timestamp without time zone) AND (credittype <> 3) AND (amount > '0'::numeric) AND (NOT (hashed SubPlan 4)))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=0.916
91.          

SubPlan (for Index Scan)

92. 0.000 0.000 ↓ 0.0 0

CTE Scan on instantfundraisingpromomatchgpsreturnedtograntaccount (cost=0.00..0.22 rows=11 width=4) (never executed)

93. 1.770 2,305.079 ↑ 74.0 1 1

Nested Loop (cost=0.87..38,431.53 rows=74 width=44) (actual time=2,304.909..2,305.079 rows=1 loops=1)

  • Buffers: shared hit=9728 read=6475
  • I/O Timings: read=2278.692
94. 1,052.867 1,052.867 ↑ 1.1 3,282 1

Index Scan using giving_cart_donor on giving_cart gc (cost=0.43..6,209.00 rows=3,630 width=4) (actual time=2.702..1,052.867 rows=3,282 loops=1)

  • Index Cond: (donor = 3511331)
  • Buffers: shared hit=63 read=2951
  • I/O Timings: read=1045.321
95. 1,250.442 1,250.442 ↓ 0.0 0 3,282

Index Scan using giving_cart_item_cart on giving_cart_item gci (cost=0.44..8.87 rows=1 width=16) (actual time=0.381..0.381 rows=0 loops=3,282)

  • Index Cond: (cart = gc.id)
  • Filter: ((create_date > '2018-12-31 00:00:00'::timestamp without time zone) AND ((type)::text = 'gc_purchase'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=9665 read=3524
  • I/O Timings: read=1233.371
96. 0.694 1,656.810 ↓ 0.0 0 1

Hash Join (cost=6,254.93..29,618.89 rows=1 width=44) (actual time=1,656.810..1,656.810 rows=0 loops=1)

  • Hash Cond: (gci_1.cart = gc_1.id)
  • Buffers: shared hit=3086 read=5173
  • I/O Timings: read=1638.928
97. 1,652.870 1,652.870 ↓ 1.1 1,740 1

Index Scan using giving_cart_item_type_idx on giving_cart_item gci_1 (cost=0.56..23,358.53 rows=1,592 width=16) (actual time=222.952..1,652.870 rows=1,740 loops=1)

  • Index Cond: ((type)::text = 'account_credit_purchase'::text)
  • Filter: (create_date > '2018-12-31 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 4688
  • Buffers: shared hit=72 read=5173
  • I/O Timings: read=1638.928
98. 0.439 3.246 ↑ 1.1 3,282 1

Hash (cost=6,209.00..6,209.00 rows=3,630 width=4) (actual time=3.246..3.246 rows=3,282 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 148kB
  • Buffers: shared hit=3014
99. 2.807 2.807 ↑ 1.1 3,282 1

Index Scan using giving_cart_donor on giving_cart gc_1 (cost=0.43..6,209.00 rows=3,630 width=4) (actual time=0.016..2.807 rows=3,282 loops=1)

  • Index Cond: (donor = 3511331)
  • Buffers: shared hit=3014
Planning time : 39.281 ms
Execution time : 4,891.074 ms