explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Htuo

Settings
# exclusive inclusive rows x rows loops node
1. 145.578 2,032,179.091 ↓ 0.0 0 1

Insert on tstmaint_8322_local.dss_codepath (cost=1,598.09..1,598.12 rows=1 width=28) (actual time=2,032,179.091..2,032,179.091 rows=0 loops=1)

  • Buffers: shared hit=578175 read=1158 dirtied=736, local hit=204
2. 145.808 2,032,033.513 ↓ 35,462.0 35,462 1

HashAggregate (cost=1,598.09..1,598.11 rows=1 width=28) (actual time=2,031,979.147..2,032,033.513 rows=35,462 loops=1)

  • Output: scope.pathid, scope.scopeindex, ((scope.scopeindex + 1)), s.source_id, (((obj.info1 + book.info1) - 1)), (((obj.info1 + book.info3) - 1)), scope.containerid
  • Buffers: shared hit=471376 read=762 dirtied=339, local hit=204
3. 221.560 2,031,887.705 ↓ 35,474.0 35,474 1

Nested Loop Semi Join (cost=683.57..1,598.07 rows=1 width=28) (actual time=1,424,481.559..2,031,887.705 rows=35,474 loops=1)

  • Output: scope.pathid, scope.scopeindex, (scope.scopeindex + 1), s.source_id, ((obj.info1 + book.info1) - 1), ((obj.info1 + book.info3) - 1), scope.containerid
  • Buffers: shared hit=471376 read=762 dirtied=339, local hit=204
4. 235.982 2,031,559.723 ↓ 35,474.0 35,474 1

Nested Loop (cost=683.57..1,597.76 rows=1 width=44) (actual time=1,424,481.126..2,031,559.723 rows=35,474 loops=1)

  • Output: scope.pathid, scope.scopeindex, scope.containerid, scope.projectid, f.idfil, s.source_id, obj.info1, obj.idobjref, book.info1, book.info3, book.blkno
  • Buffers: shared hit=364955 read=751 dirtied=339, local hit=204
5. 250.667 2,031,146.371 ↓ 35,474.0 35,474 1

Nested Loop (cost=683.57..1,597.02 rows=1 width=160) (actual time=1,424,481.097..2,031,146.371 rows=35,474 loops=1)

  • Output: scope.pathid, scope.scopeindex, scope.containerid, scope.projectid, f.idfil, r.path, obj.info1, obj.idobjref, book.info1, book.info3, book.blkno
  • Buffers: shared hit=219346 read=751 dirtied=339, local hit=204
6. 235.130 2,030,753.808 ↓ 35,474.0 35,474 1

Nested Loop (cost=683.57..1,596.74 rows=1 width=44) (actual time=1,424,481.053..2,030,753.808 rows=35,474 loops=1)

  • Output: scope.pathid, scope.scopeindex, scope.containerid, scope.projectid, f.idfilref, f.idfil, obj.info1, obj.idobjref, book.info1, book.info3, book.blkno
  • Join Filter: ((obj.idobjref = book.blkno) AND (obj.prop = book.prop))
  • Buffers: shared hit=112925 read=750 dirtied=339, local hit=204
7. 152.423 2,030,326.684 ↓ 128.5 31,999 1

Merge Join (cost=683.57..1,506.11 rows=249 width=40) (actual time=1,424,480.911..2,030,326.684 rows=31,999 loops=1)

  • Output: scope.pathid, scope.scopeindex, scope.containerid, scope.projectid, scope.idacc, f.idfilref, f.idfil, obj.info1, obj.idobjref, obj.prop
  • Merge Cond: (scope.containerid = f.idobj)
  • Join Filter: (obj.idobjref = f.idfil)
  • Buffers: shared hit=13674 read=278, local hit=204
8. 1,040,851.235 2,030,100.797 ↑ 1.1 31,999 1

