Fetching large volume of data from postgres db using spring boot rest api

See more:

Java

PostgreSQL

Spring.NET

Spring

, +

I'm trying to fetch data using Spring Boot for my REST API from PostgreSQL Database, I have more than 30 million records in my database, the API takes a lot of time to pull data from the database and sometimes it results on time out error when trying to fetch data, I don't know what I'm missing on this implementation or it's the query that takes a lot of time and if is the query how best can I optimize it so as I can fast track the pulling of large volume of data more faster from the database and get the count depending on some conditions, I joining three tables each with more than 30+ million records.

Below is my code structure / implementation

What I have tried:

// Below is my Interface Class

@Repository
public interface ClientRepository extends JpaRepository<Client, String> {

 @Query("select DISTINCT c, o from Client c left join Order o on c.ClientID = o.ClientID left join Status s on c.ClientID = s.ClientID where o.Code ='2' and "
            + "o.OrderDate >=:startDate and o.OrderDate <=:endDate and s.StatusDate >=:startDate and s.StatusDate <=:endDate and (s.OrderStatus != 'Cancelled' or s.OrderStatus != 'Pending')")
 List<Client> countClientOrder(LocalDateTime startDate, LocalDateTime endDate);
}

// Below is my service implementation class

@Service
public class ClientService {
    
    @Autowired
    private ClientRepository clientRepository;
    
    public int countClientOrder(int year, int month) {
        
    YearMonth yearMonth_ = YearMonth.of(year, month);
    LocalDate original_startDate = yearMonth_.atDay(1);
    LocalDate original_endDate = yearMonth_.atEndOfMonth();
    
    LocalDateTime startDate = original_startDate.atStartOfDay();
    LocalDateTime endDate = original_endDate.atStartOfDay();
    
    var data = clientRepository.countClientOrder(startDate, endDate).size();
    return data;
    }
}

// Below is my Rest API

@RestController
@RequestMapping("/orders_api")
public class ClientApi {

    @Autowired
    private ClientService clientService;
    
    @GetMapping("/requested_on/{year}/{month}")
    protected int getClient_Orders(@PathVariable int year, @PathVariable int month) {
    return clientService.countClientOrder(year,month);  
    }
}

// Client Model

@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "tbl_clients")
public class Client {
    @Id
    private String ClientID;
    private String ClientNames;
    private String NickName;
    private String Gender;
    private String Country;
    private String Email;
    private String Phone;
    
    @OneToMany(targetEntity = Order.class, cascade = CascadeType.ALL)
    @JoinColumn(name = "ClientID", referencedColumnName = "ClientID")
    private List<Order> order;
    
    @OneToMany(targetEntity = Status.class, cascade = CascadeType.ALL)
    @JoinColumn(name = "ClientID", referencedColumnName = "ClientID")
    private List<Status> status;
}

// Orders Model

@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "tbl_orders")
public class Order {
   @Id
   private String OrderId;
   private LocalDateTime OrderDate;
   private String OrderType;
   private String Code;
   private Int Quantity;
   private String OrderLocation;
   private String ClientID;
  
   @JsonIgnore
   @ManyToOne
   @JoinColumn(name = "ClientID", insertable=false, updatable=false)
   private Client client;
}

// Status Model

@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "tbl_status")
public class Status {
   @Id
   private String StatusId;
   private LocalDateTime StatusDate;
   private String OrderStatus;
   private String Notes;
   private String ClientID;
  
   @JsonIgnore
   @ManyToOne
   @JoinColumn(name = "ClientID", insertable=false, updatable=false)
   private Client client;
}

Any Suggestions will be much appreciated just to speed up my rest api to pull data faster.