0

I have one table with 7 jsonb columns and 13 simple columns. 2 of the jsonb columns have about 15 keys each with composite value data types. Right now the number of rows is around 15k. When i do select * in postgresql , I get the results in 2 seconds on an average.

But when i query the same in my spring boot Hibernate application, I am getting the results in about 40 seconds on an average. I am simply using JpaSpecificationExecutor.findAll(Specification spec, Pageable pageable) method to get the results.

If I drop these columns, I am able to get the results in 3 seconds.

What should I do to increase the performance in my application.

Here is the structure of one of the heavy jsonb columns

`String userProfileUrl;
List<String> groupAsssociated;
Double rate;
String code;
LocalDateTime lastLoginTime;
//Product is an Enum here with 3 key value pairs
Map<Product, LocalDateTime> lastTxnTime;
Map<String, Double> usageLimit;
Double dailySmsLimit;
//DocumentInfo here is a separate class with4 keys each having String //values
List<DocumentInfo> documents;
Map<String, String> fees;
String grade;
Map<DocumentInfo, DeliveryInfo> deptInfo;
Boolean disableUser;
//ParentUserInfo is a separate class with 5 keys each having String values
List<ParentUserInfo> parentInfo;`
Neha Arora
  • 11
  • 5

1 Answers1

0

Need more details on how you map jsonb columns. If you are using custom hibernate types like https://github.com/vladmihalcea/hibernate-types, there is some performance overhead because of json strings to target object conversion.

Amit Mendapara
  • 68
  • 1
  • 3