Result: s8

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
208.664	4204.813	↑ 4.8	234	1	→ Subquery Scan v_test_bad (cost=72243.75..76167.49 rows=1121 width=48) (actual time=3327.831..4204.813 rows=234 loops=1) Filter: (v_test_bad.collection_id = 12) Total runtime: 4218.086 ms
426.103	3996.149	↑ 1.0	224214	1	→ Unique (cost=72243.75..73364.82 rows=224214 width=19) (actual time=3324.698..3996.149 rows=224214 loops=1)
736.718	3570.046	↑ 1.0	224214	1	→ Sort (cost=72243.75..72804.28 rows=224214 width=19) (actual time=3324.694..3570.046 rows=224214 loops=1) Sort Key: t.id Sort Method: external merge Disk: 7560kB
544.268	2833.328	↑ 1.0	224214	1	→ Merge Left Join (cost=45132.71..47718.27 rows=224214 width=19) (actual time=1942.223..2833.328 rows=224214 loops=1) Merge Cond: ((a.collection_id = r.collection_id) AND (t.theme = r.theme)) Join Filter: ((r.id <> a.id) AND (r.id <> t.album_id))
853.243	2011.315	↑ 1.0	224214	1	→ Sort (cost=37607.45..38167.99 rows=224214 width=23) (actual time=1736.091..2011.315 rows=224214 loops=1) Sort Key: c.id, t.theme Sort Method: external merge Disk: 8464kB
632.578	1158.072	↑ 1.0	224214	1	→ Hash Join (cost=3464.75..13081.97 rows=224214 width=23) (actual time=307.411..1158.072 rows=224214 loops=1) Hash Cond: (t.album_id = a.id)
218.121	218.121	↑ 1.0	224214	1	→ Seq Scan on tracks t (cost=0.00..4292.14 rows=224214 width=11) (actual time=0.007..218.121 rows=224214 loops=1)
79.601	307.373	↑ 1.0	74738	1	→ Hash (cost=2530.53..2530.53 rows=74738 width=12) (actual time=307.373..307.373 rows=74738 loops=1)
155.004	227.772	↑ 1.0	74738	1	→ Hash Join (cost=27.50..2530.53 rows=74738 width=12) (actual time=2.076..227.772 rows=74738 loops=1) Hash Cond: (a.collection_id = c.id)
70.707	70.707	↑ 1.0	74738	1	→ Seq Scan on albums a (cost=0.00..1475.38 rows=74738 width=8) (actual time=0.005..70.707 rows=74738 loops=1)
1.056	2.061	↑ 1.0	1000	1	→ Hash (cost=15.00..15.00 rows=1000 width=4) (actual time=2.061..2.061 rows=1000 loops=1)
1.005	1.005	↑ 1.0	1000	1	→ Seq Scan on collections c (cost=0.00..15.00 rows=1000 width=4) (actual time=0.007..1.005 rows=1000 loops=1)
209.318	277.745	↓ 1.0	77749	1	→ Sort (cost=7525.25..7712.10 rows=74738 width=10) (actual time=206.107..277.745 rows=77749 loops=1) Sort Key: r.collection_id, r.theme Sort Method: quicksort Memory: 6576kB
68.427	68.427	↑ 1.0	74738	1	→ Seq Scan on albums r (cost=0.00..1475.38 rows=74738 width=10) (actual time=0.013..68.427 rows=74738 loops=1)

Subquery Scan v_test_bad  (cost=72243.75..76167.49 rows=1121 width=48) (actual time=3327.831..4204.813 rows=234 loops=1)
   Filter: (v_test_bad.collection_id = 12)
   ->  Unique  (cost=72243.75..73364.82 rows=224214 width=19) (actual time=3324.698..3996.149 rows=224214 loops=1)
         ->  Sort  (cost=72243.75..72804.28 rows=224214 width=19) (actual time=3324.694..3570.046 rows=224214 loops=1)
               Sort Key: t.id
               Sort Method:  external merge  Disk: 7560kB
               ->  Merge Left Join  (cost=45132.71..47718.27 rows=224214 width=19) (actual time=1942.223..2833.328 rows=224214 loops=1)
                     Merge Cond: ((a.collection_id = r.collection_id) AND (t.theme = r.theme))
                     Join Filter: ((r.id <> a.id) AND (r.id <> t.album_id))
                     ->  Sort  (cost=37607.45..38167.99 rows=224214 width=23) (actual time=1736.091..2011.315 rows=224214 loops=1)
                           Sort Key: c.id, t.theme
                           Sort Method:  external merge  Disk: 8464kB
                           ->  Hash Join  (cost=3464.75..13081.97 rows=224214 width=23) (actual time=307.411..1158.072 rows=224214 loops=1)
                                 Hash Cond: (t.album_id = a.id)
                                 ->  Seq Scan on tracks t  (cost=0.00..4292.14 rows=224214 width=11) (actual time=0.007..218.121 rows=224214 loops=1)
                                 ->  Hash  (cost=2530.53..2530.53 rows=74738 width=12) (actual time=307.373..307.373 rows=74738 loops=1)
                                       ->  Hash Join  (cost=27.50..2530.53 rows=74738 width=12) (actual time=2.076..227.772 rows=74738 loops=1)
                                             Hash Cond: (a.collection_id = c.id)
                                             ->  Seq Scan on albums a  (cost=0.00..1475.38 rows=74738 width=8) (actual time=0.005..70.707 rows=74738 loops=1)
                                             ->  Hash  (cost=15.00..15.00 rows=1000 width=4) (actual time=2.061..2.061 rows=1000 loops=1)
                                                   ->  Seq Scan on collections c  (cost=0.00..15.00 rows=1000 width=4) (actual time=0.007..1.005 rows=1000 loops=1)
                     ->  Sort  (cost=7525.25..7712.10 rows=74738 width=10) (actual time=206.107..277.745 rows=77749 loops=1)
                           Sort Key: r.collection_id, r.theme
                           Sort Method:  quicksort  Memory: 6576kB
                           ->  Seq Scan on albums r  (cost=0.00..1475.38 rows=74738 width=10) (actual time=0.013..68.427 rows=74738 loops=1)
 Total runtime: 4218.086 ms

Per node type stats

node type	count	sum of times	% of query
Hash	2	80.657 ms	1.9 %
Hash Join	2	787.582 ms	18.7 %
Merge Left Join	1	544.268 ms	12.9 %
Seq Scan	4	358.260 ms	8.5 %
Sort	3	1799.279 ms	42.8 %
Subquery Scan v_test_bad	1	208.664 ms	5.0 %
Unique	1	426.103 ms	10.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
albums	2	139.134 ms	3.3 %
Seq Scan	2	139.134 ms	100.0 %
collections	1	1.005 ms	0.0 %
Seq Scan	1	1.005 ms	100.0 %
tracks	1	218.121 ms	5.2 %
Seq Scan	1	218.121 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: s8

Colorize:

Columns visibility:

Per node type stats

Per table stats