Result: beA : motherboardmetrics

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
0.058	65171.585	↑ 1.0	1	1	→ Subquery Scan q (cost=295.44..321415.01 rows=1 width=68) (actual time=65171.581..65171.585 rows=1 loops=1)
0.348	31077.770	↑ 1.0	1	1	→ Group (cost=295.44..315370.25 rows=1 width=84) (actual time=31077.767..31077.770 rows=1 loops=1)
0.115	16544.594	↑ 1.0	1	1	→ Sort (cost=295.44..295.44 rows=1 width=84) (actual time=16544.593..16544.594 rows=1 loops=1) Sort Key: m.motherboardid, m.challengeidarray, m.donationoffset, m.donoroffset, m.studentsoffset
25.110	16544.479	↑ 1.0	1	1	→ HashAggregate (cost=295.44..295.44 rows=1 width=24) (actual time=16544.478..16544.479 rows=1 loops=1)
6.301	16519.369	↓ 22.5	2928	1	→ Merge Right Join (cost=4.03..295.28 rows=130 width=24) (actual time=296.642..16519.369 rows=2928 loops=1) Merge Cond: (ml.motherboardid = m.id)
8.390	16439.930	↓ 22.5	2928	1	→ Nested Loop Left Join (cost=0.00..398.21 rows=130 width=24) (actual time=225.005..16439.930 rows=2928 loops=1)
3.171	330.468	↓ 22.5	2928	1	→ Nested Loop Left Join (cost=0.00..103.91 rows=130 width=8) (actual time=183.689..330.468 rows=2928 loops=1)
66.605	66.605	↑ 1.0	4	1	→ Index Scan using leaderboardcategory_motherboardid on leaderboardcategory ml (cost=0.00..17.94 rows=4 width=8) (actual time=42.355..66.605 rows=4 loops=1) Index Cond: (motherboardid = 6)
260.692	260.692	↓ 17.9	732	4	→ Index Scan using leaderboardchallenge_leaderboardcategoryid on leaderboardchallenge lc (cost=0.00..21.44 rows=41 width=8) (actual time=59.970..65.173 rows=732 loops=4) Index Cond: (lc.leaderboardcategoryid = ml.id)
16101.072	16101.072	↑ 1.0	1	2928	→ Index Scan using pk_challenge on challenge c (cost=0.00..2.26 rows=1 width=20) (actual time=5.497..5.499 rows=1 loops=2928) Index Cond: (lc.challengeid = c.challengeid)
1.542	73.138	↑ 1.0	1	1	→ Sort (cost=4.03..4.03 rows=1 width=4) (actual time=71.630..73.138 rows=1 loops=1) Sort Key: m.id
71.596	71.596	↑ 1.0	1	1	→ Seq Scan on motherboard m (cost=0.00..4.03 rows=1 width=4) (actual time=71.507..71.596 rows=1 loops=1) Filter: (id = 6)
					SubPlan (forGroup)
1501.844	1821.157	↑ 1.0	1	1	→ Aggregate (cost=157537.40..157537.40 rows=1 width=4) (actual time=1821.156..1821.157 rows=1 loops=1)
27.611	319.313	↓ 364.5	30981	1	→ Nested Loop (cost=1.50..157537.38 rows=85 width=4) (actual time=4.404..319.313 rows=30981 loops=1)
3.834	6.130	↓ 14.6	2914	1	→ HashAggregate (cost=1.50..1.70 rows=200 width=4) (actual time=4.251..6.130 rows=2914 loops=1)
2.296	2.296	↓ 2.9	2928	1	→ Function Scan on generate_series i (cost=0.00..1.25 rows=1000 width=4) (actual time=0.622..2.296 rows=2928 loops=1)
99.356	285.572	↓ 11.0	11	2914	→ Index Scan using dcdonation_challengeid_fk on dcdonation d (cost=0.00..787.68 rows=1 width=8) (actual time=0.006..0.098 rows=11 loops=2914) Index Cond: (d.challengeid = ($3)[i.i]) Filter: ((subplan) = 0)
					SubPlan (forIndex Scan)
