Result: nP8V : leaderboardmetrics

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
0.057	40674.522	↑ 1.0	1	1	→ Subquery Scan q (cost=4790.37..325909.94 rows=1 width=68) (actual time=40674.518..40674.522 rows=1 loops=1)
0.125	13486.451	↑ 1.0	1	1	→ Group (cost=4790.37..319865.18 rows=1 width=88) (actual time=13486.449..13486.451 rows=1 loops=1)
0.163	7266.311	↑ 1.0	1	1	→ Sort (cost=4790.37..4790.37 rows=1 width=88) (actual time=7266.310..7266.311 rows=1 loops=1) Sort Key: l.motherboardid, l.leaderboardcategoryid, l.challengeidarray, l.donationoffset, l.donoroffset, l.studentsoffset
2.056	7266.148	↑ 1.0	1	1	→ GroupAggregate (cost=4788.98..4790.37 rows=1 width=28) (actual time=7266.148..7266.148 rows=1 loops=1)
1.673	7264.092	↑ 1.0	793	1	→ Sort (cost=4788.98..4789.18 rows=793 width=28) (actual time=7263.765..7264.092 rows=793 loops=1) Sort Key: l.id, l.motherboardid
2.143	7262.419	↑ 1.0	793	1	→ Nested Loop Left Join (cost=0.00..4785.16 rows=793 width=28) (actual time=433.234..7262.419 rows=793 loops=1)
0.909	85.212	↑ 1.0	793	1	→ Nested Loop Left Join (cost=0.00..80.40 rows=793 width=12) (actual time=82.472..85.212 rows=793 loops=1) Join Filter: (x.leaderboardcategoryid = l.id)
64.101	64.101	↑ 1.0	1	1	→ Index Scan using pk_leaderboardcategory on leaderboardcategory l (cost=0.00..8.03 rows=1 width=8) (actual time=64.099..64.101 rows=1 loops=1) Index Cond: (id = 263)
20.202	20.202	↑ 1.0	793	1	→ Seq Scan on leaderboardchallenge x (cost=0.00..71.38 rows=793 width=8) (actual time=18.335..20.202 rows=793 loops=1) Filter: (leaderboardcategoryid = 263)
7175.064	7175.064	↑ 1.0	1	793	→ Index Scan using pk_challenge on challenge c (cost=0.00..5.93 rows=1 width=20) (actual time=9.046..9.048 rows=1 loops=793) Index Cond: (x.challengeid = c.challengeid)
					SubPlan (forGroup)
152.713	293.851	↑ 1.0	1	1	→ Aggregate (cost=157537.40..157537.40 rows=1 width=4) (actual time=293.850..293.851 rows=1 loops=1)
12.509	141.138	↓ 167.0	14194	1	→ Nested Loop (cost=1.50..157537.38 rows=85 width=4) (actual time=1.326..141.138 rows=14194 loops=1)
1.094	1.749	↓ 4.0	793	1	→ HashAggregate (cost=1.50..1.70 rows=200 width=4) (actual time=1.223..1.749 rows=793 loops=1)
0.655	0.655	↑ 1.3	793	1	→ Function Scan on generate_series i (cost=0.00..1.25 rows=1000 width=4) (actual time=0.201..0.655 rows=793 loops=1)
41.716	126.880	↓ 18.0	18	793	→ Index Scan using dcdonation_challengeid_fk on dcdonation d (cost=0.00..787.68 rows=1 width=8) (actual time=0.005..0.160 rows=18 loops=793) Index Cond: (d.challengeid = ($3)[i.i]) Filter: ((subplan) = 0)
					SubPlan (forIndex Scan)
