#	exclusive	inclusive	rows x	rows	loops	node
1.	5982.437	40931.202	↓ 3066.9	1005931	1	→ Sort (cost=94310.33..94311.15 rows=328 width=56) (actual time=39970.546..40931.202 rows=1005931 loops=1) Sort Key: "_F2".title Sort Method: external merge Disk: 64752kB
2.	893.477	34948.765	↓ 3066.9	1005931	1	→ Nested Loop Left Join (cost=82904.45..94296.62 rows=328 width=56) (actual time=1910.467..34948.765 rows=1005931 loops=1)
3.	246.807	25001.909	↓ 3066.9	1005931	1	→ Nested Loop (cost=82904.45..93910.05 rows=328 width=37) (actual time=1910.450..25001.909 rows=1005931 loops=1)
4.	377.216	14695.792	↓ 1923.4	1005931	1	→ Nested Loop (cost=82904.45..90465.64 rows=523 width=74) (actual time=1910.429..14695.792 rows=1005931 loops=1)
5.	1662.652	2633.463	↓ 989.1	1062283	1	→ HashAggregate (cost=82904.45..82915.19 rows=1074 width=37) (actual time=1910.377..2633.463 rows=1062283 loops=1)
6.	970.811	970.811	↓ 989.1	1062283	1	→ Seq Scan on ancestors (cost=0.00..82901.76 rows=1074 width=37) (actual time=0.017..970.811 rows=1062283 loops=1) Filter: ('{1a12c0ec-4728-44d5-902c-a58241d13634}'::character varying[] <@ ancestors)
7.	11685.113	11685.113	↑ 1.0	1	1062283	→ Index Scan using hierarchy_pk on hierarchy (cost=0.00..7.02 rows=1 width=37) (actual time=0.010..0.011 rows=1 loops=1062283) Index Cond: ((id)::text = (ancestors.id)::text) Filter: (((primarytype)::text = ANY ('{Picture,File,Audio,Video}'::text[])) OR ('{Asset}'::character varying[] <@ mixintypes))
8.	10059.310	10059.310	↑ 1.0	1	1005931	→ Index Scan using misc_pk on misc "_F1" (cost=0.00..6.57 rows=1 width=37) (actual time=0.009..0.010 rows=1 loops=1005931) Index Cond: ((id)::text = (hierarchy.id)::text) Filter: ((lifecyclestate)::text <> 'deleted'::text)
9.	9053.379	9053.379	↑ 1.0	1	1005931	→ Index Scan using dublincore_pk on dublincore "_F2" (cost=0.00..1.17 rows=1 width=55) (actual time=0.009..0.009 rows=1 loops=1005931) Index Cond: ((hierarchy.id)::text = (id)::text)

----------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=94310.33..94311.15 rows=328 width=56) (actual time=39970.546..40931.202 rows=1005931 loops=1)
   Sort Key: "_F2".title
   Sort Method: external merge  Disk: 64752kB
   ->  Nested Loop Left Join  (cost=82904.45..94296.62 rows=328 width=56) (actual time=1910.467..34948.765 rows=1005931 loops=1)
         ->  Nested Loop  (cost=82904.45..93910.05 rows=328 width=37) (actual time=1910.450..25001.909 rows=1005931 loops=1)
               ->  Nested Loop  (cost=82904.45..90465.64 rows=523 width=74) (actual time=1910.429..14695.792 rows=1005931 loops=1)
                     ->  HashAggregate  (cost=82904.45..82915.19 rows=1074 width=37) (actual time=1910.377..2633.463 rows=1062283 loops=1)
                           ->  Seq Scan on ancestors  (cost=0.00..82901.76 rows=1074 width=37) (actual time=0.017..970.811 rows=1062283 loops=1)
                                 Filter: ('{1a12c0ec-4728-44d5-902c-a58241d13634}'::character varying[] <@ ancestors)
                     ->  Index Scan using hierarchy_pk on hierarchy  (cost=0.00..7.02 rows=1 width=37) (actual time=0.010..0.011 rows=1 loops=1062283)
                           Index Cond: ((id)::text = (ancestors.id)::text)
                           Filter: (((primarytype)::text = ANY ('{Picture,File,Audio,Video}'::text[])) OR ('{Asset}'::character varying[] <@ mixintypes))
               ->  Index Scan using misc_pk on misc "_F1"  (cost=0.00..6.57 rows=1 width=37) (actual time=0.009..0.010 rows=1 loops=1005931)
                     Index Cond: ((id)::text = (hierarchy.id)::text)
                     Filter: ((lifecyclestate)::text <> 'deleted'::text)
         ->  Index Scan using dublincore_pk on dublincore "_F2"  (cost=0.00..1.17 rows=1 width=55) (actual time=0.009..0.009 rows=1 loops=1005931)
               Index Cond: ((hierarchy.id)::text = (id)::text)
 Total runtime: 41057.628 ms
(18 rows)

Per node type stats

node type	count	sum of times	% of query
HashAggregate	1	1662.652 ms	4.1 %
Index Scan	3	30797.802 ms	75.2 %
Nested Loop	2	624.023 ms	1.5 %
Nested Loop Left Join	1	893.477 ms	2.2 %
Seq Scan	1	970.811 ms	2.4 %
Sort	1	5982.437 ms	14.6 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
ancestors	1	970.811 ms	2.4 %
Seq Scan	1	970.811 ms	100.0 %
dublincore	1	9053.379 ms	22.1 %
Index Scan	1	9053.379 ms	100.0 %
hierarchy	1	11685.113 ms	28.5 %
Index Scan	1	11685.113 ms	100.0 %
misc	1	10059.310 ms	24.6 %
Index Scan	1	10059.310 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: bCc

Colorize:

Columns visibility:

Per node type stats

Per table stats