I am using Spring Data with EclipseLink and PostgreSQL. When Spring Data Repository generate count queries using Specification and querybuilder I get something like:
SELECT COUNT(id) FROM table WHERE ...
now the problem is that I do not want COUNT(id) but rather COUNT(*) because it is faster and can use indexes in most cases (explain shows it is about 10 times faster than COUNT(id) because it just counts records using index and not records having "id" not null which causes many row data reads and postgresql planner switches to full scan).
Is there a way eclipselink could generate COUNT(*) instead of COUNT(PK)?
I traced Spring Data down to functions:
protected TypedQuery<Long> getCountQuery(Specification<T> spec) {
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Long> query = builder.createQuery(Long.class);
Root<T> root = applySpecificationToCriteria(spec, query);
if (query.isDistinct()) {
query.select(builder.countDistinct(root));
} else {
query.select(builder.count(root));
}
return em.createQuery(query);
}
private <S> Root<T> applySpecificationToCriteria(Specification<T> spec, CriteriaQuery<S> query) {
Assert.notNull(query);
Root<T> root = query.from(getDomainClass());
if (spec == null) {
return root;
}
CriteriaBuilder builder = em.getCriteriaBuilder();
Predicate predicate = spec.toPredicate(root, query, builder);
if (predicate != null) {
query.where(predicate);
}
return root;
}
so it seems everything is in EclipseLink... any help appreciated.
Home