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!.
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. |