From: Tom Lane <tgl@sss.pgh.pa.us>
To: Qu, Mischa, Majorel China <mischa.qu@majorel.cn>
Cc: pgsql-bugs@postgresql.org <pgsql-bugs@postgresql.org>
Subject: Re: exceptional result of postres_fdw external table joining local table
Date: Mon, 06 Feb 2023 10:34:24 -0500
Message-ID: <2665266.1675697664@sss.pgh.pa.us> (raw)
In-Reply-To: <BJSPR01MB0532EC69D0B373A7C23F85369CDA9@BJSPR01MB0532.CHNPR01.prod.partner.outlook.cn>
References: <BJSPR01MB0532EC69D0B373A7C23F85369CDA9@BJSPR01MB0532.CHNPR01.prod.partner.outlook.cn>
"Qu, Mischa, Majorel China" <mischa.qu@majorel.cn> writes:
> 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
What sort of plan is that query using? If it's a merge join, I'd
bet that the problem is related to local and remote servers not
having the same default collation.
regards, tom lane
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: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, mischa.qu@majorel.cn
Subject: Re: exceptional result of postres_fdw external table joining local table
In-Reply-To: <2665266.1675697664@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox