#	exclusive	inclusive	rows x	rows	loops	node
1.	138.457	58777.832	↑ 4983.7	10	1	→ Merge IN Join (cost=879725.14..891760.80 rows=49837 width=268) (actual time=58777.763..58777.832 rows=10 loops=1) Merge Cond: (noncartdonationpaymentview.donationid = "IN_subquery".donationid)
2.	1240.059	58639.103	↑ 3.3	300694	1	→ GroupAggregate (cost=879716.61..890431.59 rows=996742 width=75) (actual time=57182.887..58639.103 rows=300694 loops=1)
3.	809.133	57399.044	↑ 3.2	308601	1	→ Sort (cost=879716.61..879965.80 rows=996742 width=75) (actual time=57182.837..57399.044 rows=308601 loops=1) Sort Key: ncd.donationid, CASE WHEN (dyi_ah_match.matchingtype = 0) THEN 'double_your_impact_match'::text WHEN (dyi_ah_match.matchingtype = 1) THEN 'almost_home_match'::text WHEN (citizen_side_of_promo_match.donationid IS NOT NULL) THEN 'promo_code_match'::text ELSE NULL::text END
4.	789.641	56589.911	↑ 3.2	308601	1	→ Hash Left Join (cost=788764.51..825496.64 rows=996742 width=75) (actual time=56172.116..56589.911 rows=308601 loops=1) Hash Cond: (ncd.sponsordonationid = sponsor_side_of_promo_match.donationid)
5.	1041.753	52882.579	↑ 3.2	308601	1	→ Merge Right Join (cost=731784.54..733828.91 rows=996742 width=75) (actual time=49710.517..52882.579 rows=308601 loops=1) Merge Cond: (citizen_side_of_promo_match.sponsordonationid = ncd.donationid)
6.	1925.111	1925.111	↑ 1.0	1763099	1	→ Index Scan using uix_dcdonation_sponsordonationid on dcdonation citizen_side_of_promo_match (cost=0.00..126460.12 rows=1763099 width=8) (actual time=0.051..1925.111 rows=1763099 loops=1)
7.	994.024	49915.715	↑ 3.2	308601	1	→ Sort (cost=731784.54..732033.72 rows=996742 width=71) (actual time=49710.458..49915.715 rows=308601 loops=1) Sort Key: ncd.donationid
8.	319.695	48921.691	↑ 3.2	308601	1	→ Hash Left Join (cost=398121.90..679265.57 rows=996742 width=71) (actual time=47778.251..48921.691 rows=308601 loops=1) Hash Cond: ((ncd.donorid = dyi_ah_match.donorid) AND (p.completedmatchingid = dyi_ah_match.matchingid))
9.	492.011	48600.503	↑ 3.2	308601	1	→ Hash Left Join (cost=398045.90..678442.00 rows=996742 width=75) (actual time=47776.738..48600.503 rows=308601 loops=1) Hash Cond: (gc.giftpurchaseid = gp.id)
10.	668.936	47872.256	↑ 3.2	308601	1	→ Hash Left Join (cost=392885.45..650326.29 rows=996742 width=50) (actual time=47540.477..47872.256 rows=308601 loops=1) Hash Cond: (pay.charge = ccc.id)
11.	817.184	46553.172	↑ 3.2	308601	1	→ Hash Join (cost=379627.56..615057.95 rows=996742 width=47) (actual time=24041.009..46553.172 rows=308601 loops=1) Hash Cond: (ncd.proposalid = p.proposalid)
12.	1109.868	38917.582	↑ 3.2	308601	1	→ Hash Left Join (cost=276436.42..488469.91 rows=996742 width=47) (actual time=17222.564..38917.582 rows=308601 loops=1) Hash Cond: (pay.accountdebitid = ad.transactionid)
13.	1527.867	20589.489	↑ 3.2	308601	1	→ Merge Left Join (cost=0.00..185685.65 rows=996742 width=47) (actual time=0.187..20589.489 rows=308601 loops=1) Merge Cond: (ncd.donationid = pay.donationid)
14.	3231.942	17018.345	↑ 2.9	300694	1	→ Merge Left Join (cost=0.00..138304.25 rows=881550 width=16) (actual time=0.117..17018.345 rows=300694 loops=1) Merge Cond: (ncd.donationid = item.donation) Filter: (item.cart IS NULL)
15.	2388.202	2388.202	↑ 1.0	1763099	1	→ Index Scan using pk_dcdonation on dcdonation ncd (cost=0.00..92615.28 rows=1763099 width=16) (actual time=0.046..2388.202 rows=1763099 loops=1)
16.	11398.201	11398.201	↑ 1.0	2525662	1	→ Index Scan using giving_cart_item_donation on giving_cart_item item (cost=0.00..75078.09 rows=2525662 width=8) (actual time=0.061..11398.201 rows=2525662 loops=1)
17.	2043.277	2043.277	↑ 1.1	1989220	1	→ Index Scan using payment_altindex1 on payment pay (cost=0.00..48778.19 rows=2129133 width=35) (actual time=0.065..2043.277 rows=1989220 loops=1)
18.	972.111	17218.225	↑ 1.0	1453800	1	→ Hash (cost=268230.17..268230.17 rows=1453800 width=8) (actual time=17218.225..17218.225 rows=1453800 loops=1)
19.	6346.392	16246.114	↑ 1.0	1453800	1	→ Merge Left Join (cost=0.00..268230.17 rows=1453800 width=8) (actual time=0.117..16246.114 rows=1453800 loops=1) Merge Cond: (ad.giftcodeid = gc.id)
20.	1603.490	1603.490	↑ 1.0	1453800	1	→ Index Scan using accountdebit_giftcodeid on accountdebit ad (cost=0.00..27554.09 rows=1453800 width=8) (actual time=0.043..1603.490 rows=1453800 loops=1)
21.	8296.232	8296.232	↓ 1.0	10484041	1	→ Index Scan using pk_giftcode on giftcode gc (cost=0.00..236524.82 rows=10293956 width=8) (actual time=0.067..8296.232 rows=10484041 loops=1)
22.	532.547	6818.406	↑ 1.0	689844	1	→ Hash (cost=99296.84..99296.84 rows=689844 width=8) (actual time=6818.406..6818.406 rows=689844 loops=1)
23.	6285.859	6285.859	↑ 1.0	689844	1	→ Seq Scan on dcproposal p (cost=0.00..99296.84 rows=689844 width=8) (actual time=0.019..6285.859 rows=689844 loops=1)
24.	293.132	650.148	↑ 1.0	437728	1	→ Hash (cost=10572.73..10572.73 rows=437728 width=11) (actual time=650.148..650.148 rows=437728 loops=1)
25.	357.016	357.016	↑ 1.0	437728	1	→ Seq Scan on credit_card_charge ccc (cost=0.00..10572.73 rows=437728 width=11) (actual time=0.019..357.016 rows=437728 loops=1)
26.	102.610	236.236	↑ 1.0	143308	1	→ Hash (cost=3861.31..3861.31 rows=143308 width=33) (actual time=236.236..236.236 rows=143308 loops=1)
27.	133.626	133.626	↑ 1.0	143308	1	→ Seq Scan on giftpurchase gp (cost=0.00..3861.31 rows=143308 width=33) (actual time=0.028..133.626 rows=143308 loops=1)
28.	0.268	1.493	↑ 1.0	403	1	→ Hash (cost=75.40..75.40 rows=403 width=12) (actual time=1.493..1.493 rows=403 loops=1)
29.	1.225	1.225	↑ 1.0	403	1	→ Seq Scan on matching dyi_ah_match (cost=0.00..75.40 rows=403 width=12) (actual time=0.022..1.225 rows=403 loops=1)
30.	1105.538	2917.691	↑ 1.0	1763099	1	→ Hash (cost=47028.10..47028.10 rows=1763099 width=8) (actual time=2917.691..2917.691 rows=1763099 loops=1)
31.	1812.153	1812.153	↑ 1.0	1763099	1	→ Seq Scan on dcdonation sponsor_side_of_promo_match (cost=0.00..47028.10 rows=1763099 width=8) (actual time=0.016..1812.153 rows=1763099 loops=1)
32.	0.025	0.272	↑ 1.0	10	1	→ Sort (cost=8.53..8.53 rows=10 width=4) (actual time=0.265..0.272 rows=10 loops=1) Sort Key: "IN_subquery".donationid
33.	0.009	0.247	↑ 1.0	10	1	→ Limit (cost=0.00..8.50 rows=10 width=4) (actual time=0.096..0.247 rows=10 loops=1)
34.	0.044	0.238	↑ 88155.0	10	1	→ Nested Loop Left Join (cost=0.00..749559.12 rows=881550 width=4) (actual time=0.095..0.238 rows=10 loops=1) Filter: (item.cart IS NULL)
35.	0.069	0.069	↑ 70524.0	25	1	→ Index Scan Backward using pk_dcdonation on dcdonation ncd (cost=0.00..92615.28 rows=1763099 width=4) (actual time=0.049..0.069 rows=25 loops=1)
36.	0.125	0.125	↑ 1.0	1	25	→ Index Scan using giving_cart_item_donation on giving_cart_item item (cost=0.00..0.37 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=25) Index Cond: (item.donation = ncd.donationid)