62.072	186.216	↑ 1.0	1	31036	→ Aggregate (cost=8.22..8.22 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=31036)
124.144	124.144	↓ 0.0	0	31036	→ 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=31036) Index Cond: (donationid = $2) Filter: (((paymenttype)::text = 'check'::text) AND (received <> 'Y'::bpchar))
210.885	12711.671	↑ 1.0	1	1	→ Aggregate (cost=157537.40..157537.40 rows=1 width=4) (actual time=12711.670..12711.671 rows=1 loops=1)
33.363	12500.786	↓ 364.5	30981	1	→ Nested Loop (cost=1.50..157537.38 rows=85 width=4) (actual time=115.353..12500.786 rows=30981 loops=1)
4.762	7.159	↓ 14.6	2914	1	→ HashAggregate (cost=1.50..1.70 rows=200 width=4) (actual time=4.588..7.159 rows=2914 loops=1)
2.397	2.397	↓ 2.9	2928	1	→ Function Scan on generate_series i (cost=0.00..1.25 rows=1000 width=4) (actual time=0.693..2.397 rows=2928 loops=1)
2590.816	12460.264	↓ 11.0	11	2914	→ Index Scan using dcdonation_challengeid_fk on dcdonation d (cost=0.00..787.68 rows=1 width=8) (actual time=1.294..4.276 rows=11 loops=2914) Index Cond: (d.challengeid = ($3)[i.i]) Filter: ((subplan) = 0)
					SubPlan (forIndex Scan)
62.072	9869.448	↑ 1.0	1	31036	→ Aggregate (cost=8.22..8.22 rows=1 width=0) (actual time=0.318..0.318 rows=1 loops=31036)
9807.376	9807.376	↓ 0.0	0	31036	→ Index Scan using payment_altindex1 on payment p (cost=0.00..8.22 rows=1 width=0) (actual time=0.316..0.316 rows=0 loops=31036) Index Cond: (donationid = $2) Filter: (((paymenttype)::text = 'check'::text) AND (received <> 'Y'::bpchar))
					SubPlan (forSubquery Scan q)
