explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kBNI

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Merge Join (cost=247,266,197.45..247,536,800.84 rows=1 width=54) (actual rows= loops=)

  • Merge Cond: (((CASE WHEN ("position"((job.lobj_name)::text, '_'::text) = 0) THEN ''::text ELSE substr((job.lobj_name)::text, ("position"((job.lobj_name)::text, '_'::text) + 1)) END) = ((import1.jahr)::text)) AND ((((SubPlan 2)))::text = ((import1.objekt)::text)) AND (((CASE WHEN ("position"((job.lobj_name)::text, '_'::text) <= 1) THEN ''::text ELSE substr((job.lobj_name)::text, 1, ("position"((job.lobj_name)::text, '_'::text) - 1)) END)::integer) = import1.heft))
  • Join Filter: ((import1.pageorder)::text = (pgo.lobj_name)::text)
2. 0.000 0.000 ↓ 0.0

Unique (cost=247,266,196.24..247,430,491.12 rows=3,865,762 width=1,267) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Sort (cost=247,266,196.24..247,275,860.64 rows=3,865,762 width=1,267) (actual rows= loops=)

  • Sort Key: (CASE WHEN ("position"((job.lobj_name)::text, '_'::text) = 0) THEN ''::text ELSE substr((job.lobj_name)::text, ("position"((job.lobj_name)::text, '_'::text) + 1)) END), ((SubPlan 2)), ((CASE WHEN ("position"((job.lobj_name)::text, '_'::text) <= 1) THEN ''::text ELSE substr((job.lobj_name)::text, 1, ("position"((job.lobj_name)::text, '_'::text) - 1)) END)::integer), (to_char(('1970-01-01 00:00:00'::timestamp without time zone + (((job.new_publicationdate / 1000))::double precision * '00:00:01'::interval)), 'DD.MM.YYYY'::text)), pgo.lobj_name, wi.virtualstatus, fb.lobj_name, ((SubPlan 3)), fb.settings_ruleid, (COALESCE(ir.ruletag, ws.slot_name)), (CASE WHEN ((CASE rl.lobj_name WHEN 'Composite'::text THEN rlc.filespec_url WHEN 'SepComposite'::text THEN rlc.filespec_url WHEN 'Resource'::text THEN COALESCE(rlc.filespec_url, rl.filespec_url) ELSE rl.filespec_url END) IS NOT NULL) THEN (COALESCE((SubPlan 1), ((substr(((CASE rl.lobj_name WHEN 'Composite'::text THEN rlc.filespec_url WHEN 'SepComposite'::text THEN rlc.filespec_url WHEN 'Resource'::text THEN COALESCE(rlc.filespec_url, rl.filespec_url) ELSE rl.filespec_url END))::text, 1, 7) || CASE WHEN ("position"(substr(((CASE rl.lobj_name WHEN 'Composite'::text THEN rlc.filespec_url WHEN 'SepComposite'::text THEN rlc.filespec_url WHEN 'Resource'::text THEN COALESCE(rlc.filespec_url, rl.filespec_url) ELSE rl.filespec_url END))::text, 8), '/'::text) <= 1) THEN ''::text ELSE substr(substr(((CASE rl.lobj_name WHEN 'Composite'::text THEN rlc.filespec_url WHEN 'SepComposite'::text THEN rlc.filespec_url WHEN 'Resource'::text THEN COALESCE(rlc.filespec_url, rl.filespec_url) ELSE rl.filespec_url END))::text, 8), 1, ("position"(substr(((CASE rl.lobj_name WHEN 'Composite'::text THEN rlc.filespec_url WHEN 'SepComposite'::text THEN rlc.filespec_url WHEN 'Resource'::text THEN COALESCE(rlc.filespec_url, rl.filespec_url) ELSE rl.filespec_url END))::text, 8), '/'::text) - 1)) END) || '/'::text)) || CASE WHEN ("position"(substr(((CASE rl.lobj_name WHEN 'Composite'::text THEN rlc.filespec_url WHEN 'SepComposite'::text THEN rlc.filespec_url WHEN 'Resource'::text THEN COALESCE(rlc.filespec_url, rl.filespec_url) ELSE rl.filespec_url END))::text, 8), '/'::text) = 0) THEN ''::text ELSE substr(substr(((CASE rl.lobj_name WHEN 'Composite'::text THEN rlc.filespec_url WHEN 'SepComposite'::text THEN rlc.filespec_url WHEN 'Resource'::text THEN COALESCE(rlc.filespec_url, rl.filespec_url) ELSE rl.filespec_url END))::text, 8), ("position"(substr(((CASE rl.lobj_name WHEN 'Composite'::text THEN rlc.filespec_url WHEN 'SepComposite'::text THEN rlc.filespec_url WHEN 'Resource'::text THEN COALESCE(rlc.filespec_url, rl.filespec_url) ELSE rl.filespec_url END))::text, 8), '/'::text) + 1)) END) ELSE NULL::text END), (CASE rl.lobj_name WHEN 'Composite'::text THEN rlc.filespec_url WHEN 'SepComposite'::text THEN rlc.filespec_url WHEN 'Resource'::text THEN COALESCE(rlc.filespec_url, rl.filespec_url) ELSE rl.filespec_url END), sto.nfs, sto.url, sto.volume_login, sto.volume_password
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=239,013,092.49..244,702,721.01 rows=3,865,762 width=1,267) (actual rows= loops=)

  • Hash Cond: ((CASE WHEN ("position"(substr((COALESCE(rlc.filespec_url, rl.filespec_url))::text, 8, 64), '/'::text) <= 1) THEN ''::text ELSE substr(substr((COALESCE(rlc.filespec_url, rl.filespec_url))::text, 8, 64), 1, ("position"(substr((COALESCE(rlc.filespec_url, rl.filespec_url))::text, 8, 64), '/'::text) - 1)) END) = (sto.volumeid)::text)