"Merge IN Join  (cost=879725.14..891760.80 rows=49837 width=268) (actual time=58777.763..58777.832 rows=10 loops=1)"
"  Merge Cond: (noncartdonationpaymentview.donationid = "IN_subquery".donationid)"
"  ->  GroupAggregate  (cost=879716.61..890431.59 rows=996742 width=75) (actual time=57182.887..58639.103 rows=300694 loops=1)"
"        ->  Sort  (cost=879716.61..879965.80 rows=996742 width=75) (actual time=57182.837..57399.044 rows=308601 loops=1)"
"              Sort Key: ncd.donationid, CASE WHEN (dyi_ah_match.matchingtype = 0) THEN 'double_your_impact_match'::text WHEN (dyi_ah_match.matchingtype = 1) THEN 'almost_home_match'::text WHEN (citizen_side_of_promo_match.donationid IS NOT NULL) THEN 'promo_code_match'::text ELSE NULL::text END"
"              ->  Hash Left Join  (cost=788764.51..825496.64 rows=996742 width=75) (actual time=56172.116..56589.911 rows=308601 loops=1)"
"                    Hash Cond: (ncd.sponsordonationid = sponsor_side_of_promo_match.donationid)"
"                    ->  Merge Right Join  (cost=731784.54..733828.91 rows=996742 width=75) (actual time=49710.517..52882.579 rows=308601 loops=1)"
"                          Merge Cond: (citizen_side_of_promo_match.sponsordonationid = ncd.donationid)"
"                          ->  Index Scan using uix_dcdonation_sponsordonationid on dcdonation citizen_side_of_promo_match  (cost=0.00..126460.12 rows=1763099 width=8) (actual time=0.051..1925.111 rows=1763099 loops=1)"
"                          ->  Sort  (cost=731784.54..732033.72 rows=996742 width=71) (actual time=49710.458..49915.715 rows=308601 loops=1)"
"                                Sort Key: ncd.donationid"
"                                ->  Hash Left Join  (cost=398121.90..679265.57 rows=996742 width=71) (actual time=47778.251..48921.691 rows=308601 loops=1)"
"                                      Hash Cond: ((ncd.donorid = dyi_ah_match.donorid) AND (p.completedmatchingid = dyi_ah_match.matchingid))"
"                                      ->  Hash Left Join  (cost=398045.90..678442.00 rows=996742 width=75) (actual time=47776.738..48600.503 rows=308601 loops=1)"
"                                            Hash Cond: (gc.giftpurchaseid = gp.id)"
"                                            ->  Hash Left Join  (cost=392885.45..650326.29 rows=996742 width=50) (actual time=47540.477..47872.256 rows=308601 loops=1)"
"                                                  Hash Cond: (pay.charge = ccc.id)"
"                                                  ->  Hash Join  (cost=379627.56..615057.95 rows=996742 width=47) (actual time=24041.009..46553.172 rows=308601 loops=1)"
"                                                        Hash Cond: (ncd.proposalid = p.proposalid)"
"                                                        ->  Hash Left Join  (cost=276436.42..488469.91 rows=996742 width=47) (actual time=17222.564..38917.582 rows=308601 loops=1)"
"                                                              Hash Cond: (pay.accountdebitid = ad.transactionid)"
"                                                              ->  Merge Left Join  (cost=0.00..185685.65 rows=996742 width=47) (actual time=0.187..20589.489 rows=308601 loops=1)"
"                                                                    Merge Cond: (ncd.donationid = pay.donationid)"
"                                                                    ->  Merge Left Join  (cost=0.00..138304.25 rows=881550 width=16) (actual time=0.117..17018.345 rows=300694 loops=1)"
"                                                                          Merge Cond: (ncd.donationid = item.donation)"
"                                                                          Filter: (item.cart IS NULL)"
"                                                                          ->  Index Scan using pk_dcdonation on dcdonation ncd  (cost=0.00..92615.28 rows=1763099 width=16) (actual time=0.046..2388.202 rows=1763099 loops=1)"
"                                                                          ->  Index Scan using giving_cart_item_donation on giving_cart_item item  (cost=0.00..75078.09 rows=2525662 width=8) (actual time=0.061..11398.201 rows=2525662 loops=1)"
"                                                                    ->  Index Scan using payment_altindex1 on payment pay  (cost=0.00..48778.19 rows=2129133 width=35) (actual time=0.065..2043.277 rows=1989220 loops=1)"
"                                                              ->  Hash  (cost=268230.17..268230.17 rows=1453800 width=8) (actual time=17218.225..17218.225 rows=1453800 loops=1)"
"                                                                    ->  Merge Left Join  (cost=0.00..268230.17 rows=1453800 width=8) (actual time=0.117..16246.114 rows=1453800 loops=1)"
"                                                                          Merge Cond: (ad.giftcodeid = gc.id)"
"                                                                          ->  Index Scan using accountdebit_giftcodeid on accountdebit ad  (cost=0.00..27554.09 rows=1453800 width=8) (actual time=0.043..1603.490 rows=1453800 loops=1)"
"                                                                          ->  Index Scan using pk_giftcode on giftcode gc  (cost=0.00..236524.82 rows=10293956 width=8) (actual time=0.067..8296.232 rows=10484041 loops=1)"
"                                                        ->  Hash  (cost=99296.84..99296.84 rows=689844 width=8) (actual time=6818.406..6818.406 rows=689844 loops=1)"
"                                                              ->  Seq Scan on dcproposal p  (cost=0.00..99296.84 rows=689844 width=8) (actual time=0.019..6285.859 rows=689844 loops=1)"
"                                                  ->  Hash  (cost=10572.73..10572.73 rows=437728 width=11) (actual time=650.148..650.148 rows=437728 loops=1)"
"                                                        ->  Seq Scan on credit_card_charge ccc  (cost=0.00..10572.73 rows=437728 width=11) (actual time=0.019..357.016 rows=437728 loops=1)"
"                                            ->  Hash  (cost=3861.31..3861.31 rows=143308 width=33) (actual time=236.236..236.236 rows=143308 loops=1)"
"                                                  ->  Seq Scan on giftpurchase gp  (cost=0.00..3861.31 rows=143308 width=33) (actual time=0.028..133.626 rows=143308 loops=1)"
"                                      ->  Hash  (cost=75.40..75.40 rows=403 width=12) (actual time=1.493..1.493 rows=403 loops=1)"
"                                            ->  Seq Scan on matching dyi_ah_match  (cost=0.00..75.40 rows=403 width=12) (actual time=0.022..1.225 rows=403 loops=1)"
"                    ->  Hash  (cost=47028.10..47028.10 rows=1763099 width=8) (actual time=2917.691..2917.691 rows=1763099 loops=1)"
"                          ->  Seq Scan on dcdonation sponsor_side_of_promo_match  (cost=0.00..47028.10 rows=1763099 width=8) (actual time=0.016..1812.153 rows=1763099 loops=1)"
"  ->  Sort  (cost=8.53..8.53 rows=10 width=4) (actual time=0.265..0.272 rows=10 loops=1)"
"        Sort Key: "IN_subquery".donationid"
"        ->  Limit  (cost=0.00..8.50 rows=10 width=4) (actual time=0.096..0.247 rows=10 loops=1)"
"              ->  Nested Loop Left Join  (cost=0.00..749559.12 rows=881550 width=4) (actual time=0.095..0.238 rows=10 loops=1)"
"                    Filter: (item.cart IS NULL)"
"                    ->  Index Scan Backward using pk_dcdonation on dcdonation ncd  (cost=0.00..92615.28 rows=1763099 width=4) (actual time=0.049..0.069 rows=25 loops=1)"
"                    ->  Index Scan using giving_cart_item_donation on giving_cart_item item  (cost=0.00..0.37 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=25)"
"                          Index Cond: (item.donation = ncd.donationid)"
"Total runtime: 58808.721 ms"

