#	exclusive	inclusive	rows x	rows	loops	node
1.	2463.159	13884.259	↓ 25366.1	304393	1	→ Sort (cost=27008.22..27008.25 rows=12 width=67) (actual time=13722.868..13884.259 rows=304393 loops=1) Sort Key: "_F2".title Sort Method: external merge Disk: 20432kB
2.	249.792	11421.100	↓ 25366.1	304393	1	→ Nested Loop Left Join (cost=24782.39..27008.00 rows=12 width=67) (actual time=706.393..11421.100 rows=304393 loops=1)
3.	216.402	8127.378	↓ 25366.1	304393	1	→ Nested Loop (cost=24782.39..27002.46 rows=12 width=37) (actual time=706.341..8127.378 rows=304393 loops=1)
4.	228.888	4866.256	↓ 5160.5	304472	1	→ Nested Loop (cost=24782.39..26744.54 rows=59 width=74) (actual time=706.295..4866.256 rows=304472 loops=1)
5.	387.036	832.408	↓ 760.4	317080	1	→ HashAggregate (cost=24782.39..24786.56 rows=417 width=37) (actual time=706.217..832.408 rows=317080 loops=1)
6.	445.372	445.372	↓ 760.4	317080	1	→ Seq Scan on ancestors (cost=0.00..24781.35 rows=417 width=37) (actual time=0.047..445.372 rows=317080 loops=1) Filter: ('{83119cd4-a94c-4dc6-ba95-4620cfd525c8}'::character varying[] <@ ancestors)
7.	3804.960	3804.960	↑ 1.0	1	317080	→ Index Scan using hierarchy_pk on hierarchy (cost=0.00..4.68 rows=1 width=37) (actual time=0.012..0.012 rows=1 loops=317080) Index Cond: ((hierarchy.id)::text = (ancestors.id)::text) Filter: (((hierarchy.primarytype)::text = ANY ('{Picture,File,Audio,Video}'::text[])) OR ('{Asset}'::character varying[] <@ hierarchy.mixintypes))
8.	3044.720	3044.720	↑ 1.0	1	304472	→ Index Scan using misc_pk on misc "_F1" (cost=0.00..4.36 rows=1 width=37) (actual time=0.010..0.010 rows=1 loops=304472) Index Cond: (("_F1".id)::text = (hierarchy.id)::text) Filter: (("_F1".lifecyclestate)::text <> 'deleted'::text)
9.	3043.930	3043.930	↑ 1.0	1	304393	→ Index Scan using dublincore_pk on dublincore "_F2" (cost=0.00..0.45 rows=1 width=67) (actual time=0.010..0.010 rows=1 loops=304393) Index Cond: ((hierarchy.id)::text = ("_F2".id)::text)

Sort  (cost=27008.22..27008.25 rows=12 width=67) (actual time=13722.868..13884.259 rows=304393 loops=1)
  Sort Key: "_F2".title
  Sort Method:  external merge  Disk: 20432kB
  ->  Nested Loop Left Join  (cost=24782.39..27008.00 rows=12 width=67) (actual time=706.393..11421.100 rows=304393 loops=1)
        ->  Nested Loop  (cost=24782.39..27002.46 rows=12 width=37) (actual time=706.341..8127.378 rows=304393 loops=1)
              ->  Nested Loop  (cost=24782.39..26744.54 rows=59 width=74) (actual time=706.295..4866.256 rows=304472 loops=1)
                    ->  HashAggregate  (cost=24782.39..24786.56 rows=417 width=37) (actual time=706.217..832.408 rows=317080 loops=1)
                          ->  Seq Scan on ancestors  (cost=0.00..24781.35 rows=417 width=37) (actual time=0.047..445.372 rows=317080 loops=1)
                                Filter: ('{83119cd4-a94c-4dc6-ba95-4620cfd525c8}'::character varying[] <@ ancestors)
                    ->  Index Scan using hierarchy_pk on hierarchy  (cost=0.00..4.68 rows=1 width=37) (actual time=0.012..0.012 rows=1 loops=317080)
                          Index Cond: ((hierarchy.id)::text = (ancestors.id)::text)
                          Filter: (((hierarchy.primarytype)::text = ANY ('{Picture,File,Audio,Video}'::text[])) OR ('{Asset}'::character varying[] <@ hierarchy.mixintypes))
              ->  Index Scan using misc_pk on misc "_F1"  (cost=0.00..4.36 rows=1 width=37) (actual time=0.010..0.010 rows=1 loops=304472)
                    Index Cond: (("_F1".id)::text = (hierarchy.id)::text)
                    Filter: (("_F1".lifecyclestate)::text <> 'deleted'::text)
        ->  Index Scan using dublincore_pk on dublincore "_F2"  (cost=0.00..0.45 rows=1 width=67) (actual time=0.010..0.010 rows=1 loops=304393)
              Index Cond: ((hierarchy.id)::text = ("_F2".id)::text)
Total runtime: 13921.527 ms
18 row(s)
Total runtime: 13,928.222 ms

Per node type stats

node type	count	sum of times	% of query
HashAggregate	1	387.036 ms	2.8 %
Index Scan	3	9893.610 ms	71.3 %
Nested Loop	2	445.290 ms	3.2 %
Nested Loop Left Join	1	249.792 ms	1.8 %
Seq Scan	1	445.372 ms	3.2 %
Sort	1	2463.159 ms	17.7 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
ancestors	1	445.372 ms	3.2 %
Seq Scan	1	445.372 ms	100.0 %
dublincore	1	3043.930 ms	21.9 %
Index Scan	1	3043.930 ms	100.0 %
hierarchy	1	3804.960 ms	27.4 %
Index Scan	1	3804.960 ms	100.0 %
misc	1	3044.720 ms	21.9 %
Index Scan	1	3044.720 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: kgB

Colorize:

Columns visibility:

Per node type stats

Per table stats