5. 0.000 0.000 ↓ 0.0

Unique (cost=239,013,091.25..239,148,392.92 rows=3,865,762 width=271) (actual rows= loops=)

6.          

Initplan (for Unique)

7. 0.000 0.000 ↓ 0.0

Seq Scan on objecttemplate (cost=0.00..1.86 rows=1 width=4) (actual rows= loops=)

  • Filter: ((otpl_name)::text = 'FileBag'::text)
8. 0.000 0.000 ↓ 0.0

Seq Scan on objecttemplate objecttemplate_1 (cost=0.00..1.86 rows=1 width=4) (actual rows= loops=)

  • Filter: ((otpl_name)::text = 'RunList'::text)
9. 0.000 0.000 ↓ 0.0

Seq Scan on objecttemplate objecttemplate_2 (cost=0.00..1.86 rows=1 width=4) (actual rows= loops=)

  • Filter: ((otpl_name)::text = 'RunList'::text)
10. 0.000 0.000 ↓ 0.0

Seq Scan on objecttemplate objecttemplate_3 (cost=0.00..1.86 rows=1 width=4) (actual rows= loops=)

  • Filter: ((otpl_name)::text = 'RunList'::text)
11. 0.000 0.000 ↓ 0.0

Seq Scan on objecttemplate objecttemplate_4 (cost=0.00..1.86 rows=1 width=4) (actual rows= loops=)

  • Filter: ((otpl_name)::text = 'Job'::text)
12. 0.000 0.000 ↓ 0.0

Seq Scan on objecttemplate objecttemplate_5 (cost=0.00..1.86 rows=1 width=4) (actual rows= loops=)

  • Filter: ((otpl_name)::text = 'PageOrder'::text)
13. 0.000 0.000 ↓ 0.0

Sort (cost=239,013,080.07..239,022,744.48 rows=3,865,762 width=271) (actual rows= loops=)

  • Sort Key: ((SubPlan 2)), ((CASE WHEN ("position"((job.lobj_name)::text, '_'::text) <= 1) THEN ''::text ELSE substr((job.lobj_name)::text, 1, ("position"((job.lobj_name)::text, '_'::text) - 1)) END)::integer), job.new_publicationdate, (CASE WHEN ("position"((job.lobj_name)::text, '_'::text) = 0) THEN ''::text ELSE substr((job.lobj_name)::text, ("position"((job.lobj_name)::text, '_'::text) + 1)) END), job.lobj_name, wi.virtualstatus, fb.settings_ruleid, ((SubPlan 3)), (COALESCE(ir.ruletag, ws.slot_name)), pgo.lobj_name, fb.lobj_name, (CASE rl.lobj_name WHEN 'Composite'::text THEN rlc.filespec_url WHEN 'SepComposite'::text THEN rlc.filespec_url WHEN 'Resource'::text THEN COALESCE(rlc.filespec_url, rl.filespec_url) ELSE rl.filespec_url END), (CASE WHEN ("position"(substr((COALESCE(rlc.filespec_url, rl.filespec_url))::text, 8, 64), '/'::text) <= 1) THEN ''::text ELSE substr(substr((COALESCE(rlc.filespec_url, rl.filespec_url))::text, 8, 64), 1, ("position"(substr((COALESCE(rlc.filespec_url, rl.filespec_url))::text, 8, 64), '/'::text) - 1)) END)
