Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qzvr2-0024zh-AR for pgsql-performance@arkaria.postgresql.org; Mon, 06 Nov 2023 09:25:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qzvr0-00DpNc-Hy for pgsql-performance@arkaria.postgresql.org; Mon, 06 Nov 2023 09:25:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qzvr0-00DpNS-3F for pgsql-performance@lists.postgresql.org; Mon, 06 Nov 2023 09:25:06 +0000 Received: from mail-ej1-x62c.google.com ([2a00:1450:4864:20::62c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qzvqt-004D4W-AH for pgsql-performance@postgresql.org; Mon, 06 Nov 2023 09:25:04 +0000 Received: by mail-ej1-x62c.google.com with SMTP id a640c23a62f3a-99357737980so629113466b.2 for ; Mon, 06 Nov 2023 01:24:59 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1699262698; x=1699867498; darn=postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=gpSnVfIdHylou8TjKj1YPcMrMcu1VNjBuymSmfeVugA=; b=giH/gx6iaAkJBvJwl5mKrAesbC0yHkWZKYp24ByrMjAiiX3dqazdQ5rBBcNx583rGz 7bLSr0CjXkbILiFVVGaIBGZv0/AQwumSg4v3fLbU+ljU1oHhhrU4h9BTLxL5nrGqBT0U NgAJzdM9OHsuDk7xLlIo27Uc2BTXPKkKLgkRhk4Fl6C7Kgp/xw5JNt3xOn2k5vM0AP1H UPoRdyAMNovrDFz1buhUmutW3FaeBBB4BT6lPDXgMsh9wZDPLvx7Q/x4AkFZFFp5J9Ey I4MbD8VpHMN6YXosSqqxyowr1BIwuBdHdL30Z5kr9UOJhOYqCCMedwqT0hgSuyRQSCoS sKvA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1699262698; x=1699867498; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=gpSnVfIdHylou8TjKj1YPcMrMcu1VNjBuymSmfeVugA=; b=r95b3Sasm9wKofcc6aXO0gOuIiG/zYkIp0+b5YFLlHOpHCNiYPFukB9hkM3LtcJK8r Q4FfYgk851lvrWFPcyuWJnvC+8CaCNpIlgb7CsPc4jJtapBA159qCY4P3IYuYx+QHFKD VYQzpEFjPYdtsOfeoQaImg80bCbSEqHf86xxMIZKFMMwSU3PIb1LLh2mjoLQ7+l7zAcQ 5dOtV+iGsjpiUUYJsvfBgFPqXVXgWAa60w3arpo82QhBya0wgo2gncRJOdsoitSfdjoO 7DGvw9VMIsDGrEejmOVGg7kVjbvsd+yaakkujy2TgTsOzGo5H5XXK4sJOPaDZ4w4Wffp pcPg== X-Gm-Message-State: AOJu0YyAEycIftCNxzxLYOQsq52VJSm0tqSflAuZ2pMD0A/0sSsaPHWl HfoDR1YlRXdbOLhK3EFw91Q= X-Google-Smtp-Source: AGHT+IF2iA6HjlOsDvjckqkW+DDPmi488r9cVcN/nrRtAv3kElufB3tb0gKdQ4PYb8dHX0Vh4OUwEQ== X-Received: by 2002:a17:907:3e8e:b0:9bd:dfaa:3f2 with SMTP id hs14-20020a1709073e8e00b009bddfaa03f2mr16966298ejc.6.1699262697369; Mon, 06 Nov 2023 01:24:57 -0800 (PST) Received: from smtpclient.apple (2001-1c04-3c07-1a00-a198-c1fc-40f9-213a.cable.dynamic.v6.ziggo.nl. [2001:1c04:3c07:1a00:a198:c1fc:40f9:213a]) by smtp.gmail.com with ESMTPSA id g17-20020a1709067c5100b0099bc8bd9066sm3972370ejp.150.2023.11.06.01.24.56 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 06 Nov 2023 01:24:57 -0800 (PST) From: Frits Hoogland Message-Id: <062E2117-ABA0-4C05-90A2-E9F370223EED@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_331158EA-0BE8-426B-B5DC-81CB825AE54C" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.200.91.1.1\)) Subject: Re: [EXTERNAL] Performance down with JDBC 42 Date: Mon, 6 Nov 2023 10:24:43 +0100 In-Reply-To: Cc: psql-performance To: David Rowley , "Abraham, Danny" References: <5c1179bb-240b-4c1c-b4b3-2a24868e44bc@stiltsoft.com> <1570249.1697228785@sss.pgh.pa.us> <3153246.1697661350@sss.pgh.pa.us> <1157086.1698329377@sss.pgh.pa.us> <6c888a16-b206-4817-b5ca-9e09b904edde@stiltsoft.com> <2641105.1698788712@sss.pgh.pa.us> X-Mailer: Apple Mail (2.3774.200.91.1.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_331158EA-0BE8-426B-B5DC-81CB825AE54C Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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 wrote: >=20 > On Mon, 6 Nov 2023 at 08:37, Abraham, Danny = wrote: >>=20 >> Both plans refer to the same DB. >=20 > JDBC is making use of PREPARE statements, whereas psql, unless you're > using PREPARE is not. >=20 >> #1 =E2=80=93 Fast =E2=80=93 using psql or old JDBC driver >=20 > The absence of any $1 type parameters here shows that's a custom plan > that's planned specifically using the parameter values given. >=20 >> Slow =E2=80=93 when using JDBC 42 >=20 > 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. >=20 > You might find things run better if you adjust postgresql.conf and set > plan_cache_mode =3D force_custom_plan then select pg_reload_conf(); >=20 > Please also check the documentation so that you understand the full > implications for that. >=20 > David >=20 >=20 --Apple-Mail=_331158EA-0BE8-426B-B5DC-81CB825AE54C Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 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-prepar= ed-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 =E2=80=93 Fast =E2=80=93 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 =E2=80=93 = 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 =3D force_custom_plan then = select pg_reload_conf();

Please also check the documentation so = that you understand the full
implications for = that.

David



= --Apple-Mail=_331158EA-0BE8-426B-B5DC-81CB825AE54C--