explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KeBu : NAC from_collapse_limit=8

Settings
# exclusive inclusive rows x rows loops node
1. 0.078 21,032.234 ↓ 62.0 62 1

HashAggregate (cost=2,330.16..2,330.17 rows=1 width=35) (actual time=21,032.224..21,032.234 rows=62 loops=1)

  • Output: p2.lobj_name, p1.commentushort, l0.lobj_id
  • Group Key: p2.lobj_name, p1.commentushort, l0.lobj_id
  • Buffers: shared hit=5022078
2. 0.028 21,032.156 ↓ 62.0 62 1

Nested Loop (cost=2,133.28..2,330.16 rows=1 width=35) (actual time=18,443.064..21,032.156 rows=62 loops=1)

  • Output: p2.lobj_name, p1.commentushort, l0.lobj_id
  • Buffers: shared hit=5022078
3. 1.928 21,032.004 ↓ 62.0 62 1

Nested Loop (cost=2,133.14..2,329.66 rows=1 width=51) (actual time=18,443.058..21,032.004 rows=62 loops=1)

  • Output: l0.lobj_id, p1.commentushort, ln1_1.parentlobj_id, ln2.childlobj_id, p2.lobj_name
  • Join Filter: ((l0.lobj_id = ln1_1.childlobj_id) AND (ln2.childlobj_id = ln1_1.parentlobj_id))
  • Rows Removed by Join Filter: 3410
  • Buffers: shared hit=5021954
4. 2.908 21,023.132 ↓ 3,472.0 3,472 1

Nested Loop Left Join (cost=2,132.72..2,326.38 rows=1 width=51) (actual time=14,719.319..21,023.132 rows=3,472 loops=1)

  • Output: l0.lobj_id, p1.commentushort, ln2.childlobj_id, p2.lobj_name, ln1_2.childlobj_id
  • Buffers: shared hit=5007954
5. 1,185.015 20,999.392 ↓ 3,472.0 3,472 1

Nested Loop (cost=2,132.16..2,317.49 rows=1 width=39) (actual time=14,719.298..20,999.392 rows=3,472 loops=1)

  • Output: l0.lobj_id, ln2.childlobj_id, p2.lobj_name, ln1_2.childlobj_id
  • Buffers: shared hit=4987010
6. 4,450.777 15,806.793 ↓ 11,998.8 2,003,792 1

Merge Join (cost=2,131.87..2,147.26 rows=167 width=31) (actual time=14,719.186..15,806.793 rows=2,003,792 loops=1)

  • Output: ln2.childlobj_id, p2.lobj_name, ln1_2.childlobj_id
  • Merge Cond: (ln2.parentlobj_id = p2.lobj_id)
  • Buffers: shared hit=2897
7. 7,561.989 11,210.360 ↓ 25,913.3 73,567,792 1

Nested Loop (cost=2,089.21..5,428.30 rows=2,839 width=24) (actual time=11.982..11,210.360 rows=73,567,792 loops=1)

  • Output: ln2.childlobj_id, ln2.parentlobj_id, ln1_2.childlobj_id
  • Buffers: shared hit=2896
8. 5.139 5.139 ↓ 120.9 2,056 1

Index Only Scan using pk_objectlink on dalimes.objectlink ln2 (cost=0.42..180.58 rows=17 width=16) (actual time=0.019..5.139 rows=2,056 loops=1)

  • Output: ln2.parentotpl_id, ln2.parentlobj_id, ln2.ts_deletion, ln2.childotpl_id, ln2.childlobj_id, ln2.olnk_id
  • Index Cond: ((ln2.parentotpl_id = 8) AND (ln2.ts_deletion = 0) AND (ln2.childotpl_id = 35))
  • Heap Fetches: 2056
  • Buffers: shared hit=97
9. 3,584.780 3,643.232 ↓ 214.3 35,782 2,056

Materialize (cost=2,088.78..5,212.65 rows=167 width=8) (actual time=0.006..1.772 rows=35,782 loops=2,056)

  • Output: ln1_2.childlobj_id
  • Buffers: shared hit=2799
10. 13.026 58.452 ↓ 214.3 35,782 1

Hash Join (cost=2,088.78..5,211.82 rows=167 width=8) (actual time=11.959..58.452 rows=35,782 loops=1)

  • Output: ln1_2.childlobj_id
  • Hash Cond: (ln1_2.parentlobj_id = p1_2.lobj_id)
  • Buffers: shared hit=2799
