pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance down with JDBC 42
10+ messages / 6 participants
[nested] [flat]
* Performance down with JDBC 42
@ 2023-11-04 19:08 Abraham, Danny <danny_abraham@bmc.com>
0 siblings, 1 reply; 10+ messages in thread
From: Abraham, Danny @ 2023-11-04 19:08 UTC (permalink / raw)
To: pgsql-performance; +Cc: Abraham, Danny <danny_abraham@bmc.com>
Hi,
Asking for help with a JDBC related issue.
Environment: Linux 7.9 PG 14.9 , very busy PG Server.
A big query - 3 unions and about 10 joins runs :
- 70ms on psql , DBeaver with JDBC 42 and in our Server using old JDBC 9.2
- 2500 ms in our Server using new JDBC 42 driver. ( and this is running many times)
Question: Is there a structured way to identify optimization setup ( Planner Method s ) changes?
Are there any known changes specific to JDBC 42.
Capture a vector of session optimization setup?
Any other Idea ?
Thanks
Danny
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance down with JDBC 42
@ 2023-11-04 21:07 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Abraham, Danny <danny_abraham@bmc.com>
0 siblings, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2023-11-04 21:07 UTC (permalink / raw)
To: Abraham, Danny <danny_abraham@bmc.com>; pgsql-performance
On Sat, 2023-11-04 at 19:08 +0000, Abraham, Danny wrote:
> Asking for help with a JDBC related issue.
> Environment: Linux 7.9 PG 14.9 , very busy PG Server.
>
> A big query - 3 unions and about 10 joins runs :
> - 70ms on psql , DBeaver with JDBC 42 and in our Server using old JDBC 9.2
> - 2500 ms in our Server using new JDBC 42 driver. ( and this is running many times)
>
> Question: Is there a structured way to identify optimization setup ( Planner Method s ) changes?
> Are there any known changes specific to JDBC 42.
What I would do is enable auto_explain and look at the execution plan
when the statement is run by the JDBC driver. Then you can compare the
execution plans and spot the difference.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: [EXTERNAL] Re: Performance down with JDBC 42
@ 2023-11-05 16:20 Abraham, Danny <danny_abraham@bmc.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 3 replies; 10+ messages in thread
From: Abraham, Danny @ 2023-11-05 16:20 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance; +Cc: Abraham, Danny <danny_abraham@bmc.com>; Shivatski, Sagi <sagi_shivatski@bmc.com>
Thanks Laurenz,
Traced two huge plans. They differ.
The fast one does use Materialize and Memoize (the psql).
Is there something in JDBC 42 that blocks these algoruthms?
Thanks again
Danny
-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Saturday, November 4, 2023 11:07 PM
To: Abraham, Danny <danny_abraham@bmc.com>; psql-performance <pgsql-performance@postgresql.org>
Subject: [EXTERNAL] Re: Performance down with JDBC 42
On Sat, 2023-11-04 at 19:08 +0000, Abraham, Danny wrote:
> Asking for help with a JDBC related issue.
> Environment: Linux 7.9 PG 14.9 , very busy PG Server.
>
> A big query - 3 unions and about 10 joins runs :
> - 70ms on psql , DBeaver with JDBC 42 and in our Server using old
> JDBC 9.2
> - 2500 ms in our Server using new JDBC 42 driver. ( and this is
> running many times)
>
> Question: Is there a structured way to identify optimization setup ( Planner Method s ) changes?
> Are there any known changes specific to JDBC 42.
What I would do is enable auto_explain and look at the execution plan when the statement is run by the JDBC driver. Then you can compare the execution plans and spot the difference.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [EXTERNAL] Re: Performance down with JDBC 42
@ 2023-11-05 16:52 Andreas Kretschmer <andreas@a-kretschmer.de>
parent: Abraham, Danny <danny_abraham@bmc.com>
2 siblings, 0 replies; 10+ messages in thread
From: Andreas Kretschmer @ 2023-11-05 16:52 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Am 05.11.23 um 17:20 schrieb Abraham, Danny:
> Thanks Laurenz,
>
> Traced two huge plans. They differ.
> The fast one does use Materialize and Memoize (the psql).
> Is there something in JDBC 42 that blocks these algoruthms?
*maybe* the driver changed some settings. You can check it with
select name, setting from pg_settings where name ~ 'enable';
using the JDBC-connection.
Regards, Andreas
>
> Thanks again
>
> Danny
>
> -----Original Message-----
> From: Laurenz Albe <laurenz.albe@cybertec.at>
> Sent: Saturday, November 4, 2023 11:07 PM
> To: Abraham, Danny <danny_abraham@bmc.com>; psql-performance <pgsql-performance@postgresql.org>
> Subject: [EXTERNAL] Re: Performance down with JDBC 42
>
> On Sat, 2023-11-04 at 19:08 +0000, Abraham, Danny wrote:
>> Asking for help with a JDBC related issue.
>> Environment: Linux 7.9 PG 14.9 , very busy PG Server.
>>
>> A big query - 3 unions and about 10 joins runs :
>> - 70ms on psql , DBeaver with JDBC 42 and in our Server using old
>> JDBC 9.2
>> - 2500 ms in our Server using new JDBC 42 driver. ( and this is
>> running many times)
>>
>> Question: Is there a structured way to identify optimization setup ( Planner Method s ) changes?
>> Are there any known changes specific to JDBC 42.
> What I would do is enable auto_explain and look at the execution plan when the statement is run by the JDBC driver. Then you can compare the execution plans and spot the difference.
>
> Yours,
> Laurenz Albe
--
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [EXTERNAL] Re: Performance down with JDBC 42
@ 2023-11-05 17:34 Frits Hoogland <frits.hoogland@gmail.com>
parent: Abraham, Danny <danny_abraham@bmc.com>
2 siblings, 0 replies; 10+ messages in thread
From: Frits Hoogland @ 2023-11-05 17:34 UTC (permalink / raw)
To: Abraham, Danny <danny_abraham@bmc.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance; Shivatski, Sagi <sagi_shivatski@bmc.com>
Are you absolutely sure that the two databases you’re comparing the executing with are identical, and that the objects involved in the query are physically and logically identical?
The planning is done based on cost/statistics of the objects. If the statistics are different, the planner may come up with another plan.
Frits
> Op 5 nov 2023 om 17:20 heeft Abraham, Danny <danny_abraham@bmc.com> het volgende geschreven:
>
> Thanks Laurenz,
>
> Traced two huge plans. They differ.
> The fast one does use Materialize and Memoize (the psql).
> Is there something in JDBC 42 that blocks these algoruthms?
>
> Thanks again
>
> Danny
>
> -----Original Message-----
> From: Laurenz Albe <laurenz.albe@cybertec.at>
> Sent: Saturday, November 4, 2023 11:07 PM
> To: Abraham, Danny <danny_abraham@bmc.com>; psql-performance <pgsql-performance@postgresql.org>
> Subject: [EXTERNAL] Re: Performance down with JDBC 42
>
>> On Sat, 2023-11-04 at 19:08 +0000, Abraham, Danny wrote:
>> Asking for help with a JDBC related issue.
>> Environment: Linux 7.9 PG 14.9 , very busy PG Server.
>>
>> A big query - 3 unions and about 10 joins runs :
>> - 70ms on psql , DBeaver with JDBC 42 and in our Server using old
>> JDBC 9.2
>> - 2500 ms in our Server using new JDBC 42 driver. ( and this is
>> running many times)
>>
>> Question: Is there a structured way to identify optimization setup ( Planner Method s ) changes?
>> Are there any known changes specific to JDBC 42.
>
> What I would do is enable auto_explain and look at the execution plan when the statement is run by the JDBC driver. Then you can compare the execution plans and spot the difference.
>
> Yours,
> Laurenz Albe
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [EXTERNAL] Re: Performance down with JDBC 42
@ 2023-11-05 19:11 Jeff Janes <jeff.janes@gmail.com>
parent: Abraham, Danny <danny_abraham@bmc.com>
2 siblings, 1 reply; 10+ messages in thread
From: Jeff Janes @ 2023-11-05 19:11 UTC (permalink / raw)
To: Abraham, Danny <danny_abraham@bmc.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance; Shivatski, Sagi <sagi_shivatski@bmc.com>
On Sun, Nov 5, 2023 at 11:20 AM Abraham, Danny <danny_abraham@bmc.com>
wrote:
> Thanks Laurenz,
>
> Traced two huge plans. They differ.
> The fast one does use Materialize and Memoize (the psql).
> Is there something in JDBC 42 that blocks these algoruthms?
Directly blocking those is not likely. Maybe the way the drivers fetch
partial results is different, such that with one the planner knows to
expect only partial results to be fetched and with the other it does not.
So in one case it chooses the fast-start plan, and in the other it
doesn't. But it will be hard to get anywhere if you just dribble
information at us a bit at a time. Can you come up with a self-contained
test case? Or at least show the entirety of both plans?
Cheers,
Jeff
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: [EXTERNAL] Re: Performance down with JDBC 42
@ 2023-11-05 19:37 Abraham, Danny <danny_abraham@bmc.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Abraham, Danny @ 2023-11-05 19:37 UTC (permalink / raw)
To: pgsql-performance; +Cc: Abraham, Danny <danny_abraham@bmc.com>
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))
==>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [EXTERNAL] Re: Performance down with JDBC 42
@ 2023-11-05 19:47 David Rowley <dgrowleyml@gmail.com>
parent: Abraham, Danny <danny_abraham@bmc.com>
0 siblings, 1 reply; 10+ messages in thread
From: David Rowley @ 2023-11-05 19:47 UTC (permalink / raw)
To: Abraham, Danny <danny_abraham@bmc.com>; +Cc: pgsql-performance
On Mon, 6 Nov 2023 at 08:37, Abraham, Danny <danny_abraham@bmc.com> wrote:
>
> Both plans refer to the same DB.
JDBC is making use of PREPARE statements, whereas psql, unless you're
using PREPARE is not.
> #1 – Fast – using psql or old JDBC driver
The absence of any $1 type parameters here shows that's a custom plan
that's planned specifically using the parameter values given.
> Slow – when using JDBC 42
Because this query has $1, $2, etc, that's a generic plan. When
looking up statistics histogram bounds and MCV slots cannot be
checked. Only ndistinct is used. If you have a skewed dataset, then
this might not be very good.
You might find things run better if you adjust postgresql.conf and set
plan_cache_mode = force_custom_plan then select pg_reload_conf();
Please also check the documentation so that you understand the full
implications for that.
David
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [EXTERNAL] Performance down with JDBC 42
@ 2023-11-06 09:24 Frits Hoogland <frits.hoogland@gmail.com>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Frits Hoogland @ 2023-11-06 09:24 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; Abraham, Danny <danny_abraham@bmc.com>; +Cc: pgsql-performance
Very good point from Danny: generic and custom plans.
One thing that is almost certainly not at play here, and is mentioned: there are some specific cases where the planner does not optimise for the query in total to be executed as fast/cheap as possible, but for the first few rows. One reason for that to happen is if a query is used as a cursor.
(Warning: shameless promotion) I did a writeup on JDBC clientside/serverside prepared statements and custom and generic plans: https://dev.to/yugabyte/postgres-query-execution-jdbc-prepared-statements-51e2
The next obvious question then is if something material did change with JDBC for your old and new JDBC versions, I do believe the prepareThreshold did not change.
Frits Hoogland
> On 5 Nov 2023, at 20:47, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Mon, 6 Nov 2023 at 08:37, Abraham, Danny <danny_abraham@bmc.com> wrote:
>>
>> Both plans refer to the same DB.
>
> JDBC is making use of PREPARE statements, whereas psql, unless you're
> using PREPARE is not.
>
>> #1 – Fast – using psql or old JDBC driver
>
> The absence of any $1 type parameters here shows that's a custom plan
> that's planned specifically using the parameter values given.
>
>> Slow – when using JDBC 42
>
> Because this query has $1, $2, etc, that's a generic plan. When
> looking up statistics histogram bounds and MCV slots cannot be
> checked. Only ndistinct is used. If you have a skewed dataset, then
> this might not be very good.
>
> You might find things run better if you adjust postgresql.conf and set
> plan_cache_mode = force_custom_plan then select pg_reload_conf();
>
> Please also check the documentation so that you understand the full
> implications for that.
>
> David
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: [EXTERNAL] Performance down with JDBC 42
@ 2023-11-09 14:00 Abraham, Danny <danny_abraham@bmc.com>
parent: Frits Hoogland <frits.hoogland@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Abraham, Danny @ 2023-11-09 14:00 UTC (permalink / raw)
To: Frits Hoogland <frits.hoogland@gmail.com>; David Rowley <dgrowleyml@gmail.com>; +Cc: pgsql-performance; Abraham, Danny <danny_abraham@bmc.com>; Shivatski, Sagi <sagi_shivatski@bmc.com>
Hi guys,
Thanks for the help.
I was able to recreate the problem , on the same DB, with PSQL only. No JDBC.
A plain run of a complicated query : 50ms
A prepare and then execute of the same query: 2500ms.
The plans are different, as discussed above. The fast one is using Materialize and Memoize.
Thanks
Danny
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2023-11-09 14:00 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-04 19:08 Performance down with JDBC 42 Abraham, Danny <danny_abraham@bmc.com>
2023-11-04 21:07 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-11-05 16:20 ` Abraham, Danny <danny_abraham@bmc.com>
2023-11-05 16:52 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2023-11-05 17:34 ` Frits Hoogland <frits.hoogland@gmail.com>
2023-11-05 19:11 ` Jeff Janes <jeff.janes@gmail.com>
2023-11-05 19:37 ` Abraham, Danny <danny_abraham@bmc.com>
2023-11-05 19:47 ` David Rowley <dgrowleyml@gmail.com>
2023-11-06 09:24 ` Frits Hoogland <frits.hoogland@gmail.com>
2023-11-09 14:00 ` Abraham, Danny <danny_abraham@bmc.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox