Re: tds_fdw : Severe performance degradation from postgresql 10.23 to 16.4

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
>