Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oVBVh-0007OZ-HF for pgsql-performance@arkaria.postgresql.org; Mon, 05 Sep 2022 12:47:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oVBVg-0007Ia-Ef for pgsql-performance@arkaria.postgresql.org; Mon, 05 Sep 2022 12:47:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oVBVg-000771-6S for pgsql-performance@lists.postgresql.org; Mon, 05 Sep 2022 12:47:28 +0000 Received: from mail-io1-xd2a.google.com ([2607:f8b0:4864:20::d2a]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oVBVd-0001jp-SA for pgsql-performance@lists.postgresql.org; Mon, 05 Sep 2022 12:47:27 +0000 Received: by mail-io1-xd2a.google.com with SMTP id b142so6736660iof.10 for ; Mon, 05 Sep 2022 05:47:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20210112.gappssmtp.com; s=20210112; h=user-agent:in-reply-to:content-disposition:mime-version:references :message-id:subject:cc:to:from:date:from:to:cc:subject:date; bh=hrsN4CMeSUWKCIrsix6AMnItiVXVBIplBiBMbYN71aU=; b=uFx8zEF2t8iAKMMyNVzoPopgiIRRt+HYEc8/IJZHmVwkh1X8ifjsiB5f07ygYv36gv ccnFhBxMFANb7MQrHFrGQXfi7AqIqiDcK5xCVvdxpIxB8lQBrU4UmtTpapegWqTWcKV6 p8yqjxGpb8kObmiz79oaTDe83tQpm/NZoBoj1/YXeaUv9Ql1XiWStL5/UW1FIcqmVO8B zqxwtpCzmQkO6ksK8BfIpeKv/bmgpKGyZyra+yz26WkDdYGc31TWrhh/e0BzVYkRe+vP Nlokalk4ZxhOqYZQY/ienxIFfTs24EO705qTDAhi2fsNUDGdxUshap93K/xh6HD/mMs6 qZXg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=user-agent:in-reply-to:content-disposition:mime-version:references :message-id:subject:cc:to:from:date:x-gm-message-state:from:to:cc :subject:date; bh=hrsN4CMeSUWKCIrsix6AMnItiVXVBIplBiBMbYN71aU=; b=iJ/s4N/+tM/8wQ/sAXuqrYn0YNgKBbDp0+TJ5D+Bdu32PU0iwXQoHE5V2zwoe59/rd H5cctHpsBiLpXHuB0XgGSQIa2QNSS8UEIiSYlMDQ2rN8dkVbK8SopyKxmFQ6NrA7G1Q/ C00sa1qXoULcWSXG2Hl8xhru82SySVOlRHsSwpQLNqXBcly41ToSbDPfdzkkuya9Oh34 RdaBJXTiQXkmvNMgxmnPpymHP+cbUPdABQvbB532NpJlvCgJ5tF6FhCAxA0By+9VWO45 hNz/wfAZs/R1lAcHsOx4ES/0HDpnbzLA1qxmWV61ESF3lvQOpsPIgANNWwV46bm/5km7 fF/g== X-Gm-Message-State: ACgBeo2Jvgg4lhZ4nHCpdEFYMdlpiNWDSoSuWq/bcrM/R1elEK5wzYlz Ofb2lJB/XC93P6kM0yGQvjRnsg== X-Google-Smtp-Source: AA6agR5D0TV/V/wAaezmKA1LZXfisap1huKqEMTvYKgfcAYHJGLl7g0JQmWPBGygKMz9uno1+x7Yug== X-Received: by 2002:a02:c986:0:b0:34a:5508:8ed2 with SMTP id b6-20020a02c986000000b0034a55088ed2mr17185802jap.297.1662382043990; Mon, 05 Sep 2022 05:47:23 -0700 (PDT) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id d6-20020a028586000000b00349d85eaa89sm4095033jai.20.2022.09.05.05.47.23 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 05 Sep 2022 05:47:23 -0700 (PDT) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id C3058801058; Mon, 5 Sep 2022 07:47:22 -0500 (CDT) Date: Mon, 5 Sep 2022 07:47:22 -0500 From: Justin Pryzby To: "James Pang (chaolpan)" Cc: pgsql-performance@lists.postgresql.org Subject: Re: Postgresql JDBC process consumes more memory than psql client Message-ID: <20220905124722.GI31833@telsasoft.com> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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.prod.outlook.com -- Justin