14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,753,685.79..238,101,237.34 rows=3,865,762 width=271) (actual rows= loops=)

  • Hash Cond: ((fb.settings_ruleid)::double precision = (ir.rule_id)::double precision)
  • Join Filter: (fb.settings_ruleid IS NOT NULL)
  • Filter: ((fb.lobj_name IS NULL) OR ((COALESCE(ir.ruletag, ws.slot_name))::text <> 'Working'::text))
15. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,753,584.83..2,187,255.45 rows=3,885,188 width=268) (actual rows= loops=)

  • Hash Cond: (rlp_rlc.parentlobj_id = rl.lobj_id)
16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.25..321,995.15 rows=45,990 width=83) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Index Only Scan using ix_objectlink_child_id on objectlink rlp_rlc (cost=0.56..101,981.32 rows=45,990 width=16) (actual rows= loops=)

  • Index Cond: ((childotpl_id = $5) AND (ts_deletion = 0) AND (parentotpl_id = $6))
18. 0.000 0.000 ↓ 0.0

Index Only Scan using ixu_o_runlist_run_url on o_runlist rlc (cost=0.69..4.77 rows=1 width=83) (actual rows= loops=)

  • Index Cond: (lobj_id = rlp_rlc.childlobj_id)
19. 0.000 0.000 ↓ 0.0

Hash (cost=1,594,988.72..1,594,988.72 rows=3,885,188 width=201) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,202,396.31..1,594,988.72 rows=3,885,188 width=201) (actual rows= loops=)

  • Hash Cond: (fb_rl.parentlobj_id = fb.lobj_id)
21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.25..322,110.12 rows=45,990 width=99) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Index Only Scan using ix_objectlink_child_id on objectlink fb_rl (cost=0.56..101,981.32 rows=45,990 width=16) (actual rows= loops=)

  • Index Cond: ((childotpl_id = $4) AND (ts_deletion = 0) AND (parentotpl_id = $3))
23. 0.000 0.000 ↓ 0.0

Index Only Scan using ixu_o_runlist_run_url on o_runlist rl (cost=0.69..4.78 rows=1 width=91) (actual rows= loops=)

  • Index Cond: ((lobj_id = fb_rl.childlobj_id) AND (ts_deletion = 0))
24. 0.000 0.000 ↓ 0.0

Hash (cost=1,085,505.92..1,085,505.92 rows=3,886,171 width=118) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=851,179.14..1,085,505.92 rows=3,886,171 width=118) (actual rows= loops=)

  • Merge Cond: (((CASE WHEN ((ws.slot_value)::text ~~ '%|%'::text) THEN CASE WHEN ("position"(substr((ws.slot_value)::text, 9, 18), '|'::text) <= 1) THEN ''::text ELSE substr(substr((ws.slot_value)::text, 9, 18), 1, ("position"(substr((ws.slot_value)::text, 9, 18), '|'::text) - 1)) END ELSE substr((ws.slot_value)::text, 9, 18) END)::numeric(38,0)) = ((fb.lobj_id)::numeric))
26. 0.000 0.000 ↓ 0.0

Sort (cost=18,091.93..18,092.36 rows=169 width=90) (actual rows= loops=)

  • Sort Key: ((CASE WHEN ((ws.slot_value)::text ~~ '%|%'::text) THEN CASE WHEN ("position"(substr((ws.slot_value)::text, 9, 18), '|'::text) <= 1) THEN ''::text ELSE substr(substr((ws.slot_value)::text, 9, 18), 1, ("position"(substr((ws.slot_value)::text, 9, 18), '|'::text) - 1)) END ELSE substr((ws.slot_value)::text, 9, 18) END)::numeric(38,0))
27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.11..18,085.68 rows=169 width=90) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.42..14,823.17 rows=7 width=71) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..14,783.16 rows=5 width=62) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..14,573.96 rows=48 width=32) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on o_job job (cost=0.00..355.95 rows=2,770 width=24) (actual rows= loops=)

  • Filter: ((new_publicationdate > 0) AND (ts_deletion = 0))
32. 0.000 0.000 ↓ 0.0

