#	exclusive	inclusive	rows x	rows	loops	node
1.	4790.927	14466.334	↓ 1.3	1011861	1	→ Sort (cost=444757.03..446720.49 rows=785382 width=56) (actual time=13495.090..14466.334 rows=1011861 loops=1) Sort Key: "_F2".title Sort Method: external merge Disk: 65360kB
2.	5500.548	9675.407	↓ 1.3	1011861	1	→ Hash Right Join (cost=178573.13..344846.21 rows=785382 width=56) (actual time=3570.329..9675.407 rows=1011861 loops=1) Hash Cond: (("_F2".id)::text = (hierarchy.id)::text)
3.	604.886	604.886	↑ 1.0	1068530	1	→ Seq Scan on dublincore "_F2" (cost=0.00..127656.30 rows=1068530 width=55) (actual time=0.020..604.886 rows=1068530 loops=1)
4.	401.689	3569.973	↓ 1.3	1011861	1	→ Hash (cost=162619.86..162619.86 rows=785382 width=37) (actual time=3569.973..3569.973 rows=1011861 loops=1) Buckets: 32768 Batches: 4 Memory Usage: 17076kB
5.	1541.441	3168.284	↓ 1.3	1011861	1	→ Hash Join (cost=94569.78..162619.86 rows=785382 width=37) (actual time=1272.347..3168.284 rows=1011861 loops=1) Hash Cond: (("_F1".id)::text = (hierarchy.id)::text)
6.	354.853	354.853	↑ 1.0	1068530	1	→ Seq Scan on misc "_F1" (cost=0.00..24380.62 rows=1068530 width=37) (actual time=0.012..354.853 rows=1068530 loops=1) Filter: ((lifecyclestate)::text <> 'deleted'::text)
7.	406.396	1271.990	↑ 1.2	1011861	1	→ Hash (cost=69168.03..69168.03 rows=1250540 width=37) (actual time=1271.990..1271.990 rows=1011861 loops=1) Buckets: 32768 Batches: 8 Memory Usage: 8550kB
8.	865.594	865.594	↑ 1.2	1011861	1	→ Seq Scan on hierarchy (cost=0.00..69168.03 rows=1250540 width=37) (actual time=0.116..865.594 rows=1011861 loops=1) Filter: (((primarytype)::text = ANY ('{Picture,File,Audio,Video,File}'::text[])) OR ('{Asset}'::character varying[] <@ mixintypes))

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=444757.03..446720.49 rows=785382 width=56) (actual time=13495.090..14466.334 rows=1011861 loops=1)
   Sort Key: "_F2".title
   Sort Method: external merge  Disk: 65360kB
   ->  Hash Right Join  (cost=178573.13..344846.21 rows=785382 width=56) (actual time=3570.329..9675.407 rows=1011861 loops=1)
         Hash Cond: (("_F2".id)::text = (hierarchy.id)::text)
         ->  Seq Scan on dublincore "_F2"  (cost=0.00..127656.30 rows=1068530 width=55) (actual time=0.020..604.886 rows=1068530 loops=1)
         ->  Hash  (cost=162619.86..162619.86 rows=785382 width=37) (actual time=3569.973..3569.973 rows=1011861 loops=1)
               Buckets: 32768  Batches: 4  Memory Usage: 17076kB
               ->  Hash Join  (cost=94569.78..162619.86 rows=785382 width=37) (actual time=1272.347..3168.284 rows=1011861 loops=1)
                     Hash Cond: (("_F1".id)::text = (hierarchy.id)::text)
                     ->  Seq Scan on misc "_F1"  (cost=0.00..24380.62 rows=1068530 width=37) (actual time=0.012..354.853 rows=1068530 loops=1)
                           Filter: ((lifecyclestate)::text <> 'deleted'::text)
                     ->  Hash  (cost=69168.03..69168.03 rows=1250540 width=37) (actual time=1271.990..1271.990 rows=1011861 loops=1)
                           Buckets: 32768  Batches: 8  Memory Usage: 8550kB
                           ->  Seq Scan on hierarchy  (cost=0.00..69168.03 rows=1250540 width=37) (actual time=0.116..865.594 rows=1011861 loops=1)
                                 Filter: (((primarytype)::text = ANY ('{Picture,File,Audio,Video,File}'::text[])) OR ('{Asset}'::character varying[] <@ mixintypes))

Per node type stats

node type	count	sum of times	% of query
Hash	2	808.085 ms	5.6 %
Hash Join	1	1541.441 ms	10.7 %
Hash Right Join	1	5500.548 ms	38.0 %
Seq Scan	3	1825.333 ms	12.6 %
Sort	1	4790.927 ms	33.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
dublincore	1	604.886 ms	4.2 %
Seq Scan	1	604.886 ms	100.0 %
hierarchy	1	865.594 ms	6.0 %
Seq Scan	1	865.594 ms	100.0 %
misc	1	354.853 ms	2.5 %
Seq Scan	1	354.853 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: A8ee

Colorize:

Columns visibility:

Per node type stats

Per table stats