Nested Loop (cost=0.00..13,145,278.97 rows=36,294 width=36) (actual time=1,424,440.461..2,030,100.797 rows=31,999 loops=1)

  • Output: scope.pathid, scope.scopeindex, scope.containerid, scope.projectid, scope.idacc, obj.info1, obj.idobjref, obj.idobj, obj.prop
  • Join Filter: (scope.containerid = obj.idobj)
  • Rows Removed by Join Filter: 877924564
  • Buffers: shared hit=10513 read=172, local hit=204
9. 392.645 392.645 ↓ 1.0 27,437 1

Index Scan using objpos_idobj on tstmaint_8322_local.objpos obj (cost=0.00..765.75 rows=27,384 width=16) (actual time=0.008..392.645 rows=27,437 loops=1)

  • Output: obj.idobj, obj.idobjref, obj.posmode, obj.info1, obj.info2, obj.info3, obj.info4, obj.prop, obj.blkno
  • Buffers: shared hit=10513 read=172
10. 988,818.284 988,856.917 ↑ 1.0 31,999 27,437

Materialize (cost=0.00..683.99 rows=31,999 width=20) (actual time=0.002..36.041 rows=31,999 loops=27,437)

  • Output: scope.pathid, scope.scopeindex, scope.containerid, scope.projectid, scope.idacc
  • Buffers: local hit=204
11. 38.633 38.633 ↑ 1.0 31,999 1

Seq Scan on pg_temp_5.tmp_scope_expensivepath scope (cost=0.00..523.99 rows=31,999 width=20) (actual time=0.008..38.633 rows=31,999 loops=1)

  • Output: scope.pathid, scope.scopeindex, scope.containerid, scope.projectid, scope.idacc
  • Buffers: local hit=204
12. 73.464 73.464 ↓ 2.2 54,263 1

Index Scan using objfilref_idobj on tstmaint_8322_local.objfilref f (cost=0.00..593.49 rows=25,102 width=12) (actual time=0.025..73.464 rows=54,263 loops=1)

  • Output: f.idobj, f.idfilref, f.idfil
  • Buffers: shared hit=3161 read=106
13. 191.994 191.994 ↑ 2.0 1 31,999

Index Scan using accbook_idacc on tstmaint_8322_local.accbook book (cost=0.00..0.33 rows=2 width=20) (actual time=0.004..0.006 rows=1 loops=31,999)

  • Output: book.idacc, book.bookmode, book.info1, book.info2, book.info3, book.info4, book.prop, book.blkno
  • Index Cond: (book.idacc = scope.idacc)
  • Buffers: shared hit=99251 read=472 dirtied=339
14. 141.896 141.896 ↑ 1.0 1 35,474

Index Scan using pk_refpath on tstmaint_8322_local.refpath r (cost=0.00..0.27 rows=1 width=124) (actual time=0.003..0.004 rows=1 loops=35,474)

  • Output: r.idfilref, r.path
  • Index Cond: (r.idfilref = f.idfilref)
  • Buffers: shared hit=106421 read=1
15. 177.370 177.370 ↑ 11.0 1 35,474

Index Scan using idx_src_pathstring on tstmaint_8322_local.dss_code_sources s (cost=0.00..0.62 rows=11 width=520) (actual time=0.003..0.005 rows=1 loops=35,474)

  • Output: s.source_path, s.source_id, s.source_error, s.source_code, s.source_crc
  • Index Cond: ((s.source_path)::text = (r.path)::text)
  • Buffers: shared hit=145609
16. 106.422 106.422 ↑ 1.0 1 35,474

Index Only Scan using objpro_proobj on tstmaint_8322_local.objpro projet (cost=0.00..0.29 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=35,474)

  • Output: projet.idpro, projet.idobj
  • Index Cond: ((projet.idpro = scope.projectid) AND (projet.idobj = obj.idobjref))
  • Heap Fetches: 35474
  • Buffers: shared hit=106421 read=11