Thanks for the help.
Both plans refer to the same DB.
#1 – Fast – using psql or old JDBC driver
==>
Sort (cost=13113.27..13113.33 rows=24 width=622)
Output: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character varying(400))
Sort Key: dm.calname, dm.jobyear
-> HashAggregate (cost=13112.24..13112.48 rows=24 width=622)
Output: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character varying(400))
Group Key: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character varying(400))
-> Append (cost=4603.96..13112.00 rows=24 width=622)
-> Unique (cost=4603.96..4604.20 rows=19 width=535)
Output: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character varying(400))
-> Sort (cost=4603.96..4604.01 rows=19 width=535)
Output: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character varying(400))
Sort Key: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character varying(400))
-> Nested Loop (cost=0.00..4603.56 rows=19 width=535)
Output: dm.calname, dm.jobyear, dm.caltype, (dm.daymask)::character varying(400)
Join Filter: (((dm.calname)::text = (jd.dayscal)::text) OR ((dm.calname)::text = (jd.weekcal)::text) OR ((dm.calname)::text = (jd.confcal)::text))
-> Seq Scan on public.cms_datemm dm (cost=0.00..16.33 rows=171 width=389)
Output: dm.calname, dm.jobyear, dm.daymask, dm.caltype, dm.caldesc
Filter: ((dm.jobyear >= '2021'::bpchar) AND (dm.jobyear <= '2025'::bpchar))
-> Materialize (cost=0.00..4559.84 rows=8 width=3)
Output: jd.dayscal, jd.weekcal, jd.confcal
-> Seq Scan on public.cms_jobdef jd (cost=0.00..4559.80 rows=8 width=3)
Output: jd.dayscal, jd.weekcal, jd.confcal
Filter: (((jd.schedtab)::text = 'PZL-ZETA_REDIS_UTILITY_PSE'::text) OR ((jd.schedtab)::text ~~ 'PZL-ZETA_REDIS_UTILITY_PSE/%'::text))
-> Unique (cost=3857.44..3857.46 rows=1 width=535)
Output: dm_1.calname, dm_1.jobyear, dm_1.caltype, ((dm_1.daymask)::character varying(400))
-> Sort (cost=3857.44..3857.45 rows=1 width=535)
Output: dm_1.calname, dm_1.jobyear, dm_1.caltype, ((dm_1.daymask)::character varying(400))
Sort Key: dm_1.calname, dm_1.jobyear, dm_1.caltype, ((dm_1.daymask)::character varying(400))
-> Nested Loop (cost=0.30..3857.43 rows=1 width=535)
Output: dm_1.calname, dm_1.jobyear, dm_1.caltype, (dm_1.daymask)::character varying(400)
Join Filter: (((dm_1.calname)::text = (tag.dayscal)::text) OR ((dm_1.calname)::text = (tag.weekcal)::text) OR ((dm_1.calname)::text = (tag.confcal)::text))
-> Nested Loop (cost=0.30..3838.11 rows=1 width=3)
Output: tag.dayscal, tag.weekcal, tag.confcal
Inner Unique: true
-> Seq Scan on public.cms_tag tag (cost=0.00..30.96 rows=1396 width=7)
Output: tag.tagname, tag.groupid, tag.maxwait, tag.cal_andor, tag.monthstr, tag.dayscal, tag.weekcal, tag.confcal, tag.shift, tag.retro, tag.daystr, tag.wdaystr, tag.tagfrom, tag.tagtill, tag.roworder, tag.exclude_rbc
-> Memoize (cost=0.30..4.02 rows=1 width=4)
Output: jd_1.jobno
Cache Key: tag.groupid
Cache Mode: logical
-> Index Scan using job on public.cms_jobdef jd_1 (cost=0.29..4.01 rows=1 width=4)
Output: jd_1.jobno
Index Cond: (jd_1.jobno = tag.groupid)
Filter: (((jd_1.schedtab)::text = 'PZL-ZETA_REDIS_UTILITY_PSE'::text) OR ((jd_1.schedtab)::text ~~ 'PZL-ZETA_REDIS_UTILITY_PSE/%'::text))
-> Seq Scan on public.cms_datemm dm_1 (cost=0.00..16.33 rows=171 width=389)
Output: dm_1.calname, dm_1.jobyear, dm_1.daymask, dm_1.caltype, dm_1.caldesc
Filter: ((dm_1.jobyear >= '2021'::bpchar) AND (dm_1.jobyear <= '2025'::bpchar))
-> Unique (cost=4649.93..4649.98 rows=4 width=535)
Output: dm_2.calname, dm_2.jobyear, dm_2.caltype, ((dm_2.daymask)::character varying(400))
-> Sort (cost=4649.93..4649.94 rows=4 width=535)
Output: dm_2.calname, dm_2.jobyear, dm_2.caltype, ((dm_2.daymask)::character varying(400))
Sort Key: dm_2.calname, dm_2.jobyear, dm_2.caltype, ((dm_2.daymask)::character varying(400))
-> Nested Loop (cost=0.56..4649.89 rows=4 width=535)
Output: dm_2.calname, dm_2.jobyear, dm_2.caltype, (dm_2.daymask)::character varying(400)
Join Filter: (((dm_2.calname)::text = (tag_1.dayscal)::text) OR ((dm_2.calname)::text = (tag_1.weekcal)::text) OR ((dm_2.calname)::text = (tag_1.confcal)::text))
-> Seq Scan on public.cms_datemm dm_2 (cost=0.00..16.33 rows=171 width=389)
Output: dm_2.calname, dm_2.jobyear, dm_2.daymask, dm_2.caltype, dm_2.caldesc
Filter: ((dm_2.jobyear >= '2021'::bpchar) AND (dm_2.jobyear <= '2025'::bpchar))
-> Materialize (cost=0.56..4626.72 rows=2 width=3)
Output: tag_1.dayscal, tag_1.weekcal, tag_1.confcal
-> Nested Loop (cost=0.56..4626.71 rows=2 width=3)
Output: tag_1.dayscal, tag_1.weekcal, tag_1.confcal
Inner Unique: true
-> Nested Loop (cost=0.29..4626.32 rows=1 width=5)
Output: tl.tagname
-> Seq Scan on public.cms_jobdef jd_2 (cost=0.00..4559.80 rows=8 width=4)
Output: jd_2.jobname, jd_2.jobno, jd_2.descript, jd_2.applic, jd_2.applgroup, jd_2.schedtab, jd_2.author, jd_2.owner, jd_2.priority, jd_2.critical, jd_2.cyclic, jd_2.retro, jd_2.autoarch, jd_2.taskclass, jd_2.cyclicint, jd_2.tasktype, jd_2.datemem, jd_2.nodegrp, jd_2.platform, jd_2.nodeid, jd_2.doclib, jd_2.docmem, jd_2.memlib, jd_2.memname, jd_2.overlib, jd_2.cmdline, jd_2.maxrerun, jd_2.maxdays, jd_2.maxruns, jd_2.fromtime, jd_2.until, jd_2.maxwait, jd_2.daystr, jd_2.wdaystr, jd_2.monthstr, jd_2.ajfsonstr, jd_2.conf, jd_2.unknowntim, jd_2.dayscal, jd_2.weekcal, jd_2.confcal, jd_2.cal_andor, jd_2.shift, jd_2.adjust_cond, jd_2.startendcycind, jd_2.creationuserid, jd_2.creationdatetime, jd_2.changeuserid, jd_2.changedatetime, jd_2.relationship, jd_2.groupid, jd_2.tabrowno, jd_2.multiagent, jd_2.appltype, jd_2.timezone, jd_2.statemsk, jd_2.applver, jd_2.timeref, jd_2.actfrom, jd_2.acttill, jd_2.cmver, jd_2.applform, jd_2.instream_ind, jd_2.instream_script, jd_2.run_times, jd_2.interval_sequence, jd_2.tolerance, jd_2.cyclic_type, jd_2.removeatonce, jd_2.dayskeepinnotok, jd_2.delay, jd_2.end_folder, jd_2.is_reference, jd_2.referenced_path
Filter: (((jd_2.schedtab)::text = 'PZL-ZETA_REDIS_UTILITY_PSE'::text) OR ((jd_2.schedtab)::text ~~ 'PZL-ZETA_REDIS_UTILITY_PSE/%'::text))
-> Index Scan using job_tag on public.cms_taglink tl (cost=0.29..8.30 rows=1 width=9)
Output: tl.tagname, tl.groupid, tl.jobno, tl.roworder, tl.exclude_rbc
Index Cond: (tl.jobno = jd_2.jobno)
Filter: (tl.groupid = 0)
-> Index Scan using gro_tag on public.cms_tag tag_1 (cost=0.28..0.39 rows=1 width=14)
Output: tag_1.tagname, tag_1.groupid, tag_1.maxwait, tag_1.cal_andor, tag_1.monthstr, tag_1.dayscal, tag_1.weekcal, tag_1.confcal, tag_1.shift, tag_1.retro, tag_1.daystr, tag_1.wdaystr, tag_1.tagfrom, tag_1.tagtill, tag_1.roworder, tag_1.exclude_rbc
Index Cond: ((tag_1.groupid = 0) AND ((tag_1.tagname)::text = (tl.tagname)::text))
==>
Slow – when using JDBC 42
==>
Sort (cost=11316.99..11317.00 rows=3 width=622)
Output: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character varying(400))
Sort Key: dm.calname, dm.jobyear
-> HashAggregate (cost=11316.91..11316.94 rows=3 width=622)
Output: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character varying(400))
Group Key: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character varying(400))
-> Append (cost=10252.89..11316.88 rows=3 width=622)
-> Unique (cost=10252.89..10252.92 rows=1 width=535)
Output: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character varying(400))
-> Sort (cost=10252.89..10252.89 rows=3 width=535)
Output: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character varying(400))
Sort Key: dm.calname, dm.jobyear, dm.caltype, ((dm.daymask)::character varying(400))
-> Nested Loop (cost=0.14..10252.86 rows=3 width=535)
Output: dm.calname, dm.jobyear, dm.caltype, (dm.daymask)::character varying(400)
Join Filter: (((dm.calname)::text = (jd.dayscal)::text) OR ((dm.calname)::text = (jd.weekcal)::text) OR ((dm.calname)::text = (jd.confcal)::text))
-> Index Scan using calendar on public.cms_datemm dm (cost=0.14..14.38 rows=1 width=389)
Output: dm.calname, dm.jobyear, dm.daymask, dm.caltype, dm.caldesc
Index Cond: ((dm.jobyear >= ($3)::bpchar) AND (dm.jobyear <= ($4)::bpchar))
-> Seq Scan on public.cms_jobdef jd (cost=0.00..10235.19 rows=188 width=3)
Output: jd.dayscal, jd.weekcal, jd.confcal
Filter: (((jd.schedtab)::text = ($1)::text) OR ((jd.schedtab)::text ~~ ($2)::text))
-> Unique (cost=180.91..180.93 rows=1 width=535)
Output: dm_1.calname, dm_1.jobyear, dm_1.caltype, ((dm_1.daymask)::character varying(400))
-> Sort (cost=180.91..180.92 rows=1 width=535)
Output: dm_1.calname, dm_1.jobyear, dm_1.caltype, ((dm_1.daymask)::character varying(400))
Sort Key: dm_1.calname, dm_1.jobyear, dm_1.caltype, ((dm_1.daymask)::character varying(400))
-> Nested Loop (cost=0.56..180.90 rows=1 width=535)
Output: dm_1.calname, dm_1.jobyear, dm_1.caltype, (dm_1.daymask)::character varying(400)
Inner Unique: true
-> Nested Loop (cost=0.14..74.77 rows=18 width=393)
Output: dm_1.calname, dm_1.jobyear, dm_1.caltype, dm_1.daymask, tag.groupid
Join Filter: (((dm_1.calname)::text = (tag.dayscal)::text) OR ((dm_1.calname)::text = (tag.weekcal)::text) OR ((dm_1.calname)::text = (tag.confcal)::text))
-> Index Scan using calendar on public.cms_datemm dm_1 (cost=0.14..14.38 rows=1 width=389)
Output: dm_1.calname, dm_1.jobyear, dm_1.daymask, dm_1.caltype, dm_1.caldesc
Index Cond: ((dm_1.jobyear >= ($7)::bpchar) AND (dm_1.jobyear <= ($8)::bpchar))
-> Seq Scan on public.cms_tag tag (cost=0.00..35.96 rows=1396 width=7)
Output: tag.tagname, tag.groupid, tag.maxwait, tag.cal_andor, tag.monthstr, tag.dayscal, tag.weekcal, tag.confcal, tag.shift, tag.retro, tag.daystr, tag.wdaystr, tag.tagfrom, tag.tagtill, tag.roworder, tag.exclude_rbc
-> Index Scan using job on public.cms_jobdef jd_1 (cost=0.41..5.89 rows=1 width=4)
Output: jd_1.jobno
Index Cond: (jd_1.jobno = tag.groupid)
Filter: (((jd_1.schedtab)::text = ($5)::text) OR ((jd_1.schedtab)::text ~~ ($6)::text))
-> Unique (cost=882.97..882.99 rows=1 width=535)
Output: dm_2.calname, dm_2.jobyear, dm_2.caltype, ((dm_2.daymask)::character varying(400))
-> Sort (cost=882.97..882.98 rows=1 width=535)
Output: dm_2.calname, dm_2.jobyear, dm_2.caltype, ((dm_2.daymask)::character varying(400))
Sort Key: dm_2.calname, dm_2.jobyear, dm_2.caltype, ((dm_2.daymask)::character varying(400))
-> Nested Loop (cost=67.06..882.96 rows=1 width=535)
Output: dm_2.calname, dm_2.jobyear, dm_2.caltype, (dm_2.daymask)::character varying(400)
Inner Unique: true
-> Hash Join (cost=66.64..225.90 rows=104 width=393)
Output: dm_2.calname, dm_2.jobyear, dm_2.caltype, dm_2.daymask, tl.jobno
Hash Cond: ((tl.tagname)::text = (tag_1.tagname)::text)
-> Bitmap Heap Scan on public.cms_taglink tl (cost=16.79..169.52 rows=1098 width=13)
Output: tl.tagname, tl.groupid, tl.jobno, tl.roworder, tl.exclude_rbc
Recheck Cond: (tl.groupid = 0)
-> Bitmap Index Scan on tl_groupid (cost=0.00..16.52 rows=1098 width=0)
Index Cond: (tl.groupid = 0)
-> Hash (cost=49.82..49.82 rows=2 width=404)
Output: dm_2.calname, dm_2.jobyear, dm_2.caltype, dm_2.daymask, tag_1.tagname, tag_1.groupid
-> Nested Loop (cost=9.48..49.82 rows=2 width=404)
Output: dm_2.calname, dm_2.jobyear, dm_2.caltype, dm_2.daymask, tag_1.tagname, tag_1.groupid
Join Filter: (((dm_2.calname)::text = (tag_1.dayscal)::text) OR ((dm_2.calname)::text = (tag_1.weekcal)::text) OR ((dm_2.calname)::text = (tag_1.confcal)::text))
-> Index Scan using calendar on public.cms_datemm dm_2 (cost=0.14..14.38 rows=1 width=389)
Output: dm_2.calname, dm_2.jobyear, dm_2.daymask, dm_2.caltype, dm_2.caldesc
Index Cond: ((dm_2.jobyear >= ($11)::bpchar) AND (dm_2.jobyear <= ($12)::bpchar))
-> Bitmap Heap Scan on public.cms_tag tag_1 (cost=9.34..33.05 rows=137 width=18)
Output: tag_1.tagname, tag_1.groupid, tag_1.maxwait, tag_1.cal_andor, tag_1.monthstr, tag_1.dayscal, tag_1.weekcal, tag_1.confcal, tag_1.shift, tag_1.retro, tag_1.daystr, tag_1.wdaystr, tag_1.tagfrom, tag_1.tagtill, tag_1.roworder, tag_1.exclude_rbc
Recheck Cond: (tag_1.groupid = 0)
-> Bitmap Index Scan on gro_tag (cost=0.00..9.30 rows=137 width=0)
Index Cond: (tag_1.groupid = 0)
-> Index Scan using job on public.cms_jobdef jd_2 (cost=0.41..6.32 rows=1 width=4)
Output: jd_2.jobno
Index Cond: (jd_2.jobno = tl.jobno)
Filter: (((jd_2.schedtab)::text = ($9)::text) OR ((jd_2.schedtab)::text ~~ ($10)::text))
==>