28.388	85.164	↑ 1.0	1	14194	→ Aggregate (cost=8.22..8.22 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=14194)
56.776	56.776	↓ 0.0	0	14194	→ Index Scan using payment_altindex1 on payment p (cost=0.00..8.22 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=14194) Index Cond: (donationid = $2) Filter: (((paymenttype)::text = 'check'::text) AND (received <> 'Y'::bpchar))
26.265	5926.164	↑ 1.0	1	1	→ Aggregate (cost=157537.40..157537.40 rows=1 width=4) (actual time=5926.164..5926.164 rows=1 loops=1)
13.916	5899.899	↓ 167.0	14194	1	→ Nested Loop (cost=1.50..157537.38 rows=85 width=4) (actual time=254.983..5899.899 rows=14194 loops=1)
1.256	1.923	↓ 4.0	793	1	→ HashAggregate (cost=1.50..1.70 rows=200 width=4) (actual time=1.241..1.923 rows=793 loops=1)
0.667	0.667	↑ 1.3	793	1	→ Function Scan on generate_series i (cost=0.00..1.25 rows=1000 width=4) (actual time=0.197..0.667 rows=793 loops=1)
717.444	5884.060	↓ 18.0	18	793	→ Index Scan using dcdonation_challengeid_fk on dcdonation d (cost=0.00..787.68 rows=1 width=8) (actual time=1.349..7.420 rows=18 loops=793) Index Cond: (d.challengeid = ($3)[i.i]) Filter: ((subplan) = 0)
					SubPlan (forIndex Scan)
28.388	5166.616	↑ 1.0	1	14194	→ Aggregate (cost=8.22..8.22 rows=1 width=0) (actual time=0.363..0.364 rows=1 loops=14194)
5138.228	5138.228	↓ 0.0	0	14194	→ Index Scan using payment_altindex1 on payment p (cost=0.00..8.22 rows=1 width=0) (actual time=0.362..0.362 rows=0 loops=14194) Index Cond: (donationid = $2) Filter: (((paymenttype)::text = 'check'::text) AND (received <> 'Y'::bpchar))
					SubPlan (forSubquery Scan q)
7.936	130.817	↑ 1.0	1	1	→ Aggregate (cost=1537.18..1537.18 rows=1 width=8) (actual time=130.817..130.817 rows=1 loops=1)
23.073	122.881	↓ 71.0	14194	1	→ Nested Loop (cost=1.50..1537.13 rows=200 width=8) (actual time=20.919..122.881 rows=14194 loops=1)
17.612	28.838	↓ 71.0	14194	1	→ HashAggregate (cost=1.50..1.70 rows=200 width=4) (actual time=20.900..28.838 rows=14194 loops=1)
11.226	11.226	↓ 14.2	14194	1	→ Function Scan on generate_series i (cost=0.00..1.25 rows=1000 width=4) (actual time=3.024..11.226 rows=14194 loops=1)
70.970	70.970	↑ 1.0	1	14194	→ Index Scan using pk_dcdonation on dcdonation d (cost=0.00..7.68 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=14194) Index Cond: (d.donationid = ($0)[i.i])
2.099	4828.722	↑ 1.0	1	1	→ Aggregate (cost=1415.31..1415.31 rows=1 width=4) (actual time=4828.722..4828.722 rows=1 loops=1)
8.090	4826.623	↓ 44.5	3338	1	→ Nested Loop (cost=1.50..1415.29 rows=75 width=4) (actual time=194.164..4826.623 rows=3338 loops=1)
5.896	8.973	↓ 19.3	3860	1	→ HashAggregate (cost=1.50..1.70 rows=200 width=4) (actual time=5.698..8.973 rows=3860 loops=1)
3.077	3.077	↓ 3.9	3860	1	→ Function Scan on generate_series i (cost=0.00..1.25 rows=1000 width=4) (actual time=0.832..3.077 rows=3860 loops=1)
4809.560	4809.560	↑ 1.0	1	3860	→ Index Scan using pk_dcproposal on dcproposal p (cost=0.00..7.07 rows=1 width=8) (actual time=1.245..1.246 rows=1 loops=3860) Index Cond: (p.proposalid = ($1)[i.i]) Filter: fullyfunded
10.861	192.211	↑ 1.0	1	1	→ Aggregate (cost=1546.13..1546.13 rows=1 width=4) (actual time=192.210..192.211 rows=1 loops=1)
21.973	181.350	↓ 71.0	14194	1	→ Nested Loop Left Join (cost=1.50..1546.08 rows=200 width=4) (actual time=24.890..181.350 rows=14194 loops=1)
26.857	130.989	↓ 71.0	14194	1	→ Nested Loop (cost=1.50..1537.13 rows=200 width=8) (actual time=24.881..130.989 rows=14194 loops=1)
18.384	33.162	↓ 71.0	14194	1	→ HashAggregate (cost=1.50..1.70 rows=200 width=4) (actual time=24.866..33.162 rows=14194 loops=1)
14.778	14.778	↓ 14.2	14194	1	→ Function Scan on generate_series i (cost=0.00..1.25 rows=1000 width=4) (actual time=2.739..14.778 rows=14194 loops=1)
70.970	70.970	↑ 1.0	1	14194	→ Index Scan using pk_dcdonation on dcdonation d (cost=0.00..7.68 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=14194) Index Cond: (d.donationid = ($0)[i.i])
28.388	28.388	↓ 0.0	0	14194	→ Index Scan using pk_donationrefund on donationrefund r (cost=0.00..0.04 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=14194) Index Cond: (d.donationid = r.donationid)
14.323	22036.264	↑ 1.0	1	1	→ Aggregate (cost=1546.13..1546.13 rows=1 width=20) (actual time=22036.264..22036.264 rows=1 loops=1)
29.394	22021.941	↓ 71.0	14194	1	→ Nested Loop Left Join (cost=1.50..1546.08 rows=200 width=20) (actual time=244.280..22021.941 rows=14194 loops=1)
30.912	14881.353	↓ 71.0	14194	1	→ Nested Loop (cost=1.50..1537.13 rows=200 width=14) (actual time=173.211..14881.353 rows=14194 loops=1)
20.862	31.905	↓ 71.0	14194	1	→ HashAggregate (cost=1.50..1.70 rows=200 width=4) (actual time=21.252..31.905 rows=14194 loops=1)
11.043	11.043	↓ 14.2	14194	1	→ Function Scan on generate_series i (cost=0.00..1.25 rows=1000 width=4) (actual time=2.996..11.043 rows=14194 loops=1)
14818.536	14818.536	↑ 1.0	1	14194	→ Index Scan using pk_dcdonation on dcdonation d (cost=0.00..7.68 rows=1 width=14) (actual time=1.042..1.044 rows=1 loops=14194) Index Cond: (d.donationid = ($0)[i.i])
7111.194	7111.194	↓ 0.0	0	14194	→ Index Scan using pk_donationrefund on donationrefund r (cost=0.00..0.04 rows=1 width=14) (actual time=0.501..0.501 rows=0 loops=14194) Index Cond: (d.donationid = r.donationid)

