explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NQyF

Settings
# exclusive inclusive rows x rows loops node
1. 12,691.528 73,330.714 ↑ 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=38,507.658..73,330.714 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,578.819 27,107.160 ↑ 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=7,857.206..27,107.160 rows=6,565,601 loops=1)

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

  • Filter: (objecttype_id = 4)
  • Rows Removed by Filter: 1935322
4. 208.479 15,638.590 ↓ 12.7 415,569 1

Materialize (cost=990,006.02..1,200,167.52 rows=32,772 width=112) (actual time=7,857.166..15,638.590 rows=415,569 loops=1)

5. 5,884.180 15,430.111 ↓ 12.7 415,569 1

GroupAggregate (cost=990,006.02..1,199,757.87 rows=32,772 width=112) (actual time=7,857.162..15,430.111 rows=415,569 loops=1)

  • Group Key: fi.folder_id
6. 6,166.041 9,545.931 ↑ 1.0 4,186,844 1

Sort (cost=990,006.02..1,000,473.13 rows=4,186,844 width=18) (actual time=7,857.056..9,545.931 rows=4,186,844 loops=1)

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

8. 151.133 33,532.026 ↓ 17.2 315,545 1

Materialize (cost=2,154,184.82..2,351,611.43 rows=18,343 width=112) (actual time=26,230.090..33,532.026 rows=315,545 loops=1)

9. 5,089.174 33,380.893 ↓ 17.2 315,545 1

GroupAggregate (cost=2,154,184.82..2,351,382.14 rows=18,343 width=112) (actual time=26,230.086..33,380.893 rows=315,545 loops=1)

  • Group Key: f.parent_id
10. 11,280.319 28,291.719 ↑ 1.0 6,565,601 1

Sort (cost=2,154,184.82..2,170,598.82 rows=6,565,601 width=48) (actual time=26,230.041..28,291.719 rows=6,565,601 loops=1)

  • Sort Key: f.parent_id
  • Sort Method: external merge Disk: 372272kB
11. 5,711.629 17,011.400 ↑ 1.0 6,565,601 1

Merge Join (cost=150.38..804,832.55 rows=6,565,601 width=48) (actual time=0.043..17,011.400 rows=6,565,601 loops=1)

  • Merge Cond: (oc2.id = f.id)
12. 6,580.271 6,580.271 ↑ 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.026..6,580.271 rows=8,500,923 loops=1)

13. 4,719.500 4,719.500 ↑ 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.011..4,719.500 rows=6,565,601 loops=1)