RE: [EXTERNAL] Re: Performance down with JDBC 42
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 Sent: Saturday, November 4, 2023 11:07 PM To: Abraham, Danny ; psql-performance Subject: [EXTERNAL] Re: Performance down with JDBC 42 On Sat, 2023-11-04 at 19:08 +, 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
Re: [EXTERNAL] Re: Performance down with JDBC 42
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 Sent: Saturday, November 4, 2023 11:07 PM To: Abraham, Danny ; psql-performance Subject: [EXTERNAL] Re: Performance down with JDBC 42 On Sat, 2023-11-04 at 19:08 +, 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
Re: [EXTERNAL] Re: Performance down with JDBC 42
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 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 > Sent: Saturday, November 4, 2023 11:07 PM > To: Abraham, Danny ; psql-performance > > Subject: [EXTERNAL] Re: Performance down with JDBC 42 > >> On Sat, 2023-11-04 at 19:08 +, 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
Re: [EXTERNAL] Re: Performance down with JDBC 42
On Sun, Nov 5, 2023 at 11:20 AM Abraham, Danny 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
RE: [EXTERNAL] Re: Performance down with JDBC 42
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 ro
Re: [EXTERNAL] Re: Performance down with JDBC 42
On Mon, 6 Nov 2023 at 08:37, Abraham, Danny 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