11. 33.768 42.805 ↓ 6.5 36,782 1

Bitmap Heap Scan on dalimes.objectlink ln1_2 (cost=1,878.42..4,978.50 rows=5,674 width=16) (actual time=9.323..42.805 rows=36,782 loops=1)

  • Output: ln1_2.olnk_id, ln1_2.parentotpl_id, ln1_2.parentlobj_id, ln1_2.childotpl_id, ln1_2.childlobj_id, ln1_2.ts_deletion
  • Recheck Cond: ((ln1_2.childotpl_id = 53) AND (ln1_2.ts_deletion = 0) AND (ln1_2.parentotpl_id = 19))
  • Heap Blocks: exact=2380
  • Buffers: shared hit=2687
12. 9.037 9.037 ↓ 6.7 37,917 1

Bitmap Index Scan on ix_objectlink_child_id (cost=0.00..1,877.00 rows=5,674 width=0) (actual time=9.037..9.037 rows=37,917 loops=1)

  • Index Cond: ((ln1_2.childotpl_id = 53) AND (ln1_2.ts_deletion = 0) AND (ln1_2.parentotpl_id = 19))
  • Buffers: shared hit=307
13. 0.534 2.621 ↑ 1.0 3,426 1

Hash (cost=165.73..165.73 rows=3,571 width=8) (actual time=2.621..2.621 rows=3,426 loops=1)

  • Output: p1_2.lobj_id
  • Buckets: 4096 Batches: 1 Memory Usage: 166kB
  • Buffers: shared hit=112
14. 2.087 2.087 ↑ 1.0 3,426 1

Seq Scan on dalimes.o_folder p1_2 (cost=0.00..165.73 rows=3,571 width=8) (actual time=0.011..2.087 rows=3,426 loops=1)

  • Output: p1_2.lobj_id
  • Filter: ((p1_2.lobj_id <> '0'::bigint) AND (p1_2.ts_deletion = 0))
  • Rows Removed by Filter: 22
  • Buffers: shared hit=112
15. 145.647 145.656 ↓ 164,001.0 1,968,012 1

Sort (cost=1.41..1.44 rows=12 width=23) (actual time=0.024..145.656 rows=1,968,012 loops=1)

  • Output: p2.lobj_name, p2.lobj_id
  • Sort Key: p2.lobj_id
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=1
16. 0.009 0.009 ↓ 1.1 13 1

Seq Scan on dalimes.o_customer p2 (cost=0.00..1.20 rows=12 width=23) (actual time=0.006..0.009 rows=13 loops=1)

  • Output: p2.lobj_name, p2.lobj_id
  • Filter: ((p2.lobj_id <> '2'::bigint) AND (p2.ts_deletion = 0))
  • Buffers: shared hit=1
17. 4,007.584 4,007.584 ↓ 0.0 0 2,003,792

Index Only Scan using ixu_o_pageorder_idlist on dalimes.o_pageorder l0 (cost=0.29..1.01 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=2,003,792)

  • Output: l0.ts_deletion, l0.lobj_id
  • Index Cond: ((l0.ts_deletion = 0) AND (l0.lobj_id = ln1_2.childlobj_id))
  • Filter: (l0.lobj_id = ANY ('{144557,10687,531714,534180,518968,518930,518972,534184,531715,518934,25946,534176,531719,531735,534188,531723,534807,531739,531727,534900,531731,534912,534908,534904,534920,534924,534916,534936,518226,10905,536066,536074,536070,536078,536042,536062,536038,536046,477154,477577,477623,150606,150610,150614,536034,536058,536054,535608,535612,536053,514499,514503,10871,25942,406693,514458,514868,514919,514299,514322,514151,514495,514430,514434,514801,518555}'::bigint[]))
  • Rows Removed by Filter: 1
  • Heap Fetches: 960344
  • Buffers: shared hit=4984113
18. 3.472 20.832 ↑ 1.0 1 3,472

Nested Loop (cost=0.56..8.88 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=3,472)

  • Output: ln1.childlobj_id, p1.commentushort
  • Buffers: shared hit=20944
19. 10.416 10.416 ↑ 1.0 1 3,472