"Subquery Scan q  (cost=4790.37..325909.94 rows=1 width=68) (actual time=40674.518..40674.522 rows=1 loops=1)"
"  ->  Group  (cost=4790.37..319865.18 rows=1 width=88) (actual time=13486.449..13486.451 rows=1 loops=1)"
"        ->  Sort  (cost=4790.37..4790.37 rows=1 width=88) (actual time=7266.310..7266.311 rows=1 loops=1)"
"              Sort Key: l.motherboardid, l.leaderboardcategoryid, l.challengeidarray, l.donationoffset, l.donoroffset, l.studentsoffset"
"              ->  GroupAggregate  (cost=4788.98..4790.37 rows=1 width=28) (actual time=7266.148..7266.148 rows=1 loops=1)"
"                    ->  Sort  (cost=4788.98..4789.18 rows=793 width=28) (actual time=7263.765..7264.092 rows=793 loops=1)"
"                          Sort Key: l.id, l.motherboardid"
"                          ->  Nested Loop Left Join  (cost=0.00..4785.16 rows=793 width=28) (actual time=433.234..7262.419 rows=793 loops=1)"
"                                ->  Nested Loop Left Join  (cost=0.00..80.40 rows=793 width=12) (actual time=82.472..85.212 rows=793 loops=1)"
"                                      Join Filter: (x.leaderboardcategoryid = l.id)"
"                                      ->  Index Scan using pk_leaderboardcategory on leaderboardcategory l  (cost=0.00..8.03 rows=1 width=8) (actual time=64.099..64.101 rows=1 loops=1)"
"                                            Index Cond: (id = 263)"
"                                      ->  Seq Scan on leaderboardchallenge x  (cost=0.00..71.38 rows=793 width=8) (actual time=18.335..20.202 rows=793 loops=1)"
"                                            Filter: (leaderboardcategoryid = 263)"
"                                ->  Index Scan using pk_challenge on challenge c  (cost=0.00..5.93 rows=1 width=20) (actual time=9.046..9.048 rows=1 loops=793)"
"                                      Index Cond: (x.challengeid = c.challengeid)"
"        SubPlan"
"          ->  Aggregate  (cost=157537.40..157537.40 rows=1 width=4) (actual time=293.850..293.851 rows=1 loops=1)"
"                ->  Nested Loop  (cost=1.50..157537.38 rows=85 width=4) (actual time=1.326..141.138 rows=14194 loops=1)"
"                      ->  HashAggregate  (cost=1.50..1.70 rows=200 width=4) (actual time=1.223..1.749 rows=793 loops=1)"
"                            ->  Function Scan on generate_series i  (cost=0.00..1.25 rows=1000 width=4) (actual time=0.201..0.655 rows=793 loops=1)"
"                      ->  Index Scan using dcdonation_challengeid_fk on dcdonation d  (cost=0.00..787.68 rows=1 width=8) (actual time=0.005..0.160 rows=18 loops=793)"
"                            Index Cond: (d.challengeid = ($3)[i.i])"
"                            Filter: ((subplan) = 0)"
"                            SubPlan"
"                              ->  Aggregate  (cost=8.22..8.22 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=14194)"
"                                    ->  Index Scan using payment_altindex1 on payment p  (cost=0.00..8.22 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=14194)"
"                                          Index Cond: (donationid = $2)"
"                                          Filter: (((paymenttype)::text = 'check'::text) AND (received <> 'Y'::bpchar))"
"          ->  Aggregate  (cost=157537.40..157537.40 rows=1 width=4) (actual time=5926.164..5926.164 rows=1 loops=1)"
"                ->  Nested Loop  (cost=1.50..157537.38 rows=85 width=4) (actual time=254.983..5899.899 rows=14194 loops=1)"
"                      ->  HashAggregate  (cost=1.50..1.70 rows=200 width=4) (actual time=1.241..1.923 rows=793 loops=1)"
"                            ->  Function Scan on generate_series i  (cost=0.00..1.25 rows=1000 width=4) (actual time=0.197..0.667 rows=793 loops=1)"
"                      ->  Index Scan using dcdonation_challengeid_fk on dcdonation d  (cost=0.00..787.68 rows=1 width=8) (actual time=1.349..7.420 rows=18 loops=793)"
"                            Index Cond: (d.challengeid = ($3)[i.i])"
"                            Filter: ((subplan) = 0)"
"                            SubPlan"
"                              ->  Aggregate  (cost=8.22..8.22 rows=1 width=0) (actual time=0.363..0.364 rows=1 loops=14194)"
"                                    ->  Index Scan using payment_altindex1 on payment p  (cost=0.00..8.22 rows=1 width=0) (actual time=0.362..0.362 rows=0 loops=14194)"
"                                          Index Cond: (donationid = $2)"
"                                          Filter: (((paymenttype)::text = 'check'::text) AND (received <> 'Y'::bpchar))"
"  SubPlan"
"    ->  Aggregate  (cost=1537.18..1537.18 rows=1 width=8) (actual time=130.817..130.817 rows=1 loops=1)"
"          ->  Nested Loop  (cost=1.50..1537.13 rows=200 width=8) (actual time=20.919..122.881 rows=14194 loops=1)"
"                ->  HashAggregate  (cost=1.50..1.70 rows=200 width=4) (actual time=20.900..28.838 rows=14194 loops=1)"
"                      ->  Function Scan on generate_series i  (cost=0.00..1.25 rows=1000 width=4) (actual time=3.024..11.226 rows=14194 loops=1)"
"                ->  Index Scan using pk_dcdonation on dcdonation d  (cost=0.00..7.68 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=14194)"
"                      Index Cond: (d.donationid = ($0)[i.i])"
"    ->  Aggregate  (cost=1415.31..1415.31 rows=1 width=4) (actual time=4828.722..4828.722 rows=1 loops=1)"
"          ->  Nested Loop  (cost=1.50..1415.29 rows=75 width=4) (actual time=194.164..4826.623 rows=3338 loops=1)"
"                ->  HashAggregate  (cost=1.50..1.70 rows=200 width=4) (actual time=5.698..8.973 rows=3860 loops=1)"
"                      ->  Function Scan on generate_series i  (cost=0.00..1.25 rows=1000 width=4) (actual time=0.832..3.077 rows=3860 loops=1)"
"                ->  Index Scan using pk_dcproposal on dcproposal p  (cost=0.00..7.07 rows=1 width=8) (actual time=1.245..1.246 rows=1 loops=3860)"
"                      Index Cond: (p.proposalid = ($1)[i.i])"
"                      Filter: fullyfunded"
"    ->  Aggregate  (cost=1546.13..1546.13 rows=1 width=4) (actual time=192.210..192.211 rows=1 loops=1)"
"          ->  Nested Loop Left Join  (cost=1.50..1546.08 rows=200 width=4) (actual time=24.890..181.350 rows=14194 loops=1)"
"                ->  Nested Loop  (cost=1.50..1537.13 rows=200 width=8) (actual time=24.881..130.989 rows=14194 loops=1)"
"                      ->  HashAggregate  (cost=1.50..1.70 rows=200 width=4) (actual time=24.866..33.162 rows=14194 loops=1)"
"                            ->  Function Scan on generate_series i  (cost=0.00..1.25 rows=1000 width=4) (actual time=2.739..14.778 rows=14194 loops=1)"
"                      ->  Index Scan using pk_dcdonation on dcdonation d  (cost=0.00..7.68 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=14194)"
"                            Index Cond: (d.donationid = ($0)[i.i])"
"                ->  Index Scan using pk_donationrefund on donationrefund r  (cost=0.00..0.04 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=14194)"
"                      Index Cond: (d.donationid = r.donationid)"
"    ->  Aggregate  (cost=1546.13..1546.13 rows=1 width=20) (actual time=22036.264..22036.264 rows=1 loops=1)"
"          ->  Nested Loop Left Join  (cost=1.50..1546.08 rows=200 width=20) (actual time=244.280..22021.941 rows=14194 loops=1)"
"                ->  Nested Loop  (cost=1.50..1537.13 rows=200 width=14) (actual time=173.211..14881.353 rows=14194 loops=1)"
"                      ->  HashAggregate  (cost=1.50..1.70 rows=200 width=4) (actual time=21.252..31.905 rows=14194 loops=1)"
"                            ->  Function Scan on generate_series i  (cost=0.00..1.25 rows=1000 width=4) (actual time=2.996..11.043 rows=14194 loops=1)"
"                      ->  Index Scan using pk_dcdonation on dcdonation d  (cost=0.00..7.68 rows=1 width=14) (actual time=1.042..1.044 rows=1 loops=14194)"
"                            Index Cond: (d.donationid = ($0)[i.i])"
"                ->  Index Scan using pk_donationrefund on donationrefund r  (cost=0.00..0.04 rows=1 width=14) (actual time=0.501..0.501 rows=0 loops=14194)"
"                      Index Cond: (d.donationid = r.donationid)"
"Total runtime: 40677.764 ms"

