explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8AXd

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

Unique (cost=521,116.74..521,116.79 rows=1 width=1,267) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=521,116.74..521,116.75 rows=1 width=1,267) (actual rows= loops=)

  • Sort Key: (to_char(('1970-01-01 00:00:00'::timestamp without time zone + (((x.job_evt / 1000))::double precision * '00:00:01'::interval)), 'DD.MM.YYYY'::text)), x.pageorder_name, x.virtualstatus, x.unum, x.inputpipe, x.ruleid, x.slot_name, (CASE WHEN (x.file_url IS NOT NULL) THEN (COALESCE((SubPlan 1), ((substr((x.file_url)::text, 1, 7) || CASE WHEN ("position"(substr((x.file_url)::text, 8), '/'::text) <= 1) THEN ''::text ELSE substr(substr((x.file_url)::text, 8), 1, ("position"(substr((x.file_url)::text, 8), '/'::text) - 1)) END) || '/'::text)) || CASE WHEN ("position"(substr((x.file_url)::text, 8), '/'::text) = 0) THEN ''::text ELSE substr(substr((x.file_url)::text, 8), ("position"(substr((x.file_url)::text, 8), '/'::text) + 1)) END) ELSE NULL::text END), x.file_url, sto.nfs, sto.url, sto.volume_login, sto.volume_password
3. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=521,114.15..521,116.73 rows=1 width=1,267) (actual rows= loops=)

  • Hash Cond: ((sto.volumeid)::text = x.storageid)
4. 0.000 0.000 ↓ 0.0

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

5. 0.000 0.000 ↓ 0.0

Hash (cost=521,114.13..521,114.13 rows=1 width=1,227) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Subquery Scan on x (cost=521,053.10..521,114.13 rows=1 width=1,227) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

HashAggregate (cost=521,053.10..521,114.12 rows=1 width=271) (actual rows= loops=)

  • Group 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
8.          

Initplan (for HashAggregate)

9. 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)
10. 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)
11. 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)
12. 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)
13. 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)
14. 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)
15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=17,402.60..521,041.89 rows=1 width=271) (actual rows= loops=)

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

Nested Loop Left Join (cost=17,402.60..520,879.89 rows=1 width=268) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=17,401.35..520,870.20 rows=1 width=201) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=17,400.09..298,021.39 rows=22,995 width=118) (actual rows= loops=)

  • Hash Cond: ((fb.lobj_id)::numeric = (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))
19. 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=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=17,400.08..17,400.08 rows=1 width=90) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.97..17,400.08 rows=1 width=90) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.27..16,934.01 rows=1 width=71) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..16,867.95 rows=1 width=55) (actual rows= loops=)

  • Join Filter: (pgo.lobj_id = wi.wflinst_orgobj)
24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..16,859.94 rows=1 width=46) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Seq Scan on o_pageorder pgo (cost=0.00..16,661.14 rows=38 width=30) (actual rows= loops=)

  • Filter: (((lobj_name)::text ~~ 'TST606_188_NO_Seite_000%'::text) AND (ts_deletion = 0))
26. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((childotpl_id = $8) AND (childlobj_id = pgo.lobj_id) AND (ts_deletion = 0) AND (parentotpl_id = $7))
27. 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 = job_pgo.childlobj_id))
28. 0.000 0.000 ↓ 0.0

Index Scan using ixu_o_job_id on o_job job (cost=0.28..66.05 rows=1 width=24) (actual rows= loops=)

  • Index Cond: ((lobj_id = job_pgo.parentlobj_id) AND (ts_deletion = 0))
  • Filter: ((new_publicationdate > 0) AND (CASE WHEN ("position"((lobj_name)::text, '_'::text) = 0) THEN ''::text ELSE substr((lobj_name)::text, ("position"((lobj_name)::text, '_'::text) + 1)) END = '2018'::text) AND ((CASE WHEN ("position"((lobj_name)::text, '_'::text) <= 1) THEN ''::text ELSE substr((lobj_name)::text, 1, ("position"((lobj_name)::text, '_'::text) - 1)) END)::integer = 606) AND (((SubPlan 10))::text = '188'::text))
29.          

SubPlan (for Index Scan)

30. 0.000 0.000 ↓ 0.0

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

  • Hash Cond: (prd_job_1.parentlobj_id = prd_1.lobj_id)
31. 0.000 0.000 ↓ 0.0

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

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

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

33. 0.000 0.000 ↓ 0.0

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

34. 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[])))
35. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.25..9.68 rows=1 width=99) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((parentotpl_id = $3) AND (parentlobj_id = fb.lobj_id) AND (ts_deletion = 0) AND (childotpl_id = $4))
37. 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))
38. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.25..9.68 rows=1 width=83) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((parentotpl_id = $6) AND (parentlobj_id = rl.lobj_id) AND (ts_deletion = 0) AND (childotpl_id = $5))
40. 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)
41. 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)
42.          

SubPlan (for Nested Loop Left Join)

43. 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)
44. 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)
45. 0.000 0.000 ↓ 0.0

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

46. 0.000 0.000 ↓ 0.0

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

47. 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)
48.          

SubPlan (for Hash Right Join)

49. 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((x.file_url)::text, 8, 64), '/'::text) <= 1) THEN ''::text ELSE substr(substr((x.file_url)::text, 8, 64), 1, ("position"(substr((x.file_url)::text, 8, 64), '/'::text) - 1)) END)