Index Scan using ix_objectlink_ctctp on dalimes.objectlink ln1 (cost=0.42..8.39 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=3,472)

  • Output: ln1.olnk_id, ln1.parentotpl_id, ln1.parentlobj_id, ln1.childotpl_id, ln1.childlobj_id, ln1.ts_deletion
  • Index Cond: ((l0.lobj_id = ln1.childlobj_id) AND (ln1.childotpl_id = 53) AND (ln1.parentotpl_id = 35))
  • Buffers: shared hit=14000
20. 6.944 6.944 ↑ 1.0 1 3,472

Index Scan using ixu_o_job_id on dalimes.o_job p1 (cost=0.14..0.49 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=3,472)

  • Output: p1.outerpage_scalingvalue, p1.new_useissuescaling, p1.new_routingmargins, p1.lobj_isroot, p1.blocksize_back, p1.lobj_id, p1.new_defaultoffsety, p1.new_pageorderworkflow, p1.segmenttrim_overfold, p1.new_defaultrotation, p1.new_publicationdate, p1.new_defaultoffsetx, p1.new_collatingmarksposition, p1.new_simulation, p1.new_shrinkouterribon, p1.new_jobworkflow, p1.new_inhousepostprocessing, p1.syshierarchy, p1.printedtrim_footer, p1.parent_node_id, p1.outerpage_creepvalue, p1.segmenttrim_outer, p1.lobj_name, p1.segmenttrim_routingmargins, p1.new_folding, p1.new_lockshortname, p1.new_priority, p1.maximalscaling_use, p1.maximalscaling_value, p1.new_reversedview, p1.new_trimmedwidth, p1.new_safetylr, p1.ostt_list, p1.lobj_version, p1.new_exportasrss, p1.new_formworkflow, p1.new_rolemasks, p1.nw_denyupdateofpublicationda, p1.new_trimmedheight, p1.ts_deletion, p1.new_shiftmark, p1.new_site, p1.new_iccname, p1.maximalscaling_steps, p1.ts_creation, p1.printedtrim_head, p1.printedtrim_face, p1.new_skalingmethod, p1.outerpage_scalingstartvalue, p1.segmenttrim_gutter, p1.new_bleedleftandright, p1.new_usesectionscaling, p1.new_pagecuttoff, p1.ts_lastchange, p1.commentulong, p1.new_usenbpageforskaling, p1.creationuser_code, p1.new_safetytb, p1.new_creepingreference, p1.new_applycreeping, p1.new_compensatebracketeffect, p1.segmenttrim_millingdepth, p1.blocksize_front, p1.segmenttrim_footer, p1.new_bleedtopandbottom, p1.new_uselinearissuescaling, p1.innerpage_scalingvalue, p1.maximalscaling_stepsperform, p1.innerpage_creepvalue, p1.commentushort, p1.new_reelpostprocessing, p1.node_id, p1.new_uselinearsectionscaling, p1.new_mainpageorder, p1.ostt_value, p1.lastchangeuser_code, p1.segmenttrim_head
  • Index Cond: ((p1.lobj_id = ln1.parentlobj_id) AND (p1.ts_deletion = 0))
  • Buffers: shared hit=6944
21. 6.944 6.944 ↑ 1.0 1 3,472

Index Scan using ix_objectlink_ctctp on dalimes.objectlink ln1_1 (cost=0.42..3.26 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=3,472)

  • Output: ln1_1.olnk_id, ln1_1.parentotpl_id, ln1_1.parentlobj_id, ln1_1.childotpl_id, ln1_1.childlobj_id, ln1_1.ts_deletion
  • Index Cond: ((ln1_1.childlobj_id = ln1_2.childlobj_id) AND (ln1_1.childotpl_id = 53) AND (ln1_1.parentotpl_id = 35))
  • Buffers: shared hit=14000
22. 0.124 0.124 ↑ 1.0 1 62

Index Only Scan using ixu_o_job_id on dalimes.o_job p1_1 (cost=0.14..0.49 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=62)

  • Output: p1_1.lobj_id, p1_1.ts_deletion, p1_1.syshierarchy, p1_1.lobj_name
  • Index Cond: ((p1_1.lobj_id = ln1_1.parentlobj_id) AND (p1_1.ts_deletion = 0))
  • Heap Fetches: 62
  • Buffers: shared hit=124
Execution time : 21,032.675 ms