pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Frits Hoogland <frits.hoogland@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
To: Abraham, Danny <danny_abraham@bmc.com>
Cc: psql-performance <pgsql-performance@postgresql.org>
Subject: Re: [EXTERNAL] Performance down with JDBC 42
Date: Mon, 6 Nov 2023 10:24:43 +0100
Message-ID: <062E2117-ABA0-4C05-90A2-E9F370223EED@gmail.com> (raw)
In-Reply-To: <CAApHDvq2tB4a7TAw8pXkukoqfvWr1+LccEwYoJoXe8DpsyBh1A@mail.gmail.com>
References: <5c1179bb-240b-4c1c-b4b3-2a24868e44bc@stiltsoft.com>
	<1570249.1697228785@sss.pgh.pa.us>
	<dd874d42-ad02-48a6-82db-5666f1ee0ec1@stiltsoft.com>
	<3153246.1697661350@sss.pgh.pa.us>
	<e9d503cb-efeb-43d3-952e-f517e4d24302@stiltsoft.com>
	<1157086.1698329377@sss.pgh.pa.us>
	<6c888a16-b206-4817-b5ca-9e09b904edde@stiltsoft.com>
	<DM6PR03MB433237820C534143711439B2FAA0A@DM6PR03MB4332.namprd03.prod.outlook.com>
	<2641105.1698788712@sss.pgh.pa.us>
	<PH0PR02MB744665C3C2C73A828B42D9988EA4A@PH0PR02MB7446.namprd02.prod.outlook.com>
	<fbfaf1a0a5a83d35971b7d77c5cbf64d72375dc4.camel@cybertec.at>
	<PH0PR02MB744686A70A78460A4C2DA70E8EABA@PH0PR02MB7446.namprd02.prod.outlook.com>
	<CAMkU=1xSq_v1SvUGKpLzHCpvJ6PF3QnmFOx53+2h4zSvj_AFfQ@mail.gmail.com>
	<PH0PR02MB74460E8C36F3E81ABD4FCAC98EABA@PH0PR02MB7446.namprd02.prod.outlook.com>
	<CAApHDvq2tB4a7TAw8pXkukoqfvWr1+LccEwYoJoXe8DpsyBh1A@mail.gmail.com>

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
> 
> 

view thread (10+ messages)  latest in thread

Message-ID: <062E2117-ABA0-4C05-90A2-E9F370223EED@gmail.com>
Permalink:  ../062E2117-ABA0-4C05-90A2-E9F370223EED@gmail.com/
Also on:    postgresql.org/message-id/062E2117-ABA0-4C05-90A2-E9F370223EED@gmail.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: frits.hoogland@gmail.com, dgrowleyml@gmail.com, danny_abraham@bmc.com
  Subject: Re: [EXTERNAL] Performance down with JDBC 42
  In-Reply-To: <062E2117-ABA0-4C05-90A2-E9F370223EED@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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