explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XTIa

Settings
# exclusive inclusive rows x rows loops node
1. 13.452 197.143 ↑ 234.9 18 1

Merge Left Join (cost=3,130.10..3,295.71 rows=4,229 width=72) (actual time=179.172..197.143 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. 4.036 145.674 ↓ 1.7 7,135 1

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

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

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

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

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

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

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

6. 42.569 116.596 ↓ 8.8 1,692 1

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

  • Group Key: fi.folder_id
7. 36.312 74.027 ↓ 218.1 41,877 1

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

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

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

9. 0.152 0.152 ↓ 218.0 218 1

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

  • Filter: (owner_id = COALESCE(owner_id))
10. 13.734 17.658 ↑ 2.0 192 218

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

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

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

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

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

13. 5.787 37.227 ↓ 61.4 2,149 1

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

  • Group Key: f.parent_id
14. 4.433 31.440 ↓ 203.9 7,135 1

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

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

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

16. 2.005 5.419 ↓ 209.9 7,135 1

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

17. 0.144 0.144 ↓ 218.0 218 1

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

  • Filter: (owner_id = COALESCE(owner_id))
18. 2.180 3.270 ↑ 1.0 33 218

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

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

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

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

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