Ying Lu <emi.lu@xxxxxxxxxxxx> writes: > 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 The numbers here are consistent with the theory that there are 14625 rows in the foreign table, but when oracle_t1 is scanned on the inside of a nest loop, the FDW returns all of them on the first scan and then forgets to return any when rescanned. This'd be a bug in jdbc_fdw, and a pretty bad one :-(. But you'd have to report it to the jdbc_fdw author(s) --- the core Postgres project doesn't maintain that. regards, tom lane
