explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rV1BR

Settings
# exclusive inclusive rows x rows loops node
1. 35.762 368.335 ↑ 233.8 18 1

Merge Left Join (cost=3,130.10..3,410.85 rows=4,208 width=72) (actual time=334.265..368.335 rows=18 loops=1)

  • Merge Cond: (oc.id = f.parent_id)
  • Filter: (oc.* <> ROW(oc.id, ((COALESCE((sum(oc2.cnt_picture)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 1) THEN 1 ELSE 0 END)), '0'::bigint)))::integer, ((COALESCE((sum(oc2.cnt_spreadsheet)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 2) THEN 1 ELSE 0 END)), '0'::bigint)))::integer, ((COALESCE((sum(oc2.cnt_video)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 3) THEN 1 ELSE 0 END)), '0'::bigint)))::integer, ((COALESCE((sum(oc2.cnt_text)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 4) THEN 1 ELSE 0 END)), '0'::bigint)))::integer, ((COALESCE((sum(oc2.cnt_pdf)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 5) THEN 1 ELSE 0 END)), '0'::bigint)))::integer, ((COALESCE((sum(oc2.cnt_presentation)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 6) THEN 1 ELSE 0 END)), '0'::bigint)))::integer, ((COALESCE((sum(oc2.cnt_audio)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 7) THEN 1 ELSE 0 END)), '0'::bigint)))::integer, ((COALESCE((sum(oc2.cnt_other)), '0'::bigint) + COALESCE((sum(CASE WHEN (fi.filesort_id = 8) THEN 1 ELSE 0 END)), '0'::bigint)))::integer, ((COALESCE((sum(oc2.size)), '0'::numeric) + COALESCE((sum(fi.filesize)), '0'::numeric)))::bigint, oc.sizelimit, (COALESCE((count(*)), '0'::bigint))::integer, (COALESCE((count(*)), '0'::bigint))::integer, 4, oc.size_mirrors, '1'::smallint))
  • Rows Removed by Filter: 7117
2. 6.067 264.267 ↓ 1.7 7,135 1

Merge Left Join (cost=2,993.28..3,029.62 rows=4,229 width=230) (actual time=163.780..264.267 rows=7,135 loops=1)

  • Merge Cond: (oc.id = fi.folder_id)
3. 17.784 34.898 ↓ 1.7 7,135 1

Sort (cost=1,979.65..1,990.23 rows=4,229 width=126) (actual time=30.271..34.898 rows=7,135 loops=1)

  • Sort Key: oc.id
  • Sort Method: external sort Disk: 968kB
4. 17.114 17.114 ↓ 1.7 7,135 1

Seq Scan on containers oc (cost=0.00..1,724.94 rows=4,229 width=126) (actual time=0.021..17.114 rows=7,135 loops=1)

  • Filter: ((objecttype_id = 4) AND (containerdatatype_id = '1'::smallint))
  • Rows Removed by Filter: 37461
5. 1.237 223.302 ↓ 8.8 1,692 1

Materialize (cost=1,013.62..1,028.02 rows=192 width=112) (actual time=133.501..223.302 rows=1,692 loops=1)

6. 76.172 222.065 ↓ 8.8 1,692 1

GroupAggregate (cost=1,013.62..1,025.62 rows=192 width=112) (actual time=133.482..222.065 rows=1,692 loops=1)

  • Group Key: fi.folder_id
7. 76.425 145.893 ↓ 218.1 41,877 1

Sort (cost=1,013.62..1,014.10 rows=192 width=18) (actual time=133.033..145.893 rows=41,877 loops=1)

  • Sort Key: fi.folder_id
  • Sort Method: external merge Disk: 1392kB
8. 37.977 69.468 ↓ 218.1 41,877 1

Nested Loop (cost=11.22..1,006.34 rows=192 width=18) (actual time=0.081..69.468 rows=41,877 loops=1)

9. 0.317 0.317 ↓ 218.0 218 1

Seq Scan on storages s (cost=0.00..7.72 rows=1 width=8) (actual time=0.026..0.317 rows=218 loops=1)

  • Filter: (owner_id = COALESCE(owner_id))
10. 23.544 31.174 ↑ 2.0 192 218

Bitmap Heap Scan on files fi (cost=11.22..994.84 rows=378 width=26) (actual time=0.041..0.143 rows=192 loops=218)

  • Recheck Cond: (storage_id = s.id)
  • Heap Blocks: exact=3525
11. 7.630 7.630 ↑ 2.0 193 218

Bitmap Index Scan on ix_files_storage (cost=0.00..11.12 rows=378 width=0) (actual time=0.035..0.035 rows=193 loops=218)

  • Index Cond: (storage_id = s.id)
12. 1.482 68.306 ↓ 61.4 2,149 1

Materialize (cost=136.82..138.75 rows=35 width=112) (actual time=54.156..68.306 rows=2,149 loops=1)

13. 10.943 66.824 ↓ 61.4 2,149 1

GroupAggregate (cost=136.82..138.31 rows=35 width=112) (actual time=54.150..66.824 rows=2,149 loops=1)

  • Group Key: f.parent_id
14. 7.490 55.881 ↓ 203.9 7,135 1

Sort (cost=136.82..136.91 rows=35 width=48) (actual time=53.983..55.881 rows=7,135 loops=1)

  • Sort Key: f.parent_id
  • Sort Method: quicksort Memory: 750kB
15. 10.566 48.391 ↓ 203.9 7,135 1

Nested Loop (cost=4.96..135.93 rows=35 width=48) (actual time=0.078..48.391 rows=7,135 loops=1)

16. 3.622 9.285 ↓ 209.9 7,135 1

Nested Loop (cost=4.55..94.76 rows=34 width=16) (actual time=0.060..9.285 rows=7,135 loops=1)

17. 0.213 0.213 ↓ 218.0 218 1

Seq Scan on storages s_1 (cost=0.00..7.72 rows=1 width=8) (actual time=0.022..0.213 rows=218 loops=1)

  • Filter: (owner_id = COALESCE(owner_id))
18. 3.488 5.450 ↑ 1.0 33 218

Bitmap Heap Scan on folders f (cost=4.55..86.69 rows=34 width=24) (actual time=0.012..0.025 rows=33 loops=218)

  • Recheck Cond: (storage_id = s_1.id)
  • Heap Blocks: exact=725
19. 1.962 1.962 ↑ 1.0 34 218

Bitmap Index Scan on ix_folders_storage (cost=0.00..4.54 rows=34 width=0) (actual time=0.009..0.009 rows=34 loops=218)

  • Index Cond: (storage_id = s_1.id)
20. 28.540 28.540 ↑ 1.0 1 7,135

Index Scan using pk_containers on containers oc2 (cost=0.41..1.20 rows=1 width=48) (actual time=0.004..0.004 rows=1 loops=7,135)

  • Index Cond: ((id = f.id) AND (containerdatatype_id = '1'::smallint))