17.331	289.762	↑ 1.0	1	1	→ Aggregate (cost=1537.18..1537.18 rows=1 width=8) (actual time=289.762..289.762 rows=1 loops=1)
53.508	272.431	↓ 154.9	30981	1	→ Nested Loop (cost=1.50..1537.13 rows=200 width=8) (actual time=46.123..272.431 rows=30981 loops=1)
40.178	64.018	↓ 154.9	30981	1	→ HashAggregate (cost=1.50..1.70 rows=200 width=4) (actual time=46.099..64.018 rows=30981 loops=1)
23.840	23.840	↓ 31.0	30981	1	→ Function Scan on generate_series i (cost=0.00..1.25 rows=1000 width=4) (actual time=6.047..23.840 rows=30981 loops=1)
154.905	154.905	↑ 1.0	1	30981	→ 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=30981) Index Cond: (d.donationid = ($0)[i.i])
9.298	6295.701	↑ 1.0	1	1	→ Aggregate (cost=1415.31..1415.31 rows=1 width=4) (actual time=6295.700..6295.701 rows=1 loops=1)
42.655	6286.403	↓ 201.0	15076	1	→ Nested Loop (cost=1.50..1415.29 rows=75 width=4) (actual time=35.733..6286.403 rows=15076 loops=1)
24.073	37.116	↓ 82.5	16507	1	→ HashAggregate (cost=1.50..1.70 rows=200 width=4) (actual time=24.702..37.116 rows=16507 loops=1)
13.043	13.043	↓ 16.5	16507	1	→ Function Scan on generate_series i (cost=0.00..1.25 rows=1000 width=4) (actual time=3.494..13.043 rows=16507 loops=1)
6206.632	6206.632	↑ 1.0	1	16507	→ Index Scan using pk_dcproposal on dcproposal p (cost=0.00..7.07 rows=1 width=8) (actual time=0.375..0.376 rows=1 loops=16507) Index Cond: (p.proposalid = ($1)[i.i]) Filter: fullyfunded
25.523	413.123	↑ 1.0	1	1	→ Aggregate (cost=1546.13..1546.13 rows=1 width=4) (actual time=413.123..413.123 rows=1 loops=1)
59.204	387.600	↓ 154.9	30981	1	→ Nested Loop Left Join (cost=1.50..1546.08 rows=200 width=4) (actual time=46.520..387.600 rows=30981 loops=1)
46.962	266.434	↓ 154.9	30981	1	→ Nested Loop (cost=1.50..1537.13 rows=200 width=8) (actual time=46.510..266.434 rows=30981 loops=1)
40.000	64.567	↓ 154.9	30981	1	→ HashAggregate (cost=1.50..1.70 rows=200 width=4) (actual time=46.491..64.567 rows=30981 loops=1)
24.567	24.567	↓ 31.0	30981	1	→ Function Scan on generate_series i (cost=0.00..1.25 rows=1000 width=4) (actual time=6.656..24.567 rows=30981 loops=1)
154.905	154.905	↑ 1.0	1	30981	→ 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=30981) Index Cond: (d.donationid = ($0)[i.i])
61.962	61.962	↓ 0.0	0	30981	→ 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=30981) Index Cond: (d.donationid = r.donationid)
32.117	27095.171	↑ 1.0	1	1	→ Aggregate (cost=1546.13..1546.13 rows=1 width=20) (actual time=27095.171..27095.171 rows=1 loops=1)
56.383	27063.054	↓ 154.9	30981	1	→ Nested Loop Left Join (cost=1.50..1546.08 rows=200 width=20) (actual time=123.798..27063.054 rows=30981 loops=1)
76.564	18331.991	↓ 154.9	30981	1	→ Nested Loop (cost=1.50..1537.13 rows=200 width=14) (actual time=90.416..18331.991 rows=30981 loops=1)
45.043	69.580	↓ 154.9	30981	1	→ HashAggregate (cost=1.50..1.70 rows=200 width=4) (actual time=46.422..69.580 rows=30981 loops=1)
24.537	24.537	↓ 31.0	30981	1	→ Function Scan on generate_series i (cost=0.00..1.25 rows=1000 width=4) (actual time=6.749..24.537 rows=30981 loops=1)
18185.847	18185.847	↑ 1.0	1	30981	→ Index Scan using pk_dcdonation on dcdonation d (cost=0.00..7.68 rows=1 width=14) (actual time=0.587..0.587 rows=1 loops=30981) Index Cond: (d.donationid = ($0)[i.i])
8674.680	8674.680	↓ 0.0	0	30981	→ Index Scan using pk_donationrefund on donationrefund r (cost=0.00..0.04 rows=1 width=14) (actual time=0.280..0.280 rows=0 loops=30981) Index Cond: (d.donationid = r.donationid)

