[Solved] JPA & Criteria API specific selection, extract with select
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

JPA & Criteria API specific selection, extract with select

Database type:

the task is to group events by a specific day, to do this, I have to pull the day out of the date and group the records by it. We used to use MySql and this code worked as it should:

public static Specification<AllFile> findByGroup(FileFilter fileFilter) {
    return (Specification<AllFile>) (root, query, cb) -> {
        final Collection<Predicate> predicates = new ArrayList<>();

        if (fileFilter.getStart() != null && fileFilter.getEnd() != null) {
            Date date = new Date(fileFilter.getStart().getTime());
            date.setHours(0);
            date.setMinutes(0);
            date.setSeconds(0);
            date.setDate(1);

            predicates.add(cb.greaterThanOrEqualTo(root.get("created"), date));
            predicates.add(cb.lessThanOrEqualTo(root.get("created"), fileFilter.getEnd()));
        }
        else if(fileFilter.getStart() != null) {
            Date date = new Date(fileFilter.getStart().getTime());
            date.setHours(0);
            date.setMinutes(0);
            date.setSeconds(0);
            date.setDate(1);

            Date newDate = DateUtils.addMonths(date, 1);

            predicates.add(cb.greaterThanOrEqualTo(root.get("created"), date));
            predicates.add(cb.lessThanOrEqualTo(root.get("created"), newDate));
        }

        if (fileFilter.getRoute() != null) {
            predicates.add(cb.equal(root.get("route"), fileFilter.getRoute()));
        }

        if (fileFilter.getDevice() != null) {
            predicates.add(cb.equal(root.get("device"), fileFilter.getDevice()));
        }

        if (fileFilter.getType() != null) {
            predicates.add(cb.equal(root.get("type"), fileFilter.getType()));
        }

        if (fileFilter.getUser() != null) {
            predicates.add(cb.equal(root.get("user"), fileFilter.getUser()));
        }

        if (fileFilter.getDeleted() != null) {
            predicates.add(cb.equal(root.get("deleted"), fileFilter.getDeleted()));
        }



        query.groupBy(cb.function("day", Date.class, root.get("created")));
        return cb.and(predicates.toArray(new Predicate[predicates.size()]));
    };
}

But after switching to Postgres, this approach does not work. I need to send something like

select extract(day from allfile0_.created) as dd
from all_file allfile0_ 
where allfile0_.created>='Tue Oct 01 00:00:00 MSK 2019' and allfile0_.created<='Fri Nov 01 00:00:00 MSK 2019' 
group by dd

instead of this

select allfile0_.id as id1_0_, allfile0_.added as added2_0_, allfile0_.comment as comment3_0_, 
allfile0_.created as created4_0_, allfile0_.deleted as deleted5_0_, allfile0_.device_id as 
device_13_0_, allfile0_.duration as duration6_0_, allfile0_.mark as mark7_0_, allfile0_.path as 
path8_0_, allfile0_.recognition as recognit9_0_, allfile0_.route_id as route_i14_0_, allfile0_.size 
as size10_0_, allfile0_.type as type11_0_, allfile0_.updated as updated12_0_, allfile0_.user_id as 
user_id15_0_ 

from all_file allfile0_ 

where allfile0_.created>='2019-10-01 00:00:00' and allfile0_.created<='2019-10-04 00:00:00' 

group by extract(day from allfile0_.created)

is it possible to get what i nedd with Criteria API? Asking for advice about custom selection, to select only what i need.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.
Optimal indexes for this query:
ALTER TABLE `all_file` ADD INDEX `all_file_idx_created` (`created`);
The optimized query:
SELECT
        EXTRACT(day 
    FROM
        allfile0_.created) AS dd 
    FROM
        all_file allfile0_ 
    WHERE
        allfile0_.created >= 'Tue Oct 01 00:00:00 MSK 2019' 
        AND allfile0_.created <= 'Fri Nov 01 00:00:00 MSK 2019' 
    GROUP BY
        dd 
    ORDER BY
        NULL

Related Articles



* original question posted on StackOverflow here.