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 1tk3hS-00H1dC-2s for pgsql-general@arkaria.postgresql.org; Mon, 17 Feb 2025 16:10:26 +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 1tk3hQ-00HFir-Gy for pgsql-general@arkaria.postgresql.org; Mon, 17 Feb 2025 16:10:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tk3hP-00HFij-Ki for pgsql-general@lists.postgresql.org; Mon, 17 Feb 2025 16:10:24 +0000 Received: from fhigh-b1-smtp.messagingengine.com ([202.12.124.152]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tk3hM-001NNu-2c for pgsql-general@lists.postgresql.org; Mon, 17 Feb 2025 16:10:23 +0000 Received: from phl-compute-04.internal (phl-compute-04.phl.internal [10.202.2.44]) by mailfhigh.stl.internal (Postfix) with ESMTP id 06F4925401A5; Mon, 17 Feb 2025 11:10:19 -0500 (EST) Received: from phl-mailfrontend-01 ([10.202.2.162]) by phl-compute-04.internal (MEProxy); Mon, 17 Feb 2025 11:10:19 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm2; t=1739808618; x=1739895018; bh=HVBSj64KzzNfglLGlNSrniRIgyeeaaiRtpgUSVeFj7s=; b= Zl6fKRq0ODnEEtuPOFSgTWuIaOiWKsYw7VYPrVc//f8PPA8Qgwc/PpM8lycLMSft duE/CQVho/t7a0d2CYFXKDEnqWWYURC2xQHrF6U8n2Yia6E56FlWKkt5RuRPc9hQ J5Gkb61YsggVdtu7FoK5Z8TTpH7N7P+Kbvq+WpMRzPUlxzDH8ngqpgoZ0RhrpWqr 7Azq9qzZqefvxgKdJvudnxptlL3qfPOPWrvSqrszBgmts5isYEK5/3Y+miY40Kvt MVHvySQ+MF5VI1bzmsH8KeLHWq08j2qAe5a44KuOqDP3e7d87yfJTfA6SXmDWVxH SceHzf4BmCUA23Jc5LTNOw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm3; t=1739808618; x=1739895018; bh=H VBSj64KzzNfglLGlNSrniRIgyeeaaiRtpgUSVeFj7s=; b=xlwuAVt7qAo/L3tLS L+QnEOl0AampTgGwdjhK3HMN+sT8IzN0iyLFAKYbcE1h9V7cLclOB7oxKe6m9Bga VhP/SBq/epx+kWbboO0NQooueX2Tk10zJQrJtlQ/6ALAFFLa2rik2zkOmPPA2uK0 8f0IWN68NZo393HKjnAYoGhyrtd2QgQLxiAn/q98JvZSy5zbkNgj4m0jb3q7Yqe2 cp+PVg87prkdaAmqC1cSS2PXBh3d0d4GULAm5mSKOA0WUtpqvqFb9FttEDT7pUp6 HjR2i1zjNYbWWiqWXEk1ordSOm8kqoxwwNFwdRaJulJcNQMWsU6W1qzzMqm6hZuC mWWUQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefvddrtddtgdehkeekgecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpggftfghnshhusghstghrihgsvgdp uffrtefokffrpgfnqfghnecuuegrihhlohhuthemuceftddtnecunecujfgurhepkfffgg gfuffvfhfhjggtgfesthekredttddvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgv rhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrg htthgvrhhnpeffleegieefgfevudehtdfhkeeutdffjeevgeffgeejvedthefgudeiteef heejheenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpe grughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhmpdhnsggprhgtphhtthho pedvpdhmohguvgepshhmthhpohhuthdprhgtphhtthhopehmrgdqthgvnhhgfhgvihesnh gvtgdrtghnpdhrtghpthhtohepphhgshhqlhdqghgvnhgvrhgrlheslhhishhtshdrphho shhtghhrvghsqhhlrdhorhhg X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 17 Feb 2025 11:10:17 -0500 (EST) Message-ID: Date: Mon, 17 Feb 2025 08:10:17 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: The performance issues caused by upgrading PostgreSQL to version 16.3. To: =?UTF-8?B?6aasIOmosOmjmw==?= , "pgsql-general@lists.postgresql.org" References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2/16/25 23:55, 馬 騰飛 wrote: > Dear PostgreSQL Community, > > I hope this message finds you well. I am reaching out to seek your technical assistance regarding a performance issue we encountered after upgrading our PostgreSQL version from 12.19 to 16.3. > We have noticed a significant performance problem with a specific SQL query on one of our application screens. > Interestingly, when we isolate the problematic SQL statement and replace its parameters with actual values, it executes in just a few seconds in pgAdmin. > However, when we run the same SQL query through our application using Npgsql, it takes over ten minutes to complete. > We are using NpgsqlCommand.ExecuteReader to execute the SQL query, and the parameters are set using NpgsqlCommand.Parameters.Add. > The main table involved in this query contains approximately 800,000 records. > We believe that the SQL statement itself does not have performance issues, but there may be problems related to how the SQL is executed in the application or how the parameters are set. > However, we are unable to pinpoint the exact cause of the performance degradation. > Could you please provide us with some possible reasons or suggestions for improvement? Your insights would be greatly appreciated. This is going to need more information: 1) Was the upgrade done on the same machine or across machines? If across machines how are they different? 2) The table definition. 3) The query text. 4) The Npgsql code. > Thank you for your assistance, and I look forward to your response. > > Best regards, > Ma > > -- Adrian Klaver adrian.klaver@aklaver.com