How can I customize the count query to improve the pagination performence

[lujun2]

Application use postgres as database and has a simple list view with pagination.
But the data volume is quite large (about 30 million), a simple select and count sql can take more than 10 seconds to return.

After some investination found that, doing exact count is a expensive task because of MVCC. So I decide to return the estimate count when data volume is large

Seems like if I override AbstractFullQueryBuilder#buildPageCountQueryString and create my own counting sql to return the esmmitate count and imporve performance

But the logic in AbstractFullQueryBuilder#buildPageCountQueryString is really complex 😨, any suggestion to allow me implement a estimate version of couting sql

Thanks

[beikov]

Have you tried using bounded counting instead? Usually, that works quite well when you e.g. count up to 501, you can show in your UI that there are 500+ elements available. There is also a way to extract a count query to determine the exact count on demand if needed.

This code is not intended to be extended by users since it touches all of the internals. Reducing a query to the bare minimum that still produces the correct count is a somewhat complex task, which you already noticed by looking at the code 😄

How would you estimate the count? Maybe we can add that to Blaze-Persistence so that others can also benefit from this.

[lujun2]

Ok, maybe I can change the ui behavior, use infinite scroll instead

---

in order to estimate the record count, I found project PostgREST which support planned and estimated count.
Basically it use PostgreSQL statistics information to estimate the record count

Sadly PostgREST project in writing in Haskell, need some effort to translate it into Java, but it is POSSIBLE

[lujun2]

and we can use a postgres function to implement this estimation feature which mention in https://www.citusdata.com/blog/2016/10/12/count-performance/

CREATE FUNCTION count_estimate(query text) RETURNS integer AS $$
DECLARE
  rec   record;
  rows  integer;
BEGIN
  FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
    rows := substring(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
    EXIT WHEN rows IS NOT NULL;
  END LOOP;
  RETURN rows;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;

[beikov]

I'm not against adding something like that, but I think projects usually are better off by using bounded counting or not revealing the count at all and offer infinite scrolling.