explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xd9v

Settings
# exclusive inclusive rows x rows loops node
1. 12,910.773 128,325.357 ↑ 219,824.4 30 1

Merge Left Join (cost=639,502.58..1,250,640.65 rows=6,594,733 width=72) (actual time=93,670.927..128,325.357 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,575.209 45,620.407 ↑ 1.0 6,565,601 1

Merge Left Join (cost=344,843.67..755,761.09 rows=6,594,733 width=260) (actual time=26,844.685..45,620.407 rows=6,565,601 loops=1)

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

  • Filter: (objecttype_id = 4)
  • Rows Removed by Filter: 1935322
4. 202.448 34,207.790 ↓ 19.9 415,569 1

Materialize (cost=344,843.23..346,413.28 rows=20,934 width=112) (actual time=26,844.651..34,207.790 rows=415,569 loops=1)

5. 5,520.183 34,005.342 ↓ 19.9 415,569 1

GroupAggregate (cost=344,843.23..346,151.61 rows=20,934 width=112) (actual time=26,844.646..34,005.342 rows=415,569 loops=1)

  • Group Key: fi.folder_id
6. 7,318.539 28,485.159 ↓ 200.0 4,186,844 1

Sort (cost=344,843.23..344,895.57 rows=20,934 width=18) (actual time=26,844.410..28,485.159 rows=4,186,844 loops=1)

  • Sort Key: fi.folder_id
  • Sort Method: external merge Disk: 139176kB
7. 17,630.557 21,166.620 ↓ 200.0 4,186,844 1

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

  • Hash Cond: (fi.storage_id = s.id)
8. 2,348.188 2,348.188 ↑ 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=0.011..2,348.188 rows=4,186,844 loops=1)

9. 523.949 1,187.875 ↓ 200.0 1,917,405 1

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

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

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

  • Filter: (owner_id = COALESCE(owner_id))
11. 151.794 69,794.177 ↓ 17.2 315,545 1

Materialize (cost=294,658.92..296,102.33 rows=18,343 width=112) (actual time=63,436.969..69,794.177 rows=315,545 loops=1)

12. 4,304.534 69,642.383 ↓ 17.2 315,545 1

GroupAggregate (cost=294,658.92..295,873.04 rows=18,343 width=112) (actual time=63,436.963..69,642.383 rows=315,545 loops=1)

  • Group Key: f.parent_id
13. 11,191.448 65,337.849 ↓ 200.0 6,565,601 1

Sort (cost=294,658.92..294,740.99 rows=32,828 width=48) (actual time=63,436.934..65,337.849 rows=6,565,601 loops=1)

  • Sort Key: f.parent_id
  • Sort Method: external merge Disk: 372272kB
14. 2,645.082 54,146.401 ↓ 200.0 6,565,601 1

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

15. 9,032.254 12,107.713 ↓ 200.0 6,565,601 1

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

  • Hash Cond: (f.storage_id = s_1.id)
16. 1,574.220 1,574.220 ↑ 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=0.247..1,574.220 rows=6,565,601 loops=1)

17. 681.086 1,501.239 ↓ 200.0 1,917,405 1

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

  • Buckets: 65536 (originally 16384) Batches: 64 (originally 1) Memory Usage: 1687kB
18. 820.153 820.153 ↓ 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.031..820.153 rows=1,917,405 loops=1)

  • Filter: (owner_id = COALESCE(owner_id))
19. 39,393.606 39,393.606 ↑ 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.005..0.006 rows=1 loops=6,565,601)

  • Index Cond: (id = f.id)