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@xxxxxxxxxxxxxxxxxxxx> 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