Index Only Scan using pk_objectlink on objectlink job_pgo (cost=0.56..5.12 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((parentotpl_id = $7) AND (parentlobj_id = job.lobj_id) AND (ts_deletion = 0) AND (childotpl_id = $8))
33. 0.000 0.000 ↓ 0.0

Index Only Scan using ixu_o_pageorder_id on o_pageorder pgo (cost=0.42..4.35 rows=1 width=30) (actual rows= loops=)

  • Index Cond: ((lobj_id = job_pgo.childlobj_id) AND (ts_deletion = 0))
34. 0.000 0.000 ↓ 0.0

Index Scan using ix_wflinstance_wflinst_orgobj on wflinstance wi (cost=0.43..7.99 rows=1 width=33) (actual rows= loops=)

  • Index Cond: ((ts_deletion = 0) AND (wflinst_orgobj = pgo.lobj_id))
35. 0.000 0.000 ↓ 0.0

Index Scan using ixu_wflslot_wflnamekey on wflslot ws (cost=0.70..465.75 rows=32 width=31) (actual rows= loops=)

  • Index Cond: ((ts_deletion = 0) AND (wflinst_id = wi.wflinst_id))
  • Filter: (((slot_type)::text = 'DBOBJ'::text) AND ((slot_name)::text <> ALL ('{_LinkedObjects,_EditedObjects,_Link,_CreatedObjects,_PreviousRL,_Previous,#PreviewRef,_CheckDouble}'::text[])))
36. 0.000 0.000 ↓ 0.0

Materialize (cost=833,087.21..856,082.30 rows=4,599,019 width=42) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Sort (cost=833,087.21..844,584.75 rows=4,599,019 width=42) (actual rows= loops=)

  • Sort Key: ((fb.lobj_id)::numeric)
38. 0.000 0.000 ↓ 0.0

Seq Scan on o_filebag fb (cost=0.00..182,662.19 rows=4,599,019 width=42) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=100.94..100.94 rows=2 width=7) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on inputrule ir (cost=0.00..100.94 rows=2 width=7) (actual rows= loops=)

  • Filter: ((ruletag)::text = 'HQH'::text)
41.          

SubPlan (for Hash Left Join)

42. 0.000 0.000 ↓ 0.0

Hash Join (cost=14.19..58.24 rows=1 width=4) (actual rows= loops=)

  • Hash Cond: (prd_job.parentlobj_id = prd.lobj_id)
43. 0.000 0.000 ↓ 0.0

Index Scan using ix_objectlink_ctctp on objectlink prd_job (cost=0.56..44.56 rows=10 width=8) (actual rows= loops=)

  • Index Cond: (childlobj_id = job.lobj_id)
44. 0.000 0.000 ↓ 0.0

Hash (cost=10.50..10.50 rows=250 width=12) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on o_product prd (cost=0.00..10.50 rows=250 width=12) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Seq Scan on inputpipe (cost=0.00..2.73 rows=1 width=15) (actual rows= loops=)

  • Filter: ((pipeid)::text = (fb.settings_pipeid)::text)
47. 0.000 0.000 ↓ 0.0

Hash (cost=1.11..1.11 rows=11 width=78) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Seq Scan on storageinfo sto (cost=0.00..1.11 rows=11 width=78) (actual rows= loops=)

49.          

SubPlan (for Hash Left Join)

50. 0.000 0.000 ↓ 0.0

Seq Scan on storageinfo sto_1 (cost=0.00..1.36 rows=1 width=41) (actual rows= loops=)

  • Filter: ((volumeid)::text = CASE WHEN ("position"(substr(((CASE rl.lobj_name WHEN 'Composite'::text THEN rlc.filespec_url WHEN 'SepComposite'::text THEN rlc.filespec_url WHEN 'Resource'::text THEN COALESCE(rlc.filespec_url, rl.filespec_url) ELSE rl.filespec_url END))::text, 8, 64), '/'::text) <= 1) THEN ''::text ELSE substr(substr(((CASE rl.lobj_name WHEN 'Composite'::text THEN rlc.filespec_url WHEN 'SepComposite'::text THEN rlc.filespec_url WHEN 'Resource'::text THEN COALESCE(rlc.filespec_url, rl.filespec_url) ELSE rl.filespec_url END))::text, 8, 64), 1, ("position"(substr(((CASE rl.lobj_name WHEN 'Composite'::text THEN rlc.filespec_url WHEN 'SepComposite'::text THEN rlc.filespec_url WHEN 'Resource'::text THEN COALESCE(rlc.filespec_url, rl.filespec_url) ELSE rl.filespec_url END))::text, 8, 64), '/'::text) - 1)) END)
51. 0.000 0.000 ↓ 0.0

Sort (cost=1.21..1.21 rows=2 width=144) (actual rows= loops=)

  • Sort Key: ((import1.jahr)::text), ((import1.objekt)::text), import1.heft
52. 0.000 0.000 ↓ 0.0

Foreign Scan on import1 (cost=0.00..1.20 rows=2 width=144) (actual rows= loops=)

  • Foreign File: /scripts/importfile.csv
  • Foreign File Size: 348