How to optimize PostgreSQL Query Performance for Large Datasets with Pagination Requirements

Question

Hi I'm using Spring Boot application and PostgreSql database and I have three tables. Two tables serve as data sources, holding records of my actual transactions, The third table functions as my output table, where I store the reconciliation output derived from the data in the two aforementioned tables.

My output table structure

CREATE TABLE output_table (
  id int4 NOT NULL,
  trxn_a_ids text,
  trxn_b_ids text,
  account_id int4,
  status varchar(100),
  date date
);

output table data sample

where trxn_a_ids or trxn_b_ids can contain 1 or multiple ids of data source tables which are reconciled depend upon transaction relationship 1:1, 1:M, M:1 or M:M. In normal flow I'm picking results from output table based on any date, account_id and applying pagination. then I'm fetching actual transactions from data source tables using trxn_a_ids and trxn_b_ids of output table as API response.

Now I need to use like operator on data sources columns. In current implementation it's not sure which record will be result of my like operator condition, I can't identify the page number of output table where ids of resulted data source are held.

I have tried fetch all data of output table and data source tables to implement pagination in java but that is not good approach because on every API hit query will get millions of records and then filter data from those records.

I formulated a PostgreSQL query to address this concern, and I'm sharing it; however, it seems that its performance might degrade with a sizable dataset. I would appreciate suggestions to enhance its efficiency, especially considering the need to maintain pagination.

SELECT outputTable.*
FROM output_table outputTable
WHERE outputTable.date = '2023-01-12' 
  AND outputTable.account_id = '0001'
    
  AND (coalesce(outputTable.trxn_a_ids, '')='' OR string_to_array(outputTable.trxn_a_ids, ',')::int[] <@ ARRAY(SELECT id FROM dsA WHERE date = '2023-01-12' and account_id='0001'
 and (columnA like '%3200404%' or columnB like '%3200404%')))
 
      AND (coalesce(outputTable.trxn_b_ids, '')='' OR string_to_array(outputTable.trxn_b_ids, ',')::int[] <@ ARRAY( SELECT id FROM dsB WHERE date = '2023-01-12' and account_id='0001'
 and (columnA like '%3200404%' or columnB like '%3200404%')))
 
 offset 0 limit 10;