My query looks like this, and performs well:
select *
from table t
where (t.one,t.two,t.three) in ( (11,12,13), (21,22,23) );
Now the number of these triplets within the in
statement will vary, so I'm trying to do the following with JDBC:
String sql = "select * from table where (one, two, three) in (select * from unnest(?::smallint[], ?::integer[], ?::integer[]))"
// conn is the java.sql.Connection
PreparedStatement ps = conn.prepareStatement(sql);
ps.setArray(1, conn.createArrayOf("smallint", new Short[]{11, 21}));
ps.setArray(2, conn.createArrayOf("integer", new Integer[]{12,22}));
ps.setArray(3, conn.createArrayOf("integer", new Integer[]{13,23}));
return ps;
And that performs terribly bad. So is there a way to send the triplets using jdbc such that the end result is equivalent to the sql query?
I'd have two btree indexes one on one, two
and another on one, three
I need to use any of those in order to make this performant