#	exclusive	inclusive	rows x	rows	loops	node
1.	2466.222	14528.065	↓ 304390.0	304390	1	→ Sort (cost=26819.69..26819.70 rows=1 width=67) (actual time=14366.814..14528.065 rows=304390 loops=1) Sort Key: "_F2".title Sort Method: external merge Disk: 20432kB
2.	208.810	12061.843	↓ 304390.0	304390	1	→ Nested Loop (cost=24782.39..26819.68 rows=1 width=67) (actual time=761.713..12061.843 rows=304390 loops=1)
3.	248.602	8808.343	↓ 304469.0	304469	1	→ Nested Loop Left Join (cost=24782.39..26815.31 rows=1 width=104) (actual time=761.655..8808.343 rows=304469 loops=1)
4.	189.317	5515.051	↓ 304469.0	304469	1	→ Nested Loop (cost=24782.39..26811.78 rows=1 width=74) (actual time=761.601..5515.051 rows=304469 loops=1)
5.	404.067	886.656	↓ 760.4	317077	1	→ HashAggregate (cost=24782.39..24786.56 rows=417 width=37) (actual time=761.497..886.656 rows=317077 loops=1)
6.	482.589	482.589	↓ 760.4	317077	1	→ Seq Scan on ancestors (cost=0.00..24781.35 rows=417 width=37) (actual time=0.031..482.589 rows=317077 loops=1) Filter: ('{83119cd4-a94c-4dc6-ba95-4620cfd525c8}'::character varying[] <@ ancestors)
7.	4439.078	4439.078	↑ 1.0	1	317077	→ Index Scan using hierarchy_pk on hierarchy (cost=0.00..4.84 rows=1 width=37) (actual time=0.014..0.014 rows=1 loops=317077) Index Cond: ((hierarchy.id)::text = (ancestors.id)::text) Filter: ((((hierarchy.primarytype)::text = ANY ('{Picture,File,Audio,Video}'::text[])) OR ('{Asset}'::character varying[] <@ hierarchy.mixintypes)) AND ((hierarchy.primarytype)::text = ANY ('{RelationSearch,PublicationRelation,Tag,Tagging,CommentRelation,search_results,Thread,Artwork,Comment,QueryNav,TaskList,DefaultRelation,FilterQuery,Video,MailMessage,ContextualLink,Post,AdministrativeStatus,Picture,Relation,Document,FacetedSearch,File,AdvancedSearch,FacetedSearchDefault,Note,TaskDoc,UserProfile,CollectionSurvey,Audio,BasicAuditSearch,BlogPost}'::text[])) AND ((hierarchy.primarytype)::text = ANY ('{SectionRoot,ArtworkLibrary,ImportRoot,Workspace,RelationSearch,BlogSite,Thread,TemplateRoot,Domain,Artwork,WorkspaceRoot,ImportSet,QueryNav,WebPage,FilterQuery,Video,MailMessage,Forum,PictureBook,ContextualLink,Picture,Relation,Document,Folder,FacetedSearch,ImportFolder,File,AdvancedSearch,WebSite,FacetedSearchDefault,Note,Section,Root,MailFolder,CollectionSurvey,OrderedFolder,Audio,BasicAuditSearch,BlogPost}'::text[])) AND ((hierarchy.primarytype)::text = ANY ('{ArtworkLibrary,SectionRoot,Workspace,ImportRoot,RelationSearch,BlogSite,ManagementRoot,Tag,AdministrativeStatusContainer,Thread,search_results,Space,TemplateRoot,Domain,Artwork,WorkspaceRoot,Comment,ImportSet,QueryNav,TaskList,WebPage,FilterQuery,Video,WCOpenSocial,TaskLists,MailMessage,Forum,ContextualLink,PictureBook,Unit,TaskRoot,Post,WCHtml,AdministrativeStatus,Picture,UserWorkspacesRoot,Document,Folder,ImportFolder,FacetedSearch,WCPicture,File,FacetedSearchDefault,WebSite,AdvancedSearch,Gadget,Note,TaskDoc,HiddenFolder,Section,UserProfile,MailFolder,OrderedFolder,CollectionSurvey,Audio,BasicAuditSearch,CommentRoot,BlogPost}'::text[])))
8.	3044.690	3044.690	↑ 1.0	1	304469	→ Index Scan using dublincore_pk on dublincore "_F2" (cost=0.00..3.52 rows=1 width=67) (actual time=0.010..0.010 rows=1 loops=304469) Index Cond: ((hierarchy.id)::text = ("_F2".id)::text)
9.	3044.690	3044.690	↑ 1.0	1	304469	→ 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=304469) Index Cond: (("_F1".id)::text = (hierarchy.id)::text) Filter: (("_F1".lifecyclestate)::text <> 'deleted'::text)

