Teiid not performing optimal join

Question

For our Teiid Springboot project we use a row filter in a where clause to determine what results a user gets. Example:

SELECT * FROM very_large_table WHERE id IN ('01', '03')

We want the context in the IN clause to be dynamic like so:

SELECT * FROM very_large_table WHERE id IN (SELECT other_id from very_small_table)

The problem now is that Teiid gets all the data from very_large_table and only then tries to filter with the where clause, this makes the query 10-20 times slower. The data in this very_small_tableis only about 1-10 records and it is based on the user context we get from Java.

The very_large_table is located on a Oracle database and the very_small_table is on the Teiid Pod/Container. Somehow I can't force Teiid to ship the data to Oracle and perform filtering there.

Things that I have tried: I have specified the the foreign data wrappers as follows

CREATE FOREING DATA WRAPPER "oracle_override" TYPE "oracle" OPTIONS (EnableDependentsJoins 'true');
CREATE SERVER server_name FOREIGN DATA WRAPPER "oracle_override";

I also tried, exists statement or instead of a where clause use a join clause to see if pushdown happened. Also hints for joins don't seem to matter.

Sadly the performance impact at the moment is that high that we can't reach our performance targets.

Answer 1

Are there any cardinalities on very_small_table and very_large_table? If not the planner will assume a default plan.

You can also use a dependent join hint:

SELECT * FROM very_large_table WHERE id IN /*+ dj */ (SELECT other_id from very_small_table)

Answer 2

Often, exists performs better than in:

SELECT vlt.*
FROM very_large_table vlt
WHERE EXISTS (SELECT 1 FROM very_small_table vst WHERE vst.other_id = vlt.id);

However, this might end up scanning the large table.

If id is unique in vlt and there are no duplicates in vst, then a JOIN might optimize better:

select vlt.*
from very_small_table vst join
     very_large_table vlt
     on vst.other_id = vlt.id;