Re: psql12.3 + jdbc_fdw - return wrong query results by using OR

[Ying Lu]

Hello, 

Please find the info for both SQLs (removed yr)

For Q1: 

                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=5.40..5.41 rows=1 width=8) (actual time=1267.001..1267.001 
rows=1 loops=1)
   ->  Hash Join  (cost=2.10..4.78 rows=245 width=0) (actual 
time=0.134..1265.840 rows=2650 loops=1)
         Hash Cond: (a.c1 = b.c1)
         ->  Foreign Scan on oracle_t1 a  (cost=0.00..0.00 rows=1000 width=32) 
(actual time=0.026..1257.823 rows=14625 loops=1)
         ->  Hash  (cost=1.49..1.49 rows=49 width=5) (actual time=0.030..0.030 
rows=49 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 10kB
               ->  Seq Scan on local_t1 b  (cost=0.00..1.49 rows=49 width=5) 
(actual time=0.014..0.021 rows=49 loops=1)
 Planning Time: 0.178 ms
 Execution Time: 1363.482 ms


For Q2: 

     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=737.71..737.72 rows=1 width=8) (actual 
time=1197.366..1197.366 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..736.49 rows=489 width=0) (actual 
time=16.649..1197.292 rows=70 loops=1)
         Join Filter: ((a.c1 = b.c1) OR (a.c2 = b.c1))
         Rows Removed by Join Filter: 14555
         ->  Seq Scan on local_t1 b  (cost=0.00..1.49 rows=49 width=5) (actual 
time=0.016..0.023 rows=49 loops=1)
         ->  Foreign Scan on oracle_t1 a  (cost=0.00..0.00 rows=1000 width=64) 
(actual time=0.002..24.284 rows=298 loops=49)
 Planning Time: 0.972 ms
 Execution Time: 1299.896 ms


Thanks a lot.