Per node type stats

node type	count	sum of times	% of query
Aggregate	8	270.973 ms	0.7 %
Function Scan on generate_series i	6	41.446 ms	0.1 %
Group	1	0.125 ms	0.0 %
GroupAggregate	1	2.056 ms	0.0 %
HashAggregate	6	65.104 ms	0.2 %
Index Scan	12	40102.947 ms	98.6 %
Nested Loop	6	115.357 ms	0.3 %
Nested Loop Left Join	4	54.419 ms	0.1 %
Seq Scan	1	20.202 ms	0.0 %
Sort	2	1.836 ms	0.0 %
Subquery Scan q	1	0.057 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
challenge	1	7175.064 ms	17.6 %
Index Scan	1	7175.064 ms	100.0 %
dcdonation	5	15719.636 ms	38.6 %
Index Scan	5	15719.636 ms	100.0 %
dcproposal	1	4809.560 ms	11.8 %
Index Scan	1	4809.560 ms	100.0 %
donationrefund	2	7139.582 ms	17.6 %
Index Scan	2	7139.582 ms	100.0 %
leaderboardcategory	1	64.101 ms	0.2 %
Index Scan	1	64.101 ms	100.0 %
leaderboardchallenge	1	20.202 ms	0.0 %
Seq Scan	1	20.202 ms	100.0 %
payment	2	5195.004 ms	12.8 %
Index Scan	2	5195.004 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: nP8V : leaderboardmetrics

Colorize:

Columns visibility:

Per node type stats

Per table stats