IronPort-PHdr: A9a23:wNaHnxJoHXQjKl/otdmcuWEyDhhOgF28FgIW659yjbVIf+zj+pn5J
 0XQ6L1ri0OBRoTU7f9Iyo+0+6DtUGAN+9CN5XYFdpEfWxoMk85DmQsmDYaMAlH6K/i/aSs8E
 YxCWVZp8mv9P1JSHZP1ZkbZpTu56jtBcig=
IronPort-Data: A9a23:RBzE/KgujL0MoT6795FXuwGbX161RBAKZh0ujC45NGQN5FlHY01je
 htvXDqDP6mCZDPxLdh0PIjl8U8B7JOAzoBjQAJtpH1mFiNjpJueD7x1DKtf0wB+jyHnZBg6h
 ynLQoCYdKjYdleF+lH1dOKJQUBUjclkfJKkYAL/En03FFQMpBsJ00o5wbZo2tMw2LBVPivU0
 T/Mi5yHULOa82Yc3lI8s8pvfzs24ZweEBtB1rAPTagjUG32zhH5P7pDTU2FFEYUd6EPdgKMq
 0kv+5nilo/R109F5tpICd8XeGVSKlLZFVDmZna7x8FOjzAazhHe3JrXO9JBZWl3uWiynuxM5
 /NkiIK2YAEyP7/TzbF1vxlwS0mSPIVP/LvBZHO4q8HWkQvNcmDnxLNlC0Re0Y8wo7ksRzoQs
 6VDbmlWP3hvhMruqF6/YuJsj8MnNMDtFIgeoXpnizreCJ7KRLiSHviQtIIJh2xYasZmMcTjS
 M4QSmJVVwXmeTFVZktGK4gkpbL97pX4W2QI9A3KzUYt2EDLwxdo1P31PcDbfcaPTMNek26cp
 3na5CLhBQoGO8aS0iOBtHW2iYfnhTnqSogDPKG18uVjm1qd2ioYDxh+aLegifC9jkj7UNVFJ
 glKvCEvtqM1skesS7ERQiFUvlbZsjA8aYdrEtEHxyek97TSygCCB3obG2sphMMdiOc6Qjkj1
 1msltzvBCByvLD9dZ573urOxd9VEXVIRVLudRPoXiNeuIC6/99bYgbnC4c9TvHk17UZDBmqm
 1i3QD4Ca6L/ZCLh/4y/+V3B695HjseUFldujuk7s57M0++UTIehY4rt4l/B4LMeao2YVVKG+
 nMDnqByDdzi77nTzURho81UQ9lFAspp1hWG2jaD+LF6rVyQF4aLJ9w43d2HDB4B3jw4UTHoe
 lTPngha+YVeOnCnBYcuPdzrUJ10l/KxT4m0PhwxUjaoSsUvHONg1Hw+DXN8I0ix+KTRufhlY
 MzCIZrE4YgyUPk4l1JauNvxIZdylnxhmgs/tLjwzg+s1vKFdWWJRLIeWGZinchnhJ5oVD79q
 o4FX+PTkk03eLSnPkH/r9VJRXhUdidTOHwDg5ENHgJ1ClA4SDhJ5j646e5JRrGJaIwOy7iQp
 i3jBRIHoLc97FWeQTi3hrlYQOuHdf5CQbgTZETA4X7AN6AfXLuS
IronPort-HdrOrdr: A9a23:JrNAm6PQAOSr68BcT3X155DYdb4zR+YMi2TDiHoedfUFSKOlfp
 6V8MjzjSWE9Ar4WBkb6LS90dq7MAzhHPlOkMUs1NaZLUTbUQ6TTb2KgrGSuwEIdxeOlNK1kJ
 0QDpSWa+eAQmSS7/yKmzVQeuxIqLLsncDY5ts2jU0dNz2CAJsQiDuRfzzra3GeMzM2Y6bReq
 Dsg/Zvln6FQzA6f867Dn4KU6zovNvQjq/rZhYAGloO9BSOpSnA0s+1LzGomjMlFx9fy7Yr9m
 bI1ybj4L+4jv29whjAk0fO8pVtnsf7wNcrPr3MtiFVEESttu+bXvUiZ1SwhkFxnAhp0idvrD
 D4mWZiAy200QKXQoj6m2qq5+Cq6kdR15ar8y7ovZKkm72heNr/YPAx3r6wtXDimhIdVZhHod
 J29nPcuJxNARzamiPho9DOShFxj0Kx5WEviOgJkhVkIMMjgZJq3PoiFXluYd49NTO/7JpiHP
 hlDcna6voTeVSGb2rBtm0qxNC3RHw8EhqPX0BH46WuonJrtWE8y1FdyN0Un38G+p54Q55Y5/
 7cOqAtkL1VVMcZYa90Ge9ES8qqDW7GRw7KLQupUB/aPbBCP2iIp4/84b0z6u3vcJsUzIEqkJ
 CES19cvX5aQTOYNSRP5uw+zvngehTOYd228LAs23FQgMyIeIbW