"Subquery Scan q  (cost=295.44..321415.01 rows=1 width=68) (actual time=65171.581..65171.585 rows=1 loops=1)"
"  ->  Group  (cost=295.44..315370.25 rows=1 width=84) (actual time=31077.767..31077.770 rows=1 loops=1)"
"        ->  Sort  (cost=295.44..295.44 rows=1 width=84) (actual time=16544.593..16544.594 rows=1 loops=1)"
"              Sort Key: m.motherboardid, m.challengeidarray, m.donationoffset, m.donoroffset, m.studentsoffset"
"              ->  HashAggregate  (cost=295.44..295.44 rows=1 width=24) (actual time=16544.478..16544.479 rows=1 loops=1)"
"                    ->  Merge Right Join  (cost=4.03..295.28 rows=130 width=24) (actual time=296.642..16519.369 rows=2928 loops=1)"
"                          Merge Cond: (ml.motherboardid = m.id)"
"                          ->  Nested Loop Left Join  (cost=0.00..398.21 rows=130 width=24) (actual time=225.005..16439.930 rows=2928 loops=1)"
"                                ->  Nested Loop Left Join  (cost=0.00..103.91 rows=130 width=8) (actual time=183.689..330.468 rows=2928 loops=1)"
"                                      ->  Index Scan using leaderboardcategory_motherboardid on leaderboardcategory ml  (cost=0.00..17.94 rows=4 width=8) (actual time=42.355..66.605 rows=4 loops=1)"
"                                            Index Cond: (motherboardid = 6)"
"                                      ->  Index Scan using leaderboardchallenge_leaderboardcategoryid on leaderboardchallenge lc  (cost=0.00..21.44 rows=41 width=8) (actual time=59.970..65.173 rows=732 loops=4)"
"                                            Index Cond: (lc.leaderboardcategoryid = ml.id)"
"                                ->  Index Scan using pk_challenge on challenge c  (cost=0.00..2.26 rows=1 width=20) (actual time=5.497..5.499 rows=1 loops=2928)"
"                                      Index Cond: (lc.challengeid = c.challengeid)"
"                          ->  Sort  (cost=4.03..4.03 rows=1 width=4) (actual time=71.630..73.138 rows=1 loops=1)"
"                                Sort Key: m.id"
"                                ->  Seq Scan on motherboard m  (cost=0.00..4.03 rows=1 width=4) (actual time=71.507..71.596 rows=1 loops=1)"
"                                      Filter: (id = 6)"
"        SubPlan"
"          ->  Aggregate  (cost=157537.40..157537.40 rows=1 width=4) (actual time=1821.156..1821.157 rows=1 loops=1)"
"                ->  Nested Loop  (cost=1.50..157537.38 rows=85 width=4) (actual time=4.404..319.313 rows=30981 loops=1)"
"                      ->  HashAggregate  (cost=1.50..1.70 rows=200 width=4) (actual time=4.251..6.130 rows=2914 loops=1)"
"                            ->  Function Scan on generate_series i  (cost=0.00..1.25 rows=1000 width=4) (actual time=0.622..2.296 rows=2928 loops=1)"
"                      ->  Index Scan using dcdonation_challengeid_fk on dcdonation d  (cost=0.00..787.68 rows=1 width=8) (actual time=0.006..0.098 rows=11 loops=2914)"
"                            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=31036)"
"                                    ->  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=31036)"
"                                          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=12711.670..12711.671 rows=1 loops=1)"
"                ->  Nested Loop  (cost=1.50..157537.38 rows=85 width=4) (actual time=115.353..12500.786 rows=30981 loops=1)"
"                      ->  HashAggregate  (cost=1.50..1.70 rows=200 width=4) (actual time=4.588..7.159 rows=2914 loops=1)"
"                            ->  Function Scan on generate_series i  (cost=0.00..1.25 rows=1000 width=4) (actual time=0.693..2.397 rows=2928 loops=1)"
"                      ->  Index Scan using dcdonation_challengeid_fk on dcdonation d  (cost=0.00..787.68 rows=1 width=8) (actual time=1.294..4.276 rows=11 loops=2914)"
"                            Index Cond: (d.challengeid = ($3)[i.i])"
"                            Filter: ((subplan) = 0)"
"                            SubPlan"
"                              ->  Aggregate  (cost=8.22..8.22 rows=1 width=0) (actual time=0.318..0.318 rows=1 loops=31036)"
"                                    ->  Index Scan using payment_altindex1 on payment p  (cost=0.00..8.22 rows=1 width=0) (actual time=0.316..0.316 rows=0 loops=31036)"
"                                          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=289.762..289.762 rows=1 loops=1)"
"          ->  Nested Loop  (cost=1.50..1537.13 rows=200 width=8) (actual time=46.123..272.431 rows=30981 loops=1)"
"                ->  HashAggregate  (cost=1.50..1.70 rows=200 width=4) (actual time=46.099..64.018 rows=30981 loops=1)"
"                      ->  Function Scan on generate_series i  (cost=0.00..1.25 rows=1000 width=4) (actual time=6.047..23.840 rows=30981 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=30981)"
"                      Index Cond: (d.donationid = ($0)[i.i])"
"    ->  Aggregate  (cost=1415.31..1415.31 rows=1 width=4) (actual time=6295.700..6295.701 rows=1 loops=1)"
"          ->  Nested Loop  (cost=1.50..1415.29 rows=75 width=4) (actual time=35.733..6286.403 rows=15076 loops=1)"
"                ->  HashAggregate  (cost=1.50..1.70 rows=200 width=4) (actual time=24.702..37.116 rows=16507 loops=1)"
"                      ->  Function Scan on generate_series i  (cost=0.00..1.25 rows=1000 width=4) (actual time=3.494..13.043 rows=16507 loops=1)"
"                ->  Index Scan using pk_dcproposal on dcproposal p  (cost=0.00..7.07 rows=1 width=8) (actual time=0.375..0.376 rows=1 loops=16507)"
"                      Index Cond: (p.proposalid = ($1)[i.i])"
"                      Filter: fullyfunded"
"    ->  Aggregate  (cost=1546.13..1546.13 rows=1 width=4) (actual time=413.123..413.123 rows=1 loops=1)"
"          ->  Nested Loop Left Join  (cost=1.50..1546.08 rows=200 width=4) (actual time=46.520..387.600 rows=30981 loops=1)"
"                ->  Nested Loop  (cost=1.50..1537.13 rows=200 width=8) (actual time=46.510..266.434 rows=30981 loops=1)"
"                      ->  HashAggregate  (cost=1.50..1.70 rows=200 width=4) (actual time=46.491..64.567 rows=30981 loops=1)"
"                            ->  Function Scan on generate_series i  (cost=0.00..1.25 rows=1000 width=4) (actual time=6.656..24.567 rows=30981 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=30981)"
"                            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=30981)"
"                      Index Cond: (d.donationid = r.donationid)"
"    ->  Aggregate  (cost=1546.13..1546.13 rows=1 width=20) (actual time=27095.171..27095.171 rows=1 loops=1)"
"          ->  Nested Loop Left Join  (cost=1.50..1546.08 rows=200 width=20) (actual time=123.798..27063.054 rows=30981 loops=1)"
"                ->  Nested Loop  (cost=1.50..1537.13 rows=200 width=14) (actual time=90.416..18331.991 rows=30981 loops=1)"
"                      ->  HashAggregate  (cost=1.50..1.70 rows=200 width=4) (actual time=46.422..69.580 rows=30981 loops=1)"
"                            ->  Function Scan on generate_series i  (cost=0.00..1.25 rows=1000 width=4) (actual time=6.749..24.537 rows=30981 loops=1)"
"                      ->  Index Scan using pk_dcdonation on dcdonation d  (cost=0.00..7.68 rows=1 width=14) (actual time=0.587..0.587 rows=1 loops=30981)"
"                            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.280..0.280 rows=0 loops=30981)"
"                      Index Cond: (d.donationid = r.donationid)"
"Total runtime: 65181.157 ms"

