java - How to optimize pagination on a SELECT DISTINCT sql? -

my query select distinct on large database, , in pgadmin sql tool query lasts 12 sec.

select distinct on (city, airport, zip, country, name) city, airport, price, id mytable; 

the spring-batch reader definition:

jpapagingitemreader<myentity> reader; reader.setpagesize(page_size); 

if define page_size large database columns, performance equal 12 sec. if set size lower value (eg pagesize = 100.000 on 1.000.000 datarows db), performance bad (~ 10x long).

spring-batch applies specific pagination on queries in background. does:

query.setfirstresult(); query.setmaxresult(); 

if page size 10, den queries executed follows:

    firstresult, maxresult     0, 10    10, 10    20, 10    30, 10... 

this again translates limit , offset in sql.

question: select distinct on not combinable pagination limit/offset? me seems if full select distinct query executed again on each "pagination" run, , lasts long.

so, if database must anyhow make full distinct select before applying maxresults, can temporary save "distinct" select , fetch next page?

how can improve this, without having set pagination size 1million. or improvement not possible here?

if aren't using multiple threads process this, alternative use jdbccusoritemreader. way wouldn't need optimize paging aspect of query @ all. if you're using multiple threads, isn't option.