public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tomas Vondra <[email protected]>
To: Tom Lane <[email protected]>
To: Achilleas Mantzios <[email protected]>
Cc: [email protected]
Cc: P.Groidis <[email protected]>
Subject: Re: tds_fdw : Severe performance degradation from postgresql 10.23 to 16.4
Date: Tue, 12 Nov 2024 22:46:14 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>



On 11/12/24 20:37, Tom Lane wrote:
> Achilleas Mantzios <[email protected]> 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

view thread (13+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: tds_fdw : Severe performance degradation from postgresql 10.23 to 16.4
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox