explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7NP1

Settings
# exclusive inclusive rows x rows loops node
1. 5,227.947 63,492.713 ↑ 219,824.4 30 1

Merge Left Join (cost=4,764,304.49..5,322,221.83 rows=6,594,733 width=72) (actual time=50,613.596..63,492.713 rows=30 loops=1)

  • Merge Cond: (oc.id = (COALESCE(fi.folder_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. 8,059.157 8,059.157 ↑ 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=148) (actual time=0.057..8,059.157 rows=6,565,601 loops=1)

  • Filter: (objecttype_id = 4)
  • Rows Removed by Filter: 1935322
3. 170.066 50,205.609 ↑ 4.3 693,106 1

Materialize (cost=4,764,304.06..4,779,332.48 rows=3,005,684 width=160) (actual time=49,466.919..50,205.609 rows=693,106 loops=1)

4. 2,048.005 50,035.543 ↑ 4.3 693,106 1

Sort (cost=4,764,304.06..4,771,818.27 rows=3,005,684 width=160) (actual time=49,466.916..50,035.543 rows=693,106 loops=1)

  • Sort Key: (COALESCE(fi.folder_id, f.parent_id))
  • Sort Method: external sort Disk: 175400kB
5. 2,151.979 47,987.538 ↑ 4.3 693,106 1

Merge Full Join (cost=3,144,190.84..3,732,038.05 rows=3,005,684 width=160) (actual time=32,792.810..47,987.538 rows=693,106 loops=1)

  • Merge Cond: (f.parent_id = fi.folder_id)
6. 4,168.091 30,317.594 ↓ 17.2 315,545 1

GroupAggregate (cost=2,154,184.82..2,351,382.14 rows=18,343 width=112) (actual time=24,517.371..30,317.594 rows=315,545 loops=1)

  • Group Key: f.parent_id
7. 11,378.159 26,149.503 ↑ 1.0 6,565,601 1

Sort (cost=2,154,184.82..2,170,598.82 rows=6,565,601 width=48) (actual time=24,517.337..26,149.503 rows=6,565,601 loops=1)

  • Sort Key: f.parent_id
  • Sort Method: external merge Disk: 372272kB
8. 5,344.078 14,771.344 ↑ 1.0 6,565,601 1

Merge Join (cost=150.38..804,832.55 rows=6,565,601 width=48) (actual time=0.032..14,771.344 rows=6,565,601 loops=1)

  • Merge Cond: (oc2.id = f.id)
9. 5,011.486 5,011.486 ↑ 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.014..5,011.486 rows=8,500,923 loops=1)

10. 4,415.780 4,415.780 ↑ 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.013..4,415.780 rows=6,565,601 loops=1)

11. 197.902 15,517.965 ↓ 12.7 415,569 1

Materialize (cost=990,006.02..1,200,167.52 rows=32,772 width=112) (actual time=8,275.410..15,517.965 rows=415,569 loops=1)

12. 5,606.401 15,320.063 ↓ 12.7 415,569 1

GroupAggregate (cost=990,006.02..1,199,757.87 rows=32,772 width=112) (actual time=8,275.404..15,320.063 rows=415,569 loops=1)

  • Group Key: fi.folder_id
13. 6,263.606 9,713.662 ↑ 1.0 4,186,844 1

Sort (cost=990,006.02..1,000,473.13 rows=4,186,844 width=18) (actual time=8,275.310..9,713.662 rows=4,186,844 loops=1)

  • Sort Key: fi.folder_id
  • Sort Method: external merge Disk: 139160kB
14. 3,450.056 3,450.056 ↑ 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.058..3,450.056 rows=4,186,844 loops=1)