Per node type stats

node type	count	sum of times	% of query
GroupAggregate	1	1240.059 ms	2.1 %
Hash	6	3006.206 ms	5.1 %
Hash Join	1	817.184 ms	1.4 %
Hash Left Join	5	3380.151 ms	5.8 %
Index Scan	7	27654.638 ms	47.0 %
Index Scan Backward	1	0.069 ms	0.0 %
Limit	1	0.009 ms	0.0 %
Merge IN Join	1	138.457 ms	0.2 %
Merge Left Join	3	11106.201 ms	18.9 %
Merge Right Join	1	1041.753 ms	1.8 %
Nested Loop Left Join	1	0.044 ms	0.0 %
Seq Scan	5	8589.879 ms	14.6 %
Sort	3	1803.182 ms	3.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
accountdebit	1	1603.490 ms	2.7 %
Index Scan	1	1603.490 ms	100.0 %
credit_card_charge	1	357.016 ms	0.6 %
Seq Scan	1	357.016 ms	100.0 %
dcdonation	4	6125.535 ms	10.4 %
Index Scan	2	4313.313 ms	70.4 %
Index Scan Backward	1	0.069 ms	0.0 %
Seq Scan	1	1812.153 ms	29.6 %
dcproposal	1	6285.859 ms	10.7 %
Seq Scan	1	6285.859 ms	100.0 %
giftcode	1	8296.232 ms	14.1 %
Index Scan	1	8296.232 ms	100.0 %
giftpurchase	1	133.626 ms	0.2 %
Seq Scan	1	133.626 ms	100.0 %
giving_cart_item	2	11398.326 ms	19.4 %
Index Scan	2	11398.326 ms	100.0 %
matching	1	1.225 ms	0.0 %
Seq Scan	1	1.225 ms	100.0 %
payment	1	2043.277 ms	3.5 %
Index Scan	1	2043.277 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: ACA : noncartdonationpaymentview

Colorize:

Columns visibility:

Per node type stats

Per table stats