JPA Specification count with upper limit

Question

I'm using a readonly JPA Entity MyView built on top of a complicated PostgreSQL View my_view that uses Unions of different tables:

@Entity
@Immutable
@Data
public class MyView {
    @Id
    private Long id;
    private String name;
}

I'm also using (Spring Data) JPA Specifications on this Entity to build dynamic queries. Finally I'm using the preexisting query method to execute: Page findAll(Specification spec, Pageable pageable);

All of this works perfectly fine - but the View is so complicated it requires a full scan when sorting OR counting. By introducing indices on all relevant tables and columns I got the execution planner to use at least Index Scans when filtering, but Index-Only Scans seem to be prevented by the underlying Unions of different tables.

This means it still needs to load all rows that match the filter criteria into memory to rescan for sorting or even just counting. If no filter hits / the rows are basically unfiltered, every page is loaded once, making the whole query run for about tens of seconds. If filtering drops down the count to 100000 or less it's a question of milliseconds instead.

Further optimization of the query and index structures has diminishing returns at this point, and changing the underlying tables is impractical because of other dependents. Materializing the View or switching anything in the tech stack is also not within scope.

So instead I'd like to change the query coming into the database. Ordering is not really required for the application, so the big question is about the count necessary for pagination.

What would be acceptable is to have a limit for the count - for a few results the exact count may be useful, but if the total count ends up higher than 10000, just saying 'more than 10000 results' in the UI is perfectly fine.

PostgreSQL would allow something like

SELECT COUNT(*) FROM my_view WHERE name LIKE '%abc%'; -- incredibly slow because we need to load gigabytes of data
SELECT COUNT(*) FROM ( SELECT * FROM my_view WHERE name LIKE '%abc%' LIMIT 10000) x; -- incredibly fast because after loading a few pages into memory we reach the limit

Implementing this into custom Spring Data repository methods has turned out to be a harder problem than expected. If the query were static I could send out a native query and be done with it, but Specifications are important.

A few leads in my current research:

introducing a Hibernate StatementInspector may help us inject custom SQL
overwriting/extending the PostgreSQL dialect may do the same in a more specific manner
using query.grouping() and query.having() we may be able to build a different JPQL TypedQuery with the same intention inside the Repository implementation: returning the count from something but with an upper limit. How this query would look like is sadly beyond me.
a not-solution would be to e.g. use a projection to only return column id and just request the first 10000 elements with a normal query and count the list in Java. While internally this would be fine for the DB, the new bottleneck would be network. It also seems too crude.
way more interesting solution would be to use the EXPLAIN sql feature to get a quick row count estimate, but implementing this with JPA seems even more complicated than just limiting the count to a reasonable size.

Unfortunately this is also where I currently stand.

What are the best ways to gain this functionality of a JPA count upper limit, and are there existing solutions I'm overlooking?

JPA Specification count with upper limit

0 Answers