[OPENJPA-2920] CriteriaBuilder generating suboptimal code

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.2.0
• Component/s: criteria
• Assignee: Unassigned
• Reporter: Rik Schaaf
• Created: 12/Apr/24 22:41
• Updated: 18/Jul/24 06:49

Description

Background:

I have the following entity:

package com.github.cc007.headsplugin.integration.database.entities;

import lombok.AccessLevel;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Index;
import javax.persistence.ManyToMany;
import javax.persistence.Table;
import javax.persistence.Version;
import java.util.Collections;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "heads",
        indexes = {
                @Index(name = "heads_headowner_index", columnList = "headOwner"),
                @Index(name = "heads_name_index", columnList = "name")
        }
)
@Getter
@Setter
@ToString
@NoArgsConstructor
public class HeadEntity {

    @Id
    @Column(name = "id", nullable = false)
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Setter(AccessLevel.NONE)
    private long id;

    @Version
    @Column(name = "version", nullable = false)
    private long version;

    @Column(name = "headOwner", unique = true, nullable = false)
    private String headOwner;

    @Column(name = "name", nullable = false)
    private String name;

    @Column(name = "value", length = 1023, nullable = false)
    private String value;

    @ToString.Exclude
    @ManyToMany(
            fetch = FetchType.EAGER,
            cascade = {CascadeType.PERSIST, CascadeType.MERGE},
            mappedBy = "heads"
    )
    @Getter(AccessLevel.NONE)
    @Setter(AccessLevel.NONE)
    private Set<DatabaseEntity> databases = new HashSet<>();

    @ToString.Exclude
    @ManyToMany(
            fetch = FetchType.EAGER,
            cascade = {CascadeType.PERSIST, CascadeType.MERGE},
            mappedBy = "heads"
    )
    @Getter(AccessLevel.NONE)
    @Setter(AccessLevel.NONE)
    private Set<TagEntity> tags = new HashSet<>();

    @ToString.Exclude
    @ManyToMany(
            fetch = FetchType.EAGER,
            cascade = {CascadeType.PERSIST, CascadeType.MERGE},
            mappedBy = "heads"
    )
    @Getter(AccessLevel.NONE)
    @Setter(AccessLevel.NONE)
    private Set<CategoryEntity> categories = new HashSet<>();

    @ToString.Exclude
    @ManyToMany(
            fetch = FetchType.LAZY,
            cascade = {CascadeType.PERSIST, CascadeType.MERGE},
            mappedBy = "heads"
    )
    @Getter(AccessLevel.NONE)
    @Setter(AccessLevel.NONE)
    private Set<SearchEntity> searches = new HashSet<>();

    public Set<DatabaseEntity> getDatabases() {
        return Collections.unmodifiableSet(databases);
    }

    public Set<TagEntity> getTags() {
        return Collections.unmodifiableSet(tags);
    }

    public Set<CategoryEntity> getCategories() {
        return Collections.unmodifiableSet(categories);
    }

    public Set<SearchEntity> getSearches() {
        return Collections.unmodifiableSet(searches);
    }
} 

Note the  unique = true, nullable = false (and the heads_headowner_index)

I have created a couple of functions using a CriteriaBuilder:

public List<String> findAllHeadOwnersByHeadOwnerIn(Collection<String> headOwners) {
    final var resultList = new ArrayList<String>();
    final var headOwnerGroups = CollectionUtils.partitionCollection(headOwners, configProperties.getDatabase().getChunkSize());
    for (final var headOwnerGroup : headOwnerGroups) {
        final var query = querySelectionByCondition(HeadEntity.class,
                root -> root.get("headOwner"), String.class,
                (criteriaBuilder, root) -> root.get("headOwner").in(headOwnerGroup)
        );
        resultList.addAll(query.getResultList());
    }
    return resultList;
}

