QueryDSL not using Postgres Indexes

Question

I'm using Hibernate and QueryDSL along with PostgreSQL on a Spring application, and face some performance issues with my filtered lists. Using the StringPath class, I'm calling either startsWithIgnoreCase, endsWithIgnoreCase or containsIgnoreCase. It appears the generated query has the following where clause :

WHERE lower(person.firstname) LIKE ? ESCAPE '!'

Using the lower, the query is not taking advantage of the Postgres indexes. On a dev Database, queries take up to 1sec instead of 10ms with the ILIKE keyword.

Is there a way to get a Predicate using Postgres' ILIKE, as Ops doesn't seem to provide it?

Thanks

Answer 1

Had to update this :

We found a way to create the needed Postgres operators by registering a SQL function using ilike, in our custom Hibernate Dialect.

Example with ilike :

//Postgres Constants Operators
public class PostgresOperators {
    private static final String NS = PostgresOperators.class.getName();
    public static final Operator<Boolean> ILIKE = new OperatorImpl<>(NS, "ILIKE");
}

//Custom JPQLTemplates
public class PostgresTemplates extends HQLTemplates {

    public static final PostgresTemplates DEFAULT = new PostgresTemplates();

    public PostgresTemplates() {
        super();
        add(PostgresOperators.ILIKE, "my_ilike({0},{1})");
    }
}

Specify the JPQLTemplates when using jpaquery

new JPAQuery(entityManager, PostgresTemplates.DEFAULT);

now it gets tricky, we couldn't use ilike directly, there is an issue with an "ilike" keyword already registered, so we made an ilike function and registered it to a custom spring hibernate Dialect.

Our application.yml specifying :

#SEE JPA http://docs.spring.io/spring-boot/docs/current/reference/html/common-application-properties.html    
spring.data.jpa:com.example.customDialect.config.database.ExtendedPostgresDialect

Then

public class ExtendedPostgresDialect extends org.hibernate.dialect.PostgreSQL82Dialect {
    public ExtendedPostgresDialect() {
        super();
        registerFunction("my_ilike", new PostgreSQLIlikeFunction());
    }
}

We tried to use the registerKeyword("ilike"), didn't work, we stayed with our function and the following implementation.

public class PostgreSQLIlikeFunction implements SQLFunction {

    @Override
    public Type getReturnType(Type columnType, Mapping mapping)
        throws QueryException {
        return new BooleanType();
    }

    @SuppressWarnings("unchecked")
    @Override
    public String render(Type firstArgumentType, List args, SessionFactoryImplementor factory) throws QueryException {
        if (args.size() != 2) {
            throw new IllegalArgumentException(
                "The function must be passed 2 arguments");
        }

        String str1 = (String) args.get(0);
        String str2 = (String) args.get(1);

        return str1 + " ilike " + str2;
    }

    @Override
    public boolean hasArguments() {
        return true;
    }

    @Override
    public boolean hasParenthesesIfNoArguments() {
        return false;
    }

}

That's pretty much it, now we can use ILIKE the following way :

 BooleanOperation.create(PostgresOperators.ILIKE, expression1, expression2).isTrue()