#	exclusive	inclusive	rows x	rows	loops	node
1.	13,446.262	72,666.363	↑ 219,824.4	30	1	→ Merge Left Join (cost=3,144,191.27..4,160,025.40 rows=6,594,733 width=72) (actual time=37,674.818..72,666.363 rows=30 loops=1) Merge Cond: (oc.id = f.parent_id) Filter: ((oc.cnt_picture <> (COALESCE((sum(oc2.cnt_picture)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 1) THEN 1 ELSE 0 END)), '0'::bigint))) OR (oc.cnt_spreadsheet <> (COALESCE((sum(oc2.cnt_spreadsheet)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 2) THEN 1 ELSE 0 END)), '0'::bigint))) OR (oc.cnt_video <> (COALESCE((sum(oc2.cnt_video)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 3) THEN 1 ELSE 0 END)), '0'::bigint))) OR (oc.cnt_text <> (COALESCE((sum(oc2.cnt_text)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 4) THEN 1 ELSE 0 END)), '0'::bigint))) OR (oc.cnt_pdf <> (COALESCE((sum(oc2.cnt_pdf)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 5) THEN 1 ELSE 0 END)), '0'::bigint))) OR (oc.cnt_presentation <> (COALESCE((sum(oc2.cnt_presentation)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 6) THEN 1 ELSE 0 END)), '0'::bigint))) OR (oc.cnt_audio <> (COALESCE((sum(oc2.cnt_audio)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 7) THEN 1 ELSE 0 END)), '0'::bigint))) OR (oc.cnt_other <> (COALESCE((sum(oc2.cnt_other)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 8) THEN 1 ELSE 0 END)), '0'::bigint))) OR ((oc.size)::numeric <> (COALESCE((sum(oc2.size)), '0'::numeric) + COALESCE((sum(fi.filesize)), '0'::numeric))) OR (oc.cnt_direct_folders <> COALESCE((count()), '0'::bigint)) OR (oc.cnt_direct_files <> COALESCE((count()), '0'::bigint))) Rows Removed by Filter: 6565571
2.	2,673.455	28,166.476	↑ 1.0	6,565,601	1	→ Merge Left Join (cost=990,006.45..1,609,636.75 rows=6,594,733 width=260) (actual time=8,274.204..28,166.476 rows=6,565,601 loops=1) Merge Cond: (oc.id = fi.folder_id)
3.	9,210.920	9,210.920	↑ 1.0	6,565,601	1	→ Index Scan using pk_containers on containers oc (cost=0.43..392,646.24 rows=6,594,733 width=156) (actual time=0.027..9,210.920 rows=6,565,601 loops=1) Filter: (objecttype_id = 4) Rows Removed by Filter: 1935322
4.	217.876	16,282.101	↓ 12.7	415,569	1	→ Materialize (cost=990,006.02..1,200,167.52 rows=32,772 width=112) (actual time=8,274.174..16,282.101 rows=415,569 loops=1)
5.	5,959.326	16,064.225	↓ 12.7	415,569	1	→ GroupAggregate (cost=990,006.02..1,199,757.87 rows=32,772 width=112) (actual time=8,274.168..16,064.225 rows=415,569 loops=1) Group Key: fi.folder_id
6.	6,974.488	10,104.899	↑ 1.0	4,186,844	1	→ Sort (cost=990,006.02..1,000,473.13 rows=4,186,844 width=18) (actual time=8,273.999..10,104.899 rows=4,186,844 loops=1) Sort Key: fi.folder_id Sort Method: external merge Disk: 139160kB
7.	3,130.411	3,130.411	↑ 1.0	4,186,844	1	→ Seq Scan on files fi (cost=0.00..271,910.44 rows=4,186,844 width=18) (actual time=0.011..3,130.411 rows=4,186,844 loops=1)
8.	154.717	31,053.625	↓ 17.2	315,545	1	→ Materialize (cost=2,154,184.82..2,351,611.43 rows=18,343 width=112) (actual time=25,381.398..31,053.625 rows=315,545 loops=1)
9.	3,932.672	30,898.908	↓ 17.2	315,545	1	→ GroupAggregate (cost=2,154,184.82..2,351,382.14 rows=18,343 width=112) (actual time=25,381.392..30,898.908 rows=315,545 loops=1) Group Key: f.parent_id
10.	10,780.799	26,966.236	↑ 1.0	6,565,601	1	→ Sort (cost=2,154,184.82..2,170,598.82 rows=6,565,601 width=48) (actual time=25,381.347..26,966.236 rows=6,565,601 loops=1) Sort Key: f.parent_id Sort Method: external merge Disk: 372272kB
11.	5,885.717	16,185.437	↑ 1.0	6,565,601	1	→ Merge Join (cost=150.38..804,832.55 rows=6,565,601 width=48) (actual time=0.055..16,185.437 rows=6,565,601 loops=1) Merge Cond: (oc2.id = f.id)
12.	5,417.321	5,417.321	↑ 1.0	8,500,923	1	→ Index Scan using pk_containers on containers oc2 (cost=0.43..371,393.94 rows=8,500,923 width=48) (actual time=0.030..5,417.321 rows=8,500,923 loops=1)
13.	4,882.399	4,882.399	↑ 1.0	6,565,601	1	→ Index Scan using pk_folders on folders f (cost=0.43..330,151.55 rows=6,565,601 width=16) (actual time=0.017..4,882.399 rows=6,565,601 loops=1)

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Left Join  (cost=3144191.27..4160025.40 rows=6594733 width=72) (actual time=37674.818..72666.363 rows=30 loops=1)
   Merge Cond: (oc.id = f.parent_id)
   Filter: ((oc.cnt_picture <> (COALESCE((sum(oc2.cnt_picture)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 1) THEN 1 ELSE 0 END)), '0'::bigint))) OR (oc.cnt_spreadsheet <> (COALESCE((sum(oc2.cnt_spreadsheet)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 2) THEN 1 ELSE 0 END)), '0'::bigint))) OR (oc.cnt_video <> (COALESCE((sum(oc2.cnt_video)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 3) THEN 1 ELSE 0 END)), '0'::bigint))) OR (oc.cnt_text <> (COALESCE((sum(oc2.cnt_text)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 4) THEN 1 ELSE 0 END)), '0'::bigint))) OR (oc.cnt_pdf <> (COALESCE((sum(oc2.cnt_pdf)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 5) THEN 1 ELSE 0 END)), '0'::bigint))) OR (oc.cnt_presentation <> (COALESCE((sum(oc2.cnt_presentation)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 6) THEN 1 ELSE 0 END)), '0'::bigint))) OR (oc.cnt_audio <> (COALESCE((sum(oc2.cnt_audio)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 7) THEN 1 ELSE 0 END)), '0'::bigint))) OR (oc.cnt_other <> (COALESCE((sum(oc2.cnt_other)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 8) THEN 1 ELSE 0 END)), '0'::bigint))) OR ((oc.size)::numeric <> (COALESCE((sum(oc2.size)), '0'::numeric) + COALESCE((sum(fi.filesize)), '0'::numeric))) OR (oc.cnt_direct_folders <> COALESCE((count(*)), '0'::bigint)) OR (oc.cnt_direct_files <> COALESCE((count(*)), '0'::bigint)))
   Rows Removed by Filter: 6565571
   ->  Merge Left Join  (cost=990006.45..1609636.75 rows=6594733 width=260) (actual time=8274.204..28166.476 rows=6565601 loops=1)
         Merge Cond: (oc.id = fi.folder_id)
         ->  Index Scan using pk_containers on containers oc  (cost=0.43..392646.24 rows=6594733 width=156) (actual time=0.027..9210.920 rows=6565601 loops=1)
               Filter: (objecttype_id = 4)
               Rows Removed by Filter: 1935322
         ->  Materialize  (cost=990006.02..1200167.52 rows=32772 width=112) (actual time=8274.174..16282.101 rows=415569 loops=1)
               ->  GroupAggregate  (cost=990006.02..1199757.87 rows=32772 width=112) (actual time=8274.168..16064.225 rows=415569 loops=1)
                     Group Key: fi.folder_id
                     ->  Sort  (cost=990006.02..1000473.13 rows=4186844 width=18) (actual time=8273.999..10104.899 rows=4186844 loops=1)
                           Sort Key: fi.folder_id
                           Sort Method: external merge  Disk: 139160kB
                           ->  Seq Scan on files fi  (cost=0.00..271910.44 rows=4186844 width=18) (actual time=0.011..3130.411 rows=4186844 loops=1)
   ->  Materialize  (cost=2154184.82..2351611.43 rows=18343 width=112) (actual time=25381.398..31053.625 rows=315545 loops=1)
         ->  GroupAggregate  (cost=2154184.82..2351382.14 rows=18343 width=112) (actual time=25381.392..30898.908 rows=315545 loops=1)
               Group Key: f.parent_id
               ->  Sort  (cost=2154184.82..2170598.82 rows=6565601 width=48) (actual time=25381.347..26966.236 rows=6565601 loops=1)
                     Sort Key: f.parent_id
                     Sort Method: external merge  Disk: 372272kB
                     ->  Merge Join  (cost=150.38..804832.55 rows=6565601 width=48) (actual time=0.055..16185.437 rows=6565601 loops=1)
                           Merge Cond: (oc2.id = f.id)
                           ->  Index Scan using pk_containers on containers oc2  (cost=0.43..371393.94 rows=8500923 width=48) (actual time=0.030..5417.321 rows=8500923 loops=1)
                           ->  Index Scan using pk_folders on folders f  (cost=0.43..330151.55 rows=6565601 width=16) (actual time=0.017..4882.399 rows=6565601 loops=1)
 Planning time: 1.870 ms
 Execution time: 72782.539 ms
(28 rows)

Per node type stats

node type	count	sum of times	% of query
GroupAggregate	2	9,891.998 ms	13.6 %
Index Scan	3	19,510.640 ms	26.8 %
Materialize	2	372.593 ms	0.5 %
Merge Join	1	5,885.717 ms	8.1 %
Merge Left Join	2	16,119.717 ms	22.2 %
Seq Scan	1	3,130.411 ms	4.3 %
Sort	2	17,755.287 ms	24.4 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
containers	2	14,628.241 ms	20.1 %
Index Scan	2	14,628.241 ms	100.0 %
files	1	3,130.411 ms	4.3 %
Seq Scan	1	3,130.411 ms	100.0 %
folders	1	4,882.399 ms	6.7 %
Index Scan	1	4,882.399 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: obpv

Color mode:

Visible columns:

Per node type stats

Per table stats