exceptional result of postres_fdw external table joining local table

From: "Qu, Mischa, Majorel China" <mischa(dot)qu(at)majorel(dot)cn>
To: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: exceptional result of postres_fdw external table joining local table
Date: 2023-02-06 10:30:31
Message-ID: BJSPR01MB0532EC69D0B373A7C23F85369CDA9@BJSPR01MB0532.CHNPR01.prod.partner.outlook.cn
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 ?

CAUTION: This e-mail and any attachments may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is forbidden. Please consider the environment before printing this e-mail!.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2023-02-06 10:54:08 BUG #17775: Clarify default value for HEADER argument in documentation
Previous Message Masahiko Sawada 2023-02-06 08:25:42 Re: WAL segments removed from primary despite the fact that logical replication slot needs it.