Issue report

The following information is very important in order to help us to help you. Omission of the following details cause delays or could receive no attention at all.

Operating system

On recent GNU/Linux distributions, you can provide the content of the file /etc/os-release

PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"
NAME="Debian GNU/Linux"
VERSION_ID="12"
VERSION="12 (bookworm)"
VERSION_CODENAME=bookworm
ID=debian

Version of tds_fdw

From a psql session, paste the outputs of running \dx

If you built the package from Git sources, also paste the output of running git log --source -n 1 on your git clone from a console

We built from tarball:
tds=# \dx tds_fdw 
                                          List of installed extensions
  Name   | Version | Schema |                                    Description                                    
---------+---------+--------+-----------------------------------------------------------------------------------
 tds_fdw | 2.0.4   | public | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)
(1 row)

Version of PostgreSQL

From a psql session, paste the output of running SELECT version();

The NEW SLOW system:
 PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
The OLD FAST system:
 PostgreSQL 10.23 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

Version of FreeTDS

How to get it will depend on your Operating System and how you installes FreeTDS

From a console:

On RPM based systems: rpm -qa|grep freetds
On Deb based systems: dpkg -l|grep freetds
_If you built your own binaries from source code, then go to the sources, and run: grep 'AC_INIT' configure.ac_

pgsql10@smadb:~$ dpkg -l|grep freetds
ii  freetds-bin                           1.3.17+ds-2                    amd64        FreeTDS command-line utilities
ii  freetds-common                        1.3.17+ds-2                    all          configuration files for FreeTDS SQL client libraries
ii  freetds-dev                           1.3.17+ds-2                    amd64        MS SQL and Sybase client library (static libs and headers)
ii  freetds-doc

Logs

Please capture the logs when the error you are reporting is happening, as well as commands with their outputs if you are reporting a problem build or installing

_For problems using tdsfdw on PostgreSQL how to do it will depend on your system, but if your PostgreSQL is installed on GNU/Linux, you will want to use tail -f with the log of the PostgreSQL cluster

For MSSQL you will need to use the SQL Server Audit Log

I would note that with debug3 
set client_min_messages TO debug3;
I got identical output between the old fast and new slow systems.

Sentences, data structures, data

This will depend on the exact problem you are having and data privacy restrictions

However the more data you provide, the more likely we will be able to help

As a bare minimum, you should provide

The SQL sentence that is failing
The data structure on the PostgreSQL side and on the MSSQL side

STATEMENT : 
select count(*) as foo FROM mssql_bdynacom."ACDOC" ;

PGSQL structure
tds=# \d mssql_bdynacom."ACDOC"
                                               Foreign table "mssql_bdynacom.ACDOC"
         Column         |              Type              | Collation | Nullable | Default |              FDW options               
------------------------+--------------------------------+-----------+----------+---------+----------------------------------------
 ID                     | integer                        |           | not null |         | (column_name 'ID')
 DOC_TYPE               | smallint                       |           |          |         | (column_name 'DOC_TYPE')
.....
Server: mssql_bdynacom_srv
FDW options: (schema_name 'dbo', table_name 'ACDOC')

MS SQL structure
CREATE TABLE BDYNACOM.dbo.ACDOC (
    ID int IDENTITY(1,1) NOT NULL,
    DOC_TYPE smallint,
.....
);
CREATE INDEX .... ;

Now the problem , in the new system it runs more than 10 times slower than the old. New System (pgsql16) :

postgres@[local]/dynacom=# select count(*) as foo FROM mssql_bdynacom."ACDOC" ;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
WARNING:  Table definition mismatch: Foreign source has column named , but target table does not. Column will be ignored.
   foo   
---------
 1681679
(1 row)

Time: 28980.066 ms (00:28.980)

old system (pgsql10) :

tds=# select count(*) as foo FROM mssql_bdynacom."ACDOC" ;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
WARNING:  Table definition mismatch: Foreign source has column named , but target table does not. Column will be ignored.
   foo   
---------
 1681679
(1 row)

Time: 1440.160 ms (00:01.440)

For the duration that the slow query (pgsql 16) runs, the backend process hits 100% of its CPU.

tds-fdw / tds_fdw

Severe performance degradation from postgresql 10.23 to 16.4 #371