Select all records from offset to limit using a postgres index

Question

I want to get all data from offset to limit from a table with about 40 columns and 1.000.000 rows. I tried to index the id column via postgres and get the result of my select query via java and an entitymanager.

My query needs about 1 minute to get my results, which is a bit too long. I tried to use a different index and also limited my query down to 100 but still it needs this time. How can i fix it up? Do I need a better index or is anything wrong with my code?

CriteriaQuery<T> q = entityManager.getCriteriaBuilder().createQuery(Entity.class);
TypedQuery<T> query = entityManager.createQuery(q);
List<T> entities = query.setFirstResult(offset).setMaxResults(limit).getResultList();

Curiosa Globunznik · Accepted Answer · 2019-10-15T11:45:34.517

Right now you probably do not utilize the index at all. There is some ambiguity how a hibernate limit/offset will translate to database operations (see this comment in the case of postgres). It may imply overhead as described in detail in a reply to this post.

If you have a direct relationship of offset and limit to the values of the id column you could use that in a query of the form

SELECT e
FROM Entity
WHERE id >= offset and id < offset + limit

Given the number of records asked for is significantly smaller than the total number of records int the table the database will use the index.

The next thing is, that 40 columns is quite a bit. If you actually need significantly less for your purpose, you could define a restricted entity with just the attributes required and query for that one. This should take out some more overhead.

If you're still not within performance requirements you could chose to take a jdbc connection/query instead of using hibernate.

Btw. you could log the actual sql issued by jpa/hibernate and use it to get an execution plan from postgress, this will show you what the query actually looks like and if an index will be utilized or not. Further you could monitor the database's query execution times to get an idea which fraction of the processing time is consumed by it and which is consumed by your java client plus data transfer overhead.

True story, i didnt use the index in a good way. I reindexed my database table and now I still have kind access time. — Dennis, Oct 15 '19 at 11:42

score 0 · Answer 2 · answered Oct 15 '19 at 08:27

There also is a technique to mimick the offset+limit paging, using paging based on the page's first record's key.

Map<Integer, String> mapPageTopRecNoToKey = new HashMap<>();

Then search records >= page's key and load page size + 1 records to find the next page.

Going from page 1 to page 5 would take a bit more work but would still be fast.

This of course is a terrible kludge, but the technique at that time indeed was a speed improvement on some databases.

In your case it would be worth specifying the needed fields in jpql: select e.a, e.b is considerably faster.

Select all records from offset to limit using a postgres index

2 Answers2