public <E, T> TypedQuery<T> querySelectionByCondition(
        Class<E> entityType,
        Function<Root<E>, Path<T>> selection,
        Class<T> selectPropertyType,
        BiFunction<CriteriaBuilder, Root<E>, Predicate> whereCondition
) {
    final var criteriaBuilder = entityManager.getCriteriaBuilder();

    final var criteriaQuery = criteriaBuilder
            .createQuery(selectPropertyType);

    final var root = criteriaQuery.from(entityType);

    criteriaQuery.select(selection.apply(root)).where(whereCondition.apply(criteriaBuilder, root));

    return entityManager.createQuery(criteriaQuery);
}

public <E> Optional<E> getSingleResult(TypedQuery<E> query) {
    try {
        return Optional.of(query.getSingleResult());
    } catch (NoResultException e) {
        return Optional.empty();
    }
}

public <T> List<T> getMutableResultList(TypedQuery<T> query) {
    return new ArrayList<>(query.getResultList());
} 

 

Note the root.get("headOwner").in(headOwnerGroup) that is used in the findAllHeadOwnersByHeadOwnerIn function.
(FYI: I use a chunksize of 500, because it was causing issues in the past when I didn't partition or used anything above 1000).

Expected behavior:

When I run code that uses this, I would expect it to generate a SELECT h.headOwner FROM heads h WHERE h.headowner IN (...);

I also expected the query to be fast, even in a database with 10000s of rows, due to the index.

Actual behavior:

The resulting typed query is this:

SELECT t0.headOwner FROM heads t0 
WHERE (
 (
     t0.headOwner = ?
  OR t0.headOwner = ?
  OR t0.headOwner = ?
  OR t0.headOwner = ?

  -- in total 500 t0.headOwner comparisons

  OR t0.headOwner = ?
  OR t0.headOwner = ?
  OR t0.headOwner = ?
 ) 
 AND t0.headOwner IS NOT NULL
) 

Note the AND t0.headOwner IS NOT NULL even though headOwner is not nullable.

In a database with 50000-ish rows, this query takes about 1500-2500ms to complete.
If I ran the query, without the AND statement, so:

SELECT t0.headOwner FROM heads t0 
WHERE (
 (
     t0.headOwner = ?
  OR t0.headOwner = ?
  OR t0.headOwner = ?

-- in total 500 t0.headOwner comparisons

  OR t0.headOwner = ?
  OR t0.headOwner = ?
  OR t0.headOwner = ?
 ) 
 -- without the AND t0.headOwner IS NOT NULL
)

This query only takes 150ms to complete.
If I were to use SELECT h.headOwner FROM heads h WHERE h.headowner IN (...); with 500 values in the IN() expression, then it only takes 80-100ms

Versions:

• OpenJPA: 3.2.0
• HSQLDB: 2.7.1
• Java: OpenJDK 17.0.2

Config:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
             xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
  <persistence-unit name="default" transaction-type="RESOURCE_LOCAL">
    <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
    <class&amp;gt;com.github.cc007.headsplugin.integration.database.entities.CategoryEntity</class&amp;gt;
    <class&amp;gt;com.github.cc007.headsplugin.integration.database.entities.DatabaseEntity</class&amp;gt;
    <class&amp;gt;com.github.cc007.headsplugin.integration.database.entities.HeadEntity</class&amp;gt;
    <class&amp;gt;com.github.cc007.headsplugin.integration.database.entities.SearchEntity</class&amp;gt;
    <class&amp;gt;com.github.cc007.headsplugin.integration.database.entities.TagEntity</class&amp;gt;
    <exclude-unlisted-classes>true</exclude-unlisted-classes>
    <properties>
      <property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema(SchemaAction='add', ForeignKeys=true)"/>
      <property name="openjpa.ConnectionURL" value="jdbc:hsqldb:file:plugins/HeadsPluginAPI/data/heads;hsqldb.lock_file=false"/>
      <property name="openjpa.ConnectionDriverName" value="org.hsqldb.jdbcDriver"/>
      <property name="openjpa.ConnectionUserName" value="sa"/>
      <property name="openjpa.ConnectionPassword" value=""/>
<!--      <property name="openjpa.Log" value="DefaultLevel=WARN, Tool=INFO"/>-->
      <property name="openjpa.Log" value="DefaultLevel=WARN, Tool=INFO, SQL=TRACE"/>
    </properties>
  </persistence-unit>
</persistence>