On 5/28/20 9:59 AM, Ying Lu wrote:
Hello,
would help to see the EXPLAIN ANALYZE for the queries above.
And the EXPLAIN ANALYZE for the first query?
Also please include the entire query, for example:
EXPLAIN ANALYZE select count(*) from oracle_t1 as a inner join local_t1
as b on (a.c1 = b.c1 OR a.c2 = b.c1);
This is important because in below I see:
Filter: (yr= '2020'::text)
which I don't see in your original queries.
Please find the explain analyze info
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..736.49 rows=489 width=333) (actual time=313.495..1224.671 rows=9 loops=1)
Join Filter: ((a.c1 = b.c1) OR (a.c2 = b.c1))
Rows Removed by Join Filter: 2106
-> Seq Scan on local_t1 b (cost=0.00..1.49 rows=49 width=13) (actual time=0.016..0.029 rows=49 loops=1)
-> Foreign Scan on oracle_t1 a (cost=0.00..0.00 rows=1000 width=320) (actual time=3.445..24.977 rows=43 loops=49)
Filter: (yr= '2020'::text)
Rows Removed by Filter: 255
Planning Time: 0.532 ms
Execution Time: 1327.697 ms
Thanks.
________________________________________
psql12.3 + jdbc_fdw(oracle18.x), tried:
[1]
select count(*)
from oracle_t1 as a
inner join local_t1 as b
on (a.c1 = b.c1);
return 100 records
[2]
select count(*)
from oracle_t1 as a
inner join local_t1 as b
on (a.c1 = b.c1
OR
a.c2 = b.c1
);
return only 2 records
(no null values in both tables.)
May I know what may cause the error please?
I'm guessing you are seeing this:
https://www.postgresql.org/docs/12/sql-expressions.html
4.2.14. Expression Evaluation Rules
". Boolean expressions (AND/OR/NOT combinations) in those clauses can be
reorganized in any manner allowed by the laws of Boolean algebra."
It would help to see the EXPLAIN ANALYZE for the queries above.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
