explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Aa0

Settings
# exclusive inclusive rows x rows loops node
1. 27.120 13,920.245 ↑ 234.9 18 1

Merge Left Join (cost=3,130.10..3,295.71 rows=4,229 width=72) (actual time=13,885.299..13,920.245 rows=18 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: 7117
2. 8.020 10,307.216 ↓ 1.7 7,135 1

Merge Left Join (cost=2,993.28..3,029.62 rows=4,229 width=270) (actual time=10,195.808..10,307.216 rows=7,135 loops=1)

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

Sort (cost=1,979.65..1,990.23 rows=4,229 width=166) (actual time=258.496..263.498 rows=7,135 loops=1)

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

Seq Scan on containers oc (cost=0.00..1,724.94 rows=4,229 width=166) (actual time=23.820..243.464 rows=7,135 loops=1)

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

Materialize (cost=1,013.62..1,028.02 rows=192 width=112) (actual time=9,937.304..10,035.698 rows=1,692 loops=1)

6. 82.380 10,034.495 ↓ 8.8 1,692 1

GroupAggregate (cost=1,013.62..1,025.62 rows=192 width=112) (actual time=9,937.290..10,034.495 rows=1,692 loops=1)

  • Group Key: fi.folder_id
7. 76.774 9,952.115 ↓ 218.1 41,877 1

Sort (cost=1,013.62..1,014.10 rows=192 width=18) (actual time=9,936.734..9,952.115 rows=41,877 loops=1)

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

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

9. 171.468 171.468 ↓ 218.0 218 1

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

  • Filter: (owner_id = COALESCE(owner_id))
10. 9,314.486 9,664.158 ↑ 2.0 192 218

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

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

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

  • Index Cond: (storage_id = s.id)
12. 1.405 3,585.909 ↓ 61.4 2,149 1

Materialize (cost=136.82..138.75 rows=35 width=112) (actual time=3,570.253..3,585.909 rows=2,149 loops=1)

13. 11.246 3,584.504 ↓ 61.4 2,149 1

GroupAggregate (cost=136.82..138.31 rows=35 width=112) (actual time=3,570.248..3,584.504 rows=2,149 loops=1)

  • Group Key: f.parent_id
14. 10.198 3,573.258 ↓ 203.9 7,135 1

Sort (cost=136.82..136.91 rows=35 width=48) (actual time=3,570.077..3,573.258 rows=7,135 loops=1)

  • Sort Key: f.parent_id
  • Sort Method: quicksort Memory: 750kB
15. 6.213 3,563.060 ↓ 203.9 7,135 1

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

16. 4.267 2,529.407 ↓ 209.9 7,135 1

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

17. 0.482 0.482 ↓ 218.0 218 1

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

  • Filter: (owner_id = COALESCE(owner_id))
18. 2,521.824 2,524.658 ↑ 1.0 33 218

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

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

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

  • Index Cond: (storage_id = s_1.id)
20. 1,027.440 1,027.440 ↑ 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.139..0.144 rows=1 loops=7,135)

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