From: "James Pang (chaolpan)" <chaolpan@cisco.com>
To: Justin Pryzby <pryzby@telsasoft.com>
CC: "pgsql-performance@lists.postgresql.org"
	<pgsql-performance@lists.postgresql.org>
Subject: RE: Postgresql JDBC process consumes more memory than psql client
Thread-Topic: Postgresql JDBC process consumes more memory than psql client
Thread-Index: AdjBJDa7k+GqN3+NR5iuG9i/iPSAXAAAW10AAAAZ0cA=
Date: Mon, 5 Sep 2022 12:52:14 +0000
Message-ID: 
 <PH0PR11MB5191C2C75F91CE1237267479D67F9@PH0PR11MB5191.namprd11.prod.outlook.com>
References: 
 <PH0PR11MB5191A047B707B2FE6527C549D67F9@PH0PR11MB5191.namprd11.prod.outlook.com>
 <20220905124722.GI31833@telsasoft.com>
In-Reply-To: <20220905124722.GI31833@telsasoft.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?PEDNVVD0mqffBkXuerUTy0YRxdb9W33yNtAURgrU2bVV+RI2yXeCUDk/QPH9?=
 =?us-ascii?Q?nFDa3VfecNV9FU9JzuyEocD+0dzlGgmUNbIzxSJs/XArKikK4Gu2sSg4Wv9i?=
 =?us-ascii?Q?gZ6gCYOP0GsUyVEHf34rLFDV1E4LNdoYtlG/0Yydpm9EcOzS3bvDwX+gqpp9?=
 =?us-ascii?Q?vliBMdS9Ae/rXRSWJWpR1pzAylXy93vgqnzVfdQW32sSfUUWkhVvZaeJVOII?=
 =?us-ascii?Q?imsHwlamciItDXMM/qlHh+7MC+7QPXvBejb6M3wLBzV8V3L0likZ55vPTXqR?=
 =?us-ascii?Q?f7WRiSsXBln8OkeRXTaX3CCHip/i6M6wUXDxBoEpVnQnLwYo9nQF+voq4yQi?=
 =?us-ascii?Q?RoIk5Q7saNZrVsq1L4C+2OWRe3aN1tOlOeTo6X2rJqw7jhNXkQesJXYxZGmI?=
 =?us-ascii?Q?LjXJ6ZphotQ+qyLXmvs4k1xD5KZ/sUxFFzQdKO4hfvhgd9VZpUiplmPHivYq?=
 =?us-ascii?Q?Qu06d4pDCRmAVpgTxW1A6HRzStCjCYri8cQI90M4X1JcbJm4Cj8u42IkHx8/?=
 =?us-ascii?Q?EWDAbvQN3+WPOJfPJ30oaHkVg/hvWHCugpjTHJow2MdyIcyqsEtlGBEUMEdD?=
 =?us-ascii?Q?No7Yr0SuPqxnrQgdgWRoAukwLKyt4K4o0D+qQccPnGuZPeO0wmdXQE4EJAJj?=
 =?us-ascii?Q?tyU1GScgER3XOYy1cFR3XLe3OlJbic7vWB0jvqWJ8vYVQlDQ30oxEKmYdGIQ?=
 =?us-ascii?Q?q0WFtPnJvCm0yUcb1ObPcdB1NpTbV0wRr7zh+OFjDhlyhZcKEt/hRj5IkIF0?=
 =?us-ascii?Q?VIpaXC28WTZMiDfUM/d94QLsfdYmzWkTwBZObge5BrgmgwBX6hrwhGUyy9cu?=
 =?us-ascii?Q?sdvywS1Akc9fjYnd4VvEZG01w6dGPMNXWNpe9j/smQYnypot/M2uumPoiYQS?=
 =?us-ascii?Q?a+PsIKLBuDqlWdQylOeDuDrR4oeAhpw/09g0L7XBOE4jlx7/dLMpQ2TwFrkV?=
 =?us-ascii?Q?e/Sbit2tNiAJpyrSD8AukgFUpLX2NrOiYu8pxr8fqJBRX0J3/7/AnPQFh1lh?=
 =?us-ascii?Q?OmPUILOd3rQOSjzvgIwSf5qOMlQ2GT6acrjRr6qTzWvm2cP4co6pu8rQfeqg?=
 =?us-ascii?Q?qWmuKS5ztYCZ0zpWwHw9D2kn0pQpnKHzEfeiEcoiBNyHRPBe1Ibyzi7+Wc/S?=
 =?us-ascii?Q?Aguaq7eqFkXP4902HKMejFxjR0fC1OFCbvsZkK1SDJ+oIoSMcXl/KYqv8L6F?=
 =?us-ascii?Q?N9zre5RQO9jPQ1aIfaRuKnkpiWCYw+tId/3wRSsJ/l8vdJq/BFa/bS2yccga?=
 =?us-ascii?Q?gUkbcMmbU+tKEN+KwAMIECCmWmzdPkmO7LlMR/Oh1MS0Xa0NcdSyiHdgBXTS?=
 =?us-ascii?Q?CBFpnMmrdC/c7i5Ca/IE9LtiJ0v15/789sSB3J1A14MQmmRDf3vBMER6GR9L?=
 =?us-ascii?Q?j4nWml1FUn9Kh151m51nPq9PFWtbyqsxLeCGecGMPU+bLuRTNfKYccGsSvuZ?=
 =?us-ascii?Q?YurnZCYQ2Uh1gDyqxflwGKQBnMIVB622+tYBsY5aCBU501jdJUvd80u2bbJ3?=
 =?us-ascii?Q?EBanz9GO0HGpqZC5YR4xl8QEueFCThwCMOqkpQYP2cJrhoj6WN6WF+Tq4sTF?=
 =?us-ascii?Q?escPlVPfKig6DfSk8C4lcpU5/VebhpMvOg3CiL2Y?=
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: PH0PR11MB5191.namprd11.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 96749453-910a-452b-9631-08da8f3d7511
X-MS-Exchange-CrossTenant-originalarrivaltime: 05 Sep 2022 12:52:14.5741
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 5ae1af62-9505-4097-a69a-c1553ef7840e
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 qd9PHtLbaFVptR0v5121rHup+Z7c/vSVpBg+ZSc8QJ0DRJJaPB14q/Y8jObsJAZ7rcFvVijL4j6cVw9VAbZyrA==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN6PR11MB1761
X-OriginatorOrg: cisco.com
X-Outbound-SMTP-Client: 64.101.210.232, xfe-rtp-002.cisco.com
X-Outbound-Node: alln-core-6.cisco.com
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Archived-At: 
 <https://www.postgresql.org/message-id/PH0PR11MB5191C2C75F91CE1237267479D67F9%40PH0PR11MB5191.namprd11.prod.outlook.com>