Per node type stats

node type	count	sum of times	% of query
Aggregate	8	1921.142 ms	2.9 %
Function Scan on generate_series i	6	90.680 ms	0.1 %
Group	1	0.348 ms	0.0 %
HashAggregate	7	183.000 ms	0.3 %
Index Scan	13	62488.992 ms	95.9 %
Merge Right Join	1	6.301 ms	0.0 %
Nested Loop	6	280.663 ms	0.4 %
Nested Loop Left Join	4	127.148 ms	0.2 %
Seq Scan	1	71.596 ms	0.1 %
Sort	2	1.657 ms	0.0 %
Subquery Scan q	1	0.058 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
challenge	1	16101.072 ms	24.7 %
Index Scan	1	16101.072 ms	100.0 %
dcdonation	5	21185.829 ms	32.5 %
Index Scan	5	21185.829 ms	100.0 %
dcproposal	1	6206.632 ms	9.5 %
Index Scan	1	6206.632 ms	100.0 %
donationrefund	2	8736.642 ms	13.4 %
Index Scan	2	8736.642 ms	100.0 %
leaderboardcategory	1	66.605 ms	0.1 %
Index Scan	1	66.605 ms	100.0 %
leaderboardchallenge	1	260.692 ms	0.4 %
Index Scan	1	260.692 ms	100.0 %
motherboard	1	71.596 ms	0.1 %
Seq Scan	1	71.596 ms	100.0 %
payment	2	9931.520 ms	15.2 %
Index Scan	2	9931.520 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: beA : motherboardmetrics

Colorize:

Columns visibility:

Per node type stats

Per table stats