Sort  (cost=26819.69..26819.70 rows=1 width=67) (actual time=14366.814..14528.065 rows=304390 loops=1)
  Sort Key: "_F2".title
  Sort Method:  external merge  Disk: 20432kB
  ->  Nested Loop  (cost=24782.39..26819.68 rows=1 width=67) (actual time=761.713..12061.843 rows=304390 loops=1)
        ->  Nested Loop Left Join  (cost=24782.39..26815.31 rows=1 width=104) (actual time=761.655..8808.343 rows=304469 loops=1)
              ->  Nested Loop  (cost=24782.39..26811.78 rows=1 width=74) (actual time=761.601..5515.051 rows=304469 loops=1)
                    ->  HashAggregate  (cost=24782.39..24786.56 rows=417 width=37) (actual time=761.497..886.656 rows=317077 loops=1)
                          ->  Seq Scan on ancestors  (cost=0.00..24781.35 rows=417 width=37) (actual time=0.031..482.589 rows=317077 loops=1)
                                Filter: ('{83119cd4-a94c-4dc6-ba95-4620cfd525c8}'::character varying[] <@ ancestors)
                    ->  Index Scan using hierarchy_pk on hierarchy  (cost=0.00..4.84 rows=1 width=37) (actual time=0.014..0.014 rows=1 loops=317077)
                          Index Cond: ((hierarchy.id)::text = (ancestors.id)::text)
                          Filter: ((((hierarchy.primarytype)::text = ANY ('{Picture,File,Audio,Video}'::text[])) OR ('{Asset}'::character varying[] <@ hierarchy.mixintypes)) AND ((hierarchy.primarytype)::text = ANY ('{RelationSearch,PublicationRelation,Tag,Tagging,CommentRelation,search_results,Thread,Artwork,Comment,QueryNav,TaskList,DefaultRelation,FilterQuery,Video,MailMessage,ContextualLink,Post,AdministrativeStatus,Picture,Relation,Document,FacetedSearch,File,AdvancedSearch,FacetedSearchDefault,Note,TaskDoc,UserProfile,CollectionSurvey,Audio,BasicAuditSearch,BlogPost}'::text[])) AND ((hierarchy.primarytype)::text = ANY ('{SectionRoot,ArtworkLibrary,ImportRoot,Workspace,RelationSearch,BlogSite,Thread,TemplateRoot,Domain,Artwork,WorkspaceRoot,ImportSet,QueryNav,WebPage,FilterQuery,Video,MailMessage,Forum,PictureBook,ContextualLink,Picture,Relation,Document,Folder,FacetedSearch,ImportFolder,File,AdvancedSearch,WebSite,FacetedSearchDefault,Note,Section,Root,MailFolder,CollectionSurvey,OrderedFolder,Audio,BasicAuditSearch,BlogPost}'::text[])) AND ((hierarchy.primarytype)::text = ANY ('{ArtworkLibrary,SectionRoot,Workspace,ImportRoot,RelationSearch,BlogSite,ManagementRoot,Tag,AdministrativeStatusContainer,Thread,search_results,Space,TemplateRoot,Domain,Artwork,WorkspaceRoot,Comment,ImportSet,QueryNav,TaskList,WebPage,FilterQuery,Video,WCOpenSocial,TaskLists,MailMessage,Forum,ContextualLink,PictureBook,Unit,TaskRoot,Post,WCHtml,AdministrativeStatus,Picture,UserWorkspacesRoot,Document,Folder,ImportFolder,FacetedSearch,WCPicture,File,FacetedSearchDefault,WebSite,AdvancedSearch,Gadget,Note,TaskDoc,HiddenFolder,Section,UserProfile,MailFolder,OrderedFolder,CollectionSurvey,Audio,BasicAuditSearch,CommentRoot,BlogPost}'::text[])))
              ->  Index Scan using dublincore_pk on dublincore "_F2"  (cost=0.00..3.52 rows=1 width=67) (actual time=0.010..0.010 rows=1 loops=304469)
                    Index Cond: ((hierarchy.id)::text = ("_F2".id)::text)
        ->  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=304469)
              Index Cond: (("_F1".id)::text = (hierarchy.id)::text)
              Filter: (("_F1".lifecyclestate)::text <> 'deleted'::text)
Total runtime: 14565.619 ms
18 row(s)

Per node type stats

node type	count	sum of times	% of query
HashAggregate	1	404.067 ms	2.8 %
Index Scan	3	10528.458 ms	72.5 %
Nested Loop	2	398.127 ms	2.7 %
Nested Loop Left Join	1	248.602 ms	1.7 %
Seq Scan	1	482.589 ms	3.3 %
Sort	1	2466.222 ms	17.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
ancestors	1	482.589 ms	3.3 %
Seq Scan	1	482.589 ms	100.0 %
dublincore	1	3044.690 ms	21.0 %
Index Scan	1	3044.690 ms	100.0 %
hierarchy	1	4439.078 ms	30.6 %
Index Scan	1	4439.078 ms	100.0 %
misc	1	3044.690 ms	21.0 %
Index Scan	1	3044.690 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: YyV

Colorize:

Columns visibility:

Per node type stats

Per table stats