Precedence: bulk

PG V13, yes JDBC use prepared statements ,  from psql use pruned ,but even =
all partitions it NOT consumes too much memory.  Any idea how to print SQL =
plan from JDBC driver ?=20

-----Original Message-----
From: Justin Pryzby <pryzby@telsasoft.com>=20
Sent: Monday, September 5, 2022 8:47 PM
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql JDBC process consumes more memory than psql client

On Mon, Sep 05, 2022 at 12:40:46PM +0000, James Pang (chaolpan) wrote:
>   We run same update or delete SQL statement " DELETE FROM ... WHERE ... =
"  the table is a hash partition table (256 hash partitions). When run the =
sql from Postgresql JDBC driver, it soon increased to 150MB memory (RES fil=
ed from top command),       but when run the same SQL from psql , it only c=
onsumes about 10MB memory.  UPDATE statements is similar , need 100MB memor=
y, even it delete or update 0 rows.  Any specific control about Postgresql =
JDBC driver ?

It sounds like JDBC is using prepared statements, and partitions maybe were=
n't pruned by the server.  What is the query plan from psql vs from jdbc ?

https://wiki.postgresql.org/wiki/Slow_Query_Questions

What version is the postgres server ?
That affects pruning as well as memory use.

https://www.postgresql.org/docs/14/release-14.html
Improve the performance of updates and deletes on partitioned tables with m=
any partitions (Amit Langote, Tom Lane)

This change greatly reduces the planner's overhead for such cases, and also=
 allows updates/deletes on partitioned tables to use execution-time partiti=
on pruning.

Actually, this is about the same response as when you asked in June, except=
 that was about UPDATE.
https://www.postgresql.org/message-id/PH0PR11MB519134D4171A126776E3E063D6B8=
9@PH0PR11MB5191.namprd11.prod.outlook.com

--
Justin