Message-ID: <f1ddfd33-64e2-4a77-b0a6-c111a08590d1@cloud.gatewaynet.com>
Date: Wed, 13 Nov 2024 10:45:58 +0200
MIME-Version: 1.0
Subject: Re: tds_fdw : Severe performance degradation from postgresql 10.23 to
 16.4
To: Tomas Vondra <tomas@vondra.me>, Tom Lane <tgl@sss.pgh.pa.us>
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>
 <fa820fa8-601f-4d9a-a716-66a5f794d758@vondra.me>
Content-Language: en-US
From: Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
In-Reply-To: <fa820fa8-601f-4d9a-a716-66a5f794d758@vondra.me>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/f1ddfd33-64e2-4a77-b0a6-c111a08590d1%40cloud.gatewaynet.com>
Precedence: bulk

Dear All

false alert, I run strace and it was obvious the slow one was producing 
huge debug output, while the fast one did not. It was not even a tds_fdw 
issue. It was freetds. Turned out we have forgotten enabled debugging 
inside the freetds configuration. You will ask me we did I get this 
effect of debugging only when run with user "postgres" ? Because 
/tmp/freetds.log belonged to postgres!

I changed owner to nobody:nogroup and dont even need to restart postgres. !!

I am sorry for all the noise.

Thank you so much for your prompts !!

On 11/12/24 23:46, Tomas Vondra wrote:
>
> 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
>