JPA where in embeddable optimization (Hibernate, PostgreSQL)

Question

I have built a simple Query that returns a result list by searching an @Embeddable field with a list of possibilities. The query is:

select d from data d where embedField in :embedValues

It works fine, but with hibernate and PostgreSQL the resulting native query issued to the sql server is like this:

select * from data where 
    f1=$1 and f2=$2
  or
    f1=$3 and f2=$4
  or
    f1=$5 and f2=$6

I would have expected the native query to be more efficient, e.g.,

select * from data where (f1,f2) in (($1,$2),($3,$4),($5,$6))

I wrote the namedQuery because I wanted better performance, but I don't think I will get it with the query being executed. Is there a better way to easily get a better native query more like what I was expecting?

For completeness sakes, I am adding some illustrative classes.

@Embeddable class Embed {
  int f1;
  int f2;
  public Embed(int i, int j) {f1 = i; f2 = j;}
}

@Entity class Datum {
  @Embedded Embed embedField;
}

class Test {
  public static void main(String... args) {
    new Test().run();
  }
  public void run() {
    EntityManger em = ...
    List<Datum> data = em.createQuery("select d from Data d where embedField in $1")
      .setParameter(1, Arrays.asList(new Embed(1,2),new Embed(3,4),new Embed(5,6)))
      .getResultList();
  }
}

Thanks for any help, appreciated.

Answer 1

Well, I wrote a native Query in a String that did what I wanted, as described above, but it gave absolutely no performance benefit. I can well imagine that by the time the SQL server turns the queries into an execution plan it is the same result anyway and thus no difference. So, the answer is, no, there is no better way to write the JQPL query, it is fine as it is.