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

You can create an index on lower(firstname) that should be used by the query (but only if the wildcards are at the end of the comparison value). Another option would be to create a trigram index: depesz.com/index.php/2011/02/19/… — user330315
– user330315, Commented Sep 15, 2015 at 10:08
Thanks, it does answer my initial question, although I find myself struggling with other issues. We're already using trigram indexes, but it appears Postgre is not using the expected index. I'll try to figure out if we can remove the conflicting index (using btree on two columns), otherwise I'm a bit short on this issue. I also tried using citext data type, but the query analyzer shows that there are no index used, and searches indicates this issue, with lower index as a solution : dba.stackexchange.com/questions/105244/… — Christophe Douy
– Christophe Douy, Commented Sep 15, 2015 at 18:03
Please read this: wiki.postgresql.org/wiki/Slow_Query_Questions and then add the missing information to your question. This is impossible to answer without more information (and: it's either Postgres, PostgreSQL or simply pg, but never Postgre) — user330315
– user330315, Commented Sep 15, 2015 at 18:27

bsiamionau · Accepted Answer · 2018-01-22 13:22:00Z

3

I've got exactly the same issue - lower(column) causes wrong pg statistics calculation and request is planned not efficiently, ilike solves the problem. I hadn't understood what parts of OP's answer are relevant to solution so reinvented the same approach, but a bit shorter.

Introduce new dialect with my_ilike function and it's implementation:

public class ExtendedPostgresDialect extends org.hibernate.dialect.PostgreSQL9Dialect {
    public ExtendedPostgresDialect() {
        super();
        registerFunction("my_ilike", new SQLFunctionTemplate(BooleanType.INSTANCE, "(?1 ilike ?2)"));
    }
}

Specify this dialect to be used by Hibernate (I use Java config):

Properties props = new Properties();
props.setProperty("hibernate.dialect", "com.example.ExtendedPostgresDialect");
factory.setJpaProperties(props);

That's it, now you can use it:

BooleanTemplate.create("function('my_ilike', {0}, {%1%})", stringPath, value).isTrue();

answered Jan 22, 2018 at 13:22

bsiamionau

8,2575 gold badges52 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Surya Mukerjee Over a year ago

In the new version of QueryDSL, they have replaced BooleanTemplate.create(...) with Expressions.booleanTemplate(....)

Christophe Douy · Accepted Answer · 2015-11-26 16:48:17Z

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()

Collectives™ on Stack Overflow

QueryDSL not using Postgres Indexes

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related