#	exclusive	inclusive	rows x	rows	loops	node
1.	0.625	557.567	↓ 1.0	753	1	→ Sort (cost=8,245.75..8,247.62 rows=750 width=99) (actual time=557.483..557.567 rows=753 loops=1) Sort Key: (count(post.id)) DESC Sort Method: quicksort Memory: 161kB
2.	221.894	556.942	↓ 1.0	753	1	→ HashAggregate (cost=8,202.43..8,209.93 rows=750 width=99) (actual time=556.738..556.942 rows=753 loops=1) Group Key: card.id
3.	210.183	335.048	↓ 10.3	890,723	1	→ Hash Join (cost=6,006.98..7,770.48 rows=86,390 width=95) (actual time=105.938..335.048 rows=890,723 loops=1) Hash Cond: (post_to_tag_link_1.tag_id = tag.id)
4.	14.526	120.654	↓ 2.0	32,633	1	→ Hash Join (cost=5,901.77..6,658.63 rows=16,316 width=8) (actual time=101.701..120.654 rows=32,633 loops=1) Hash Cond: (post_to_tag_link_1.post_id = post.id)
5.	4.600	4.600	↑ 1.0	32,633	1	→ Seq Scan on post_to_tag_link post_to_tag_link_1 (cost=0.00..471.33 rows=32,633 width=8) (actual time=0.018..4.600 rows=32,633 loops=1)
6.	9.394	101.528	↓ 2.0	24,491	1	→ Hash (cost=5,748.69..5,748.69 rows=12,246 width=8) (actual time=101.528..101.528 rows=24,491 loops=1) Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1213kB
7.	28.862	92.134	↓ 2.0	24,491	1	→ Hash Join (cost=1,336.75..5,748.69 rows=12,246 width=8) (actual time=40.041..92.134 rows=24,491 loops=1) Hash Cond: (post.id = a.post_id)
8.	23.517	23.517	↑ 1.0	24,492	1	→ Seq Scan on post (cost=0.00..4,078.92 rows=24,492 width=4) (actual time=0.022..23.517 rows=24,492 loops=1)
9.	7.085	39.755	↓ 1.0	24,491	1	→ Hash (cost=1,035.27..1,035.27 rows=24,118 width=4) (actual time=39.755..39.755 rows=24,491 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1118kB
10.	4.644	32.670	↓ 1.0	24,491	1	→ Subquery Scan on a (cost=552.91..1,035.27 rows=24,118 width=4) (actual time=21.483..32.670 rows=24,491 loops=1)
11.	21.318	28.026	↓ 1.0	24,491	1	→ HashAggregate (cost=552.91..794.09 rows=24,118 width=36) (actual time=21.482..28.026 rows=24,491 loops=1) Group Key: post_to_tag_link.post_id
12.	6.708	6.708	↑ 1.0	32,633	1	→ Seq Scan on post_to_tag_link (cost=0.00..471.33 rows=32,633 width=4) (actual time=0.015..6.708 rows=32,633 loops=1)
13.	0.848	4.211	↓ 1.1	1,318	1	→ Hash (cost=90.39..90.39 rows=1,186 width=99) (actual time=4.211..4.211 rows=1,318 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 196kB
14.	0.728	3.363	↓ 1.1	1,318	1	→ Hash Join (cost=39.91..90.39 rows=1,186 width=99) (actual time=1.446..3.363 rows=1,318 loops=1) Hash Cond: (card_to_tag_link_1.tag_id = tag.id)
15.	0.862	2.300	↓ 1.1	1,318	1	→ Hash Join (cost=29.88..64.04 rows=1,186 width=95) (actual time=1.085..2.300 rows=1,318 loops=1) Hash Cond: (card_to_tag_link_1.card_id = card.id)
16.	0.422	0.422	↓ 1.1	1,318	1	→ Seq Scan on card_to_tag_link card_to_tag_link_1 (cost=0.00..17.86 rows=1,186 width=8) (actual time=0.042..0.422 rows=1,318 loops=1)
17.	0.486	1.016	↓ 1.0	757	1	→ Hash (cost=20.50..20.50 rows=750 width=91) (actual time=1.016..1.016 rows=757 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 104kB
18.	0.530	0.530	↓ 1.0	757	1	→ Seq Scan on card (cost=0.00..20.50 rows=750 width=91) (actual time=0.023..0.530 rows=757 loops=1)
19.	0.103	0.335	↑ 1.0	224	1	→ Hash (cost=7.24..7.24 rows=224 width=4) (actual time=0.335..0.335 rows=224 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 16kB
20.	0.232	0.232	↑ 1.0	224	1	→ Seq Scan on tag (cost=0.00..7.24 rows=224 width=4) (actual time=0.016..0.232 rows=224 loops=1)

"Sort  (cost=8245.75..8247.62 rows=750 width=99) (actual time=557.483..557.567 rows=753 loops=1)"
"  Sort Key: (count(post.id)) DESC"
"  Sort Method: quicksort  Memory: 161kB"
"  ->  HashAggregate  (cost=8202.43..8209.93 rows=750 width=99) (actual time=556.738..556.942 rows=753 loops=1)"
"        Group Key: card.id"
"        ->  Hash Join  (cost=6006.98..7770.48 rows=86390 width=95) (actual time=105.938..335.048 rows=890723 loops=1)"
"              Hash Cond: (post_to_tag_link_1.tag_id = tag.id)"
"              ->  Hash Join  (cost=5901.77..6658.63 rows=16316 width=8) (actual time=101.701..120.654 rows=32633 loops=1)"
"                    Hash Cond: (post_to_tag_link_1.post_id = post.id)"
"                    ->  Seq Scan on post_to_tag_link post_to_tag_link_1  (cost=0.00..471.33 rows=32633 width=8) (actual time=0.018..4.600 rows=32633 loops=1)"
"                    ->  Hash  (cost=5748.69..5748.69 rows=12246 width=8) (actual time=101.528..101.528 rows=24491 loops=1)"
"                          Buckets: 32768 (originally 16384)  Batches: 1 (originally 1)  Memory Usage: 1213kB"
"                          ->  Hash Join  (cost=1336.75..5748.69 rows=12246 width=8) (actual time=40.041..92.134 rows=24491 loops=1)"
"                                Hash Cond: (post.id = a.post_id)"
"                                ->  Seq Scan on post  (cost=0.00..4078.92 rows=24492 width=4) (actual time=0.022..23.517 rows=24492 loops=1)"
"                                ->  Hash  (cost=1035.27..1035.27 rows=24118 width=4) (actual time=39.755..39.755 rows=24491 loops=1)"
"                                      Buckets: 32768  Batches: 1  Memory Usage: 1118kB"
"                                      ->  Subquery Scan on a  (cost=552.91..1035.27 rows=24118 width=4) (actual time=21.483..32.670 rows=24491 loops=1)"
"                                            ->  HashAggregate  (cost=552.91..794.09 rows=24118 width=36) (actual time=21.482..28.026 rows=24491 loops=1)"
"                                                  Group Key: post_to_tag_link.post_id"
"                                                  ->  Seq Scan on post_to_tag_link  (cost=0.00..471.33 rows=32633 width=4) (actual time=0.015..6.708 rows=32633 loops=1)"
"              ->  Hash  (cost=90.39..90.39 rows=1186 width=99) (actual time=4.211..4.211 rows=1318 loops=1)"
"                    Buckets: 2048  Batches: 1  Memory Usage: 196kB"
"                    ->  Hash Join  (cost=39.91..90.39 rows=1186 width=99) (actual time=1.446..3.363 rows=1318 loops=1)"
"                          Hash Cond: (card_to_tag_link_1.tag_id = tag.id)"
"                          ->  Hash Join  (cost=29.88..64.04 rows=1186 width=95) (actual time=1.085..2.300 rows=1318 loops=1)"
"                                Hash Cond: (card_to_tag_link_1.card_id = card.id)"
"                                ->  Seq Scan on card_to_tag_link card_to_tag_link_1  (cost=0.00..17.86 rows=1186 width=8) (actual time=0.042..0.422 rows=1318 loops=1)"
"                                ->  Hash  (cost=20.50..20.50 rows=750 width=91) (actual time=1.016..1.016 rows=757 loops=1)"
"                                      Buckets: 1024  Batches: 1  Memory Usage: 104kB"
"                                      ->  Seq Scan on card  (cost=0.00..20.50 rows=750 width=91) (actual time=0.023..0.530 rows=757 loops=1)"
"                          ->  Hash  (cost=7.24..7.24 rows=224 width=4) (actual time=0.335..0.335 rows=224 loops=1)"
"                                Buckets: 1024  Batches: 1  Memory Usage: 16kB"
"                                ->  Seq Scan on tag  (cost=0.00..7.24 rows=224 width=4) (actual time=0.016..0.232 rows=224 loops=1)"
"Planning time: 5.204 ms"
"Execution time: 558.566 ms"

Per node type stats

node type	count	sum of times	% of query
Hash	5	17.916 ms	3.2 %
Hash Join	5	255.161 ms	45.8 %
HashAggregate	2	243.212 ms	43.6 %
Seq Scan	6	36.009 ms	6.5 %
Sort	1	0.625 ms	0.1 %
Subquery Scan	1	4.644 ms	0.8 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
card	1	0.530 ms	0.1 %
Seq Scan	1	0.530 ms	100.0 %
card_to_tag_link	1	0.422 ms	0.1 %
Seq Scan	1	0.422 ms	100.0 %
post	1	23.517 ms	4.2 %
Seq Scan	1	23.517 ms	100.0 %
post_to_tag_link	2	11.308 ms	2.0 %
Seq Scan	2	11.308 ms	100.0 %
tag	1	0.232 ms	0.0 %
Seq Scan	1	0.232 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8SjR

Color mode:

Visible columns:

Per node type stats

Per table stats