Result: UGE

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
0.480	4.119	↓ 1.1	234	1	→ Unique (cost=1444.92..1447.11 rows=219 width=19) (actual time=3.435..4.119 rows=234 loops=1)
0.530	3.639	↓ 1.1	234	1	→ Sort (cost=1444.92..1445.47 rows=219 width=19) (actual time=3.433..3.639 rows=234 loops=1) Sort Key: t.id Sort Method: quicksort Memory: 40kB
0.570	3.109	↓ 1.1	234	1	→ Merge Left Join (cost=1434.58..1436.41 rows=219 width=19) (actual time=2.268..3.109 rows=234 loops=1) Merge Cond: (t.theme = r.theme) Join Filter: ((r.id <> a.id) AND (r.id <> t.album_id) AND (r.collection_id = a.collection_id))
0.583	2.266	↓ 1.1	234	1	→ Sort (cost=1278.10..1278.65 rows=219 width=23) (actual time=2.059..2.266 rows=234 loops=1) Sort Key: t.theme Sort Method: quicksort Memory: 43kB
0.582	1.683	↓ 1.1	234	1	→ Nested Loop (cost=0.00..1269.58 rows=219 width=23) (actual time=0.134..1.683 rows=234 loops=1)
0.152	0.321	↓ 1.1	78	1	→ Nested Loop (cost=0.00..163.22 rows=73 width=12) (actual time=0.085..0.321 rows=78 loops=1)
0.044	0.044	↑ 1.0	1	1	→ Index Scan using collections_pkey on collections c (cost=0.00..8.27 rows=1 width=4) (actual time=0.043..0.044 rows=1 loops=1) Index Cond: (id = 12)
0.125	0.125	↓ 1.1	78	1	→ Index Scan using i_albums_collection_id on albums a (cost=0.00..154.22 rows=73 width=8) (actual time=0.037..0.125 rows=78 loops=1) Index Cond: (a.collection_id = 12)
0.780	0.780	↑ 1.0	3	78	→ Index Scan using i_tracks_album_id on tracks t (cost=0.00..15.12 rows=3 width=11) (actual time=0.004..0.010 rows=3 loops=78) Index Cond: (t.album_id = a.id)
0.181	0.273	↓ 1.1	81	1	→ Sort (cost=156.48..156.66 rows=73 width=10) (actual time=0.200..0.273 rows=81 loops=1) Sort Key: r.theme Sort Method: quicksort Memory: 28kB
0.092	0.092	↓ 1.1	78	1	→ Index Scan using i_albums_collection_id on albums r (cost=0.00..154.22 rows=73 width=10) (actual time=0.007..0.092 rows=78 loops=1) Index Cond: (collection_id = 12)

 Unique  (cost=1444.92..1447.11 rows=219 width=19) (actual time=3.435..4.119 rows=234 loops=1)
   ->  Sort  (cost=1444.92..1445.47 rows=219 width=19) (actual time=3.433..3.639 rows=234 loops=1)
         Sort Key: t.id
         Sort Method:  quicksort  Memory: 40kB
         ->  Merge Left Join  (cost=1434.58..1436.41 rows=219 width=19) (actual time=2.268..3.109 rows=234 loops=1)
               Merge Cond: (t.theme = r.theme)
               Join Filter: ((r.id <> a.id) AND (r.id <> t.album_id) AND (r.collection_id = a.collection_id))
               ->  Sort  (cost=1278.10..1278.65 rows=219 width=23) (actual time=2.059..2.266 rows=234 loops=1)
                     Sort Key: t.theme
                     Sort Method:  quicksort  Memory: 43kB
                     ->  Nested Loop  (cost=0.00..1269.58 rows=219 width=23) (actual time=0.134..1.683 rows=234 loops=1)
                           ->  Nested Loop  (cost=0.00..163.22 rows=73 width=12) (actual time=0.085..0.321 rows=78 loops=1)
                                 ->  Index Scan using collections_pkey on collections c  (cost=0.00..8.27 rows=1 width=4) (actual time=0.043..0.044 rows=1 loops=1)
                                       Index Cond: (id = 12)
                                 ->  Index Scan using i_albums_collection_id on albums a  (cost=0.00..154.22 rows=73 width=8) (actual time=0.037..0.125 rows=78 loops=1)
                                       Index Cond: (a.collection_id = 12)
                           ->  Index Scan using i_tracks_album_id on tracks t  (cost=0.00..15.12 rows=3 width=11) (actual time=0.004..0.010 rows=3 loops=78)
                                 Index Cond: (t.album_id = a.id)
               ->  Sort  (cost=156.48..156.66 rows=73 width=10) (actual time=0.200..0.273 rows=81 loops=1)
                     Sort Key: r.theme
                     Sort Method:  quicksort  Memory: 28kB
                     ->  Index Scan using i_albums_collection_id on albums r  (cost=0.00..154.22 rows=73 width=10) (actual time=0.007..0.092 rows=78 loops=1)
                           Index Cond: (collection_id = 12)
 Total runtime: 4.399 ms
(24 rows)

Per node type stats

node type	count	sum of times	% of query
Index Scan	4	1.041 ms	25.3 %
Merge Left Join	1	0.570 ms	13.8 %
Nested Loop	2	0.734 ms	17.8 %
Sort	3	1.294 ms	31.4 %
Unique	1	0.480 ms	11.7 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
albums	2	0.217 ms	5.3 %
Index Scan	2	0.217 ms	100.0 %
collections	1	0.044 ms	1.1 %
Index Scan	1	0.044 ms	100.0 %
tracks	1	0.780 ms	18.9 %
Index Scan	1	0.780 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: UGE

Colorize:

Columns visibility:

Per node type stats

Per table stats