From: Justin Pryzby <pryzby@telsasoft.com>
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql JDBC process consumes more memory than psql client
Date: Mon, 5 Sep 2022 07:47:22 -0500
Message-ID: <20220905124722.GI31833@telsasoft.com> (raw)
In-Reply-To: <PH0PR11MB5191A047B707B2FE6527C549D67F9@PH0PR11MB5191.namprd11.prod.outlook.com>
References: <PH0PR11MB5191A047B707B2FE6527C549D67F9@PH0PR11MB5191.namprd11.prod.outlook.com>
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 filed from top command), but when run the same SQL from psql , it only consumes about 10MB memory. UPDATE statements is similar , need 100MB memory, 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
weren'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 many 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
partition 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/PH0PR11MB519134D4171A126776E3E063D6B89@PH0PR11MB5191.namprd11....
--
Justin
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: pryzby@telsasoft.com, chaolpan@cisco.com, pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql JDBC process consumes more memory than psql client
In-Reply-To: <20220905124722.GI31833@telsasoft.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