explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uX4H

Settings
# exclusive inclusive rows x rows loops node
1. 13,355.761 210,697.853 ↑ 219,824.4 30 1

Merge Left Join (cost=639,502.58..1,250,640.65 rows=6,594,733 width=72) (actual time=172,435.617..210,697.853 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,669.569 58,807.710 ↑ 1.0 6,565,601 1

Merge Left Join (cost=344,843.67..755,761.09 rows=6,594,733 width=260) (actual time=39,257.493..58,807.710 rows=6,565,601 loops=1)

  • Merge Cond: (oc.id = fi.folder_id)
3. 9,107.035 9,107.035 ↑ 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.019..9,107.035 rows=6,565,601 loops=1)

  • Filter: (objecttype_id = 4)
  • Rows Removed by Filter: 1935322
4. 209.144 47,031.106 ↓ 19.9 415,569 1

Materialize (cost=344,843.23..346,413.28 rows=20,934 width=112) (actual time=39,257.472..47,031.106 rows=415,569 loops=1)

5. 5,859.082 46,821.962 ↓ 19.9 415,569 1

GroupAggregate (cost=344,843.23..346,151.61 rows=20,934 width=112) (actual time=39,257.467..46,821.962 rows=415,569 loops=1)

  • Group Key: fi.folder_id
6. 7,055.863 40,962.880 ↓ 200.0 4,186,844 1

Sort (cost=344,843.23..344,895.57 rows=20,934 width=18) (actual time=39,257.306..40,962.880 rows=4,186,844 loops=1)

  • Sort Key: fi.folder_id
  • Sort Method: external merge Disk: 139176kB
7. 20,680.458 33,907.017 ↓ 200.0 4,186,844 1

Hash Join (cost=55,520.40..343,340.85 rows=20,934 width=18) (actual time=1,985.719..33,907.017 rows=4,186,844 loops=1)

  • Hash Cond: (fi.storage_id = s.id)
8. 11,254.283 11,254.283 ↑ 1.0 4,186,844 1

Seq Scan on files fi (cost=0.00..271,910.44 rows=4,186,844 width=26) (actual time=13.088..11,254.283 rows=4,186,844 loops=1)

9. 752.611 1,972.276 ↓ 200.0 1,917,405 1

Hash (cost=55,400.56..55,400.56 rows=9,587 width=8) (actual time=1,972.276..1,972.276 rows=1,917,405 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 64 (originally 1) Memory Usage: 1687kB
10. 1,219.665 1,219.665 ↓ 200.0 1,917,405 1

Seq Scan on storages s (cost=0.00..55,400.56 rows=9,587 width=8) (actual time=5.790..1,219.665 rows=1,917,405 loops=1)

  • Filter: (owner_id = COALESCE(owner_id))
11. 166.857 138,534.382 ↓ 17.2 315,545 1

Materialize (cost=294,658.92..296,102.33 rows=18,343 width=112) (actual time=129,359.895..138,534.382 rows=315,545 loops=1)

12. 6,262.143 138,367.525 ↓ 17.2 315,545 1

GroupAggregate (cost=294,658.92..295,873.04 rows=18,343 width=112) (actual time=129,359.890..138,367.525 rows=315,545 loops=1)

  • Group Key: f.parent_id
13. 13,889.240 132,105.382 ↓ 200.0 6,565,601 1

Sort (cost=294,658.92..294,740.99 rows=32,828 width=48) (actual time=129,359.858..132,105.382 rows=6,565,601 loops=1)

  • Sort Key: f.parent_id
  • Sort Method: external merge Disk: 372280kB
14. 4,454.143 118,216.142 ↓ 200.0 6,565,601 1

Nested Loop (cost=55,520.83..291,184.88 rows=32,828 width=48) (actual time=1,369.299..118,216.142 rows=6,565,601 loops=1)

15. 17,881.180 21,843.585 ↓ 200.0 6,565,601 1

Hash Join (cost=55,520.40..273,206.69 rows=32,828 width=16) (actual time=1,337.748..21,843.585 rows=6,565,601 loops=1)

  • Hash Cond: (f.storage_id = s_1.id)
16. 2,630.454 2,630.454 ↑ 1.0 6,565,601 1

Seq Scan on folders f (cost=0.00..192,737.01 rows=6,565,601 width=24) (actual time=5.067..2,630.454 rows=6,565,601 loops=1)

17. 606.225 1,331.951 ↓ 200.0 1,917,405 1

Hash (cost=55,400.56..55,400.56 rows=9,587 width=8) (actual time=1,331.951..1,331.951 rows=1,917,405 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 64 (originally 1) Memory Usage: 1687kB
18. 725.726 725.726 ↓ 200.0 1,917,405 1

Seq Scan on storages s_1 (cost=0.00..55,400.56 rows=9,587 width=8) (actual time=0.029..725.726 rows=1,917,405 loops=1)

  • Filter: (owner_id = COALESCE(owner_id))
19. 91,918.414 91,918.414 ↑ 1.0 1 6,565,601

Index Scan using pk_containers on containers oc2 (cost=0.43..0.54 rows=1 width=48) (actual time=0.013..0.014 rows=1 loops=6,565,601)

  • Index Cond: (id = f.id)