exceptional result of postres_fdw external table joining local table - Mailing list pgsql-bugs

Hi,

   I found a problem when using postres_fdw external table.

   The PGDB version is 11.9。 I created a postgres_fdw external table to use a table from another DB, and I added use_remote_estimate true option to optimize remote sql。

  gap_new=> \des+

                                                                                List of foreign servers

      Name      | Owner | Foreign-data wrapper | Access privileges | Type | Version |                                    FDW options                                     | Descri

ption

----------------+-------+----------------------+-------------------+------+---------+------------------------------------------------------------------------------------+---

server28wx_fdw | gap   | postgres_fdw         | qu=U/qu         |      |         | (host '192.168.1.28', port '5432', dbname 'db1', use_remote_estimate 'true') |

(1 row)

But when I run some full table scans and found a problems. the results of the following sql were different.

Method 1: directly join external table and local table

select count(distinct user_id),count(distinct member_code)

from a_fdw t1

join b as t6 on t1.user_id=t6.tags ->> '10'::varchar(50)

where coalesce(user_id,'') <> '';

-- result: 50739 50845

Method 2: create a local temp table from external table and then join temp table and local table.

drop table if exists temp_a;

select *

into temp temp_a

from a_fdw;

select count(distinct user_id),count(distinct member_code)

from a_fdw t1

join b as t6 on t1.user_id=t6.tags ->> '10'::varchar(50)

where coalesce(user_id,'') <> '';

--result 1641737 1645368

The two methods produces different results. And apparently, the result of Method 1 lost some data.

Is there some problem with  use_remote_estimate option configuration ?