Message-ID: <fa820fa8-601f-4d9a-a716-66a5f794d758@vondra.me>
Date: Tue, 12 Nov 2024 22:46:14 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: tds_fdw : Severe performance degradation from postgresql 10.23 to
 16.4
To: Tom Lane <tgl@sss.pgh.pa.us>,
 Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
Cc: pgsql-performance@lists.postgresql.org, "P.Groidis" <itdev@gatewaynet.com>
References: <f43721f9-e628-4a22-b84d-e0ccc75b50cc@cloud.gatewaynet.com>
 <75b1aafc-bf5f-48fe-a495-fba11d8f0b05@vondra.me>
 <9640ce5b-3128-4c3c-b901-65747bc67b83@cloud.gatewaynet.com>
 <6797bc15-486b-48c3-9e89-cf85ae9a4043@vondra.me>
 <cd2aa7ec-cfe5-447d-ac13-31f2cb65a1c4@cloud.gatewaynet.com>
 <b31dbff5-4aae-4860-b17d-29ec0e9e14be@cloud.gatewaynet.com>
 <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: Tomas Vondra <tomas@vondra.me>
In-Reply-To: <1224347.1731440256@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/fa820fa8-601f-4d9a-a716-66a5f794d758%40vondra.me>
Precedence: bulk


On 11/12/24 20:37, Tom Lane wrote:
> Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> 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?
> 

I don't know, the query is pretty trivial, and the estimates seemed
exactly the same in both cases. And it shouldn't affect how the query
gets planned on the MSSQL side.

But this seems really strange:

  Planning Time: 14029.724 ms
  ...
  Execution Time: 15102.803 ms

It's not about the execution, it's about the planning. I have no idea
why should the planning take this long, except maybe for waiting for a
lock, or something like that. But that's not really consistent with the
profile ... it's weird.

I'm not familiar with tds_fdw, but I see there are a bunch of table
options [1] that might affect this, namely:

* use_remote_estimate
* local_tuple_estimate
* row_estimate_method (defaults to 'execute')

Are you sure these are set to the same value on both machines?

Wild random guesses:

1) Could you try running the query with jit=off?

2) Did you run ANALYZE on the foreign table? Could matter when not using
remote estimates (use_remote_estimate=false).

3) Could it be some sort of memory pressure/swapping? But that would
look different in the profile, AFAIK.


regards


[1] https://github.com/tds-fdw/tds_fdw/blob/master/ForeignTableCreation.md

-- 
Tomas Vondra