#	exclusive	inclusive	rows x	rows	loops	node
1.	0.006	0.098	↑ 53.5	2	1	→ Subquery Scan on foo (cost=88.41..4276.12 rows=107 width=12) (actual time=0.083..0.098 rows=2 loops=1)
2.	0.008	0.058	↑ 53.5	2	1	→ HashAggregate (cost=88.41..89.48 rows=107 width=8) (actual time=0.058..0.058 rows=2 loops=1)
3.	0.005	0.050	↑ 35.7	3	1	→ Nested Loop (cost=15.01..87.87 rows=107 width=8) (actual time=0.039..0.050 rows=3 loops=1)
4.	0.008	0.036	↑ 35.7	3	1	→ Hash Join (cost=15.01..55.50 rows=107 width=8) (actual time=0.032..0.036 rows=3 loops=1) Hash Cond: (badgegroups_to_topiccategories.category_id = user_topiccategories_counters.category_id)
5.	0.010	0.010	↑ 713.3	3	1	→ Seq Scan on badgegroups_to_topiccategories (cost=0.00..31.40 rows=2140 width=8) (actual time=0.007..0.010 rows=3 loops=1)
6.	0.004	0.018	↑ 3.3	3	1	→ Hash (cost=14.88..14.88 rows=10 width=8) (actual time=0.018..0.018 rows=3 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB
7.	0.006	0.014	↑ 3.3	3	1	→ Bitmap Heap Scan on user_topiccategories_counters (cost=4.33..14.88 rows=10 width=8) (actual time=0.012..0.014 rows=3 loops=1) Recheck Cond: (user_id = 500000)
8.	0.008	0.008	↑ 3.3	3	1	→ Bitmap Index Scan on counters_pkey (cost=0.00..4.33 rows=10 width=0) (actual time=0.008..0.008 rows=3 loops=1) Index Cond: (user_id = 500000)
9.	0.009	0.009	↑ 1.0	1	3	→ Index Scan using badgegroups_pkey on badgegroups (cost=0.00..0.29 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=3) Index Cond: (badgegroup_id = badgegroups_to_topiccategories.badgegroup_id)
10.						SubPlan (forSubquery Scan on foo)
11.	0.004	0.034	↑ 1.0	1	2	→ Limit (cost=39.11..39.12 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=2)
12.	0.020	0.030	↑ 3.0	1	2	→ Sort (cost=39.11..39.12 rows=3 width=8) (actual time=0.015..0.015 rows=1 loops=2) Sort Key: badges.points Sort Method: quicksort Memory: 25kB
13.	0.010	0.010	↑ 1.5	2	2	→ Seq Scan on badges (cost=0.00..39.10 rows=3 width=8) (actual time=0.005..0.005 rows=2 loops=2) Filter: ((points < foo.sum) AND (badgegroup_id = foo.badgegroup_id))

"Subquery Scan on foo  (cost=88.41..4276.12 rows=107 width=12) (actual time=0.083..0.098 rows=2 loops=1)"
"  ->  HashAggregate  (cost=88.41..89.48 rows=107 width=8) (actual time=0.058..0.058 rows=2 loops=1)"
"        ->  Nested Loop  (cost=15.01..87.87 rows=107 width=8) (actual time=0.039..0.050 rows=3 loops=1)"
"              ->  Hash Join  (cost=15.01..55.50 rows=107 width=8) (actual time=0.032..0.036 rows=3 loops=1)"
"                    Hash Cond: (badgegroups_to_topiccategories.category_id = user_topiccategories_counters.category_id)"
"                    ->  Seq Scan on badgegroups_to_topiccategories  (cost=0.00..31.40 rows=2140 width=8) (actual time=0.007..0.010 rows=3 loops=1)"
"                    ->  Hash  (cost=14.88..14.88 rows=10 width=8) (actual time=0.018..0.018 rows=3 loops=1)"
"                          Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"                          ->  Bitmap Heap Scan on user_topiccategories_counters  (cost=4.33..14.88 rows=10 width=8) (actual time=0.012..0.014 rows=3 loops=1)"
"                                Recheck Cond: (user_id = 500000)"
"                                ->  Bitmap Index Scan on counters_pkey  (cost=0.00..4.33 rows=10 width=0) (actual time=0.008..0.008 rows=3 loops=1)"
"                                      Index Cond: (user_id = 500000)"
"              ->  Index Scan using badgegroups_pkey on badgegroups  (cost=0.00..0.29 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=3)"
"                    Index Cond: (badgegroup_id = badgegroups_to_topiccategories.badgegroup_id)"
"  SubPlan 1"
"    ->  Limit  (cost=39.11..39.12 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=2)"
"          ->  Sort  (cost=39.11..39.12 rows=3 width=8) (actual time=0.015..0.015 rows=1 loops=2)"
"                Sort Key: badges.points"
"                Sort Method: quicksort  Memory: 25kB"
"                ->  Seq Scan on badges  (cost=0.00..39.10 rows=3 width=8) (actual time=0.005..0.005 rows=2 loops=2)"
"                      Filter: ((points < foo.sum) AND (badgegroup_id = foo.badgegroup_id))"
"Total runtime: 0.178 ms"

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	0.006 ms	6.1 %
Bitmap Index Scan	1	0.008 ms	8.2 %
Hash	1	0.004 ms	4.1 %
Hash Join	1	0.008 ms	8.2 %
HashAggregate	1	0.008 ms	8.2 %
Index Scan	1	0.009 ms	9.2 %
Limit	1	0.004 ms	4.1 %
Nested Loop	1	0.005 ms	5.1 %
Seq Scan	2	0.020 ms	20.4 %
Sort	1	0.020 ms	20.4 %
Subquery Scan on foo	1	0.006 ms	6.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
badgegroups	1	0.009 ms	9.2 %
Index Scan	1	0.009 ms	100.0 %
badgegroups_to_topiccategories	1	0.010 ms	10.2 %
Seq Scan	1	0.010 ms	100.0 %
badges	1	0.010 ms	10.2 %
Seq Scan	1	0.010 ms	100.0 %
user_topiccategories_counters	1	0.006 ms	6.1 %
Bitmap Heap Scan	1	0.006 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: Vnh

Colorize:

Columns visibility:

Per node type stats

Per table stats