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 1tB7a5-0020RS-7q for pgsql-performance@arkaria.postgresql.org; Wed, 13 Nov 2024 07:14:24 +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 1tB7a2-00BvMY-L0 for pgsql-performance@arkaria.postgresql.org; Wed, 13 Nov 2024 07:14:23 +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 1tB7a2-00BvMP-BM for pgsql-performance@lists.postgresql.org; Wed, 13 Nov 2024 07:14:22 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tB7a0-001gAk-7q for pgsql-performance@lists.postgresql.org; Wed, 13 Nov 2024 07:14:22 +0000 Message-ID: Date: Wed, 13 Nov 2024 09:14:18 +0200 MIME-Version: 1.0 Subject: Re: tds_fdw : Severe performance degradation from postgresql 10.23 to 16.4 To: Tom Lane Cc: Tomas Vondra , pgsql-performance@lists.postgresql.org, "P.Groidis" References: <75b1aafc-bf5f-48fe-a495-fba11d8f0b05@vondra.me> <9640ce5b-3128-4c3c-b901-65747bc67b83@cloud.gatewaynet.com> <6797bc15-486b-48c3-9e89-cf85ae9a4043@vondra.me> <14f7d64f-d864-4429-9cf2-7ef92d6a90fb@vondra.me> <50da382b-1d47-4332-9279-8af0996f8088@cloud.gatewaynet.com> <1224347.1731440256@sss.pgh.pa.us> Content-Language: en-US From: Achilleas Mantzios - cloud In-Reply-To: <1224347.1731440256@sss.pgh.pa.us> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 11/12/24 21:37, Tom Lane wrote: > Achilleas Mantzios writes: >> Our sysadm created the system debian Debian GNU/Linux 12 (bookworm)with >> postgres as user 1000. Now at some point we realized that whenever we >> run a pgsql cluster with another user (I found that after spending two >> good days testing), the above query runs in about 1 second. With user >> postgres 1000 in 30 seconds. As you saw the perf output are completely >> different. > Don't recall details offhand, but in some situations where the calling > SQL user doesn't have permissions to read particular columns, the > planner will not consult statistics for those columns. That can lead > to a different, less optimal plan being used. Maybe something like > that is happening here? Thank you Tom, In all runs of this query the user is postgres (superuser). Besides, this is just a simple query run on a FOREIGN TABLE living in a MS SQL Server. > > regards, tom lane