How to optimize query where we are using transaction slab?-CodePudding

I need to generate an aging report as per transaction slab i.e. on the interval of 2, 5, 10, and so on. Users will select a report start date and end date and then we need to generate a report for that particular date range. The report should be in such a way that transactions should be divided in slabs; like 0-2, 3-4, 5-6, 6-8, and greater than 8 days. I wrote a subquery for each slab and it gave me the required data. But since the data increases on daily basis it is taking too much time. I tried indexing as well, but it is not improving performance much.

select id_ledger as ledger_id,
       customer_id,
       title     as ledger,
       (
           select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
           from journal_voucher_details
           where cr_amount = 0
             and (('2021-10-23' - transaction_date) <= (2))
             and ledger_id = l.customer_id
             and transaction_date >= '2021-10-01'
             and transaction_date <= '2021-10-24'
           group by ledger_id
       )         as slab1,
       (
           select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
           from journal_voucher_details
           where cr_amount = 0
             and (('2021-10-23' - transaction_date) <= (2 * 2))
             and (('2021-10-23' - transaction_date) > (2 * 1))
             and ledger_id = l.customer_id
             and transaction_date >= '2021-10-01'
             and transaction_date <= '2021-10-24'
           group by ledger_id
       )         as slab2,
       (
           select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
           from journal_voucher_details
           where cr_amount = 0
             and (('2021-10-23' - transaction_date) <= (2 * 3))
             and (('2021-10-23' - transaction_date) > (2 * 2))
             and ledger_id = l.customer_id
             and transaction_date >= '2021-10-01'
             and transaction_date <= '2021-10-24'
           group by ledger_id
       )         as slab3,
       (
           select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
           from journal_voucher_details
           where cr_amount = 0
             and (('2021-10-23' - transaction_date) <= (2 * 4))
             and (('2021-10-23' - transaction_date) > (2 * 3))
             and ledger_id = l.customer_id
             and transaction_date >= '2021-10-01'
             and transaction_date <= '2021-10-24'
           group by ledger_id
       )         as slab4,
       (
           select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
           from journal_voucher_details
           where cr_amount = 0
             and (('2021-10-23' - transaction_date) > (2 * 4))
             and ledger_id = l.customer_id
             and transaction_date >= '2021-10-01'
             and transaction_date <= '2021-10-24'
           group by ledger_id
       )         as slab5,
       (
           select COALESCE((sum(dr_amount) - sum(clearance_amount)), 0) as diff_value
           from journal_voucher_details
           where cr_amount = 0
             and 1 = 1
             and ledger_id = l.customer_id
             and transaction_date >= '2021-10-01'
             and transaction_date <= '2021-10-24'
           group by ledger_id
       )         as balance
from ledgers l;

NB: 2021-10-1 ==> start date

2021-10-24 ==> end date

2021-10-23 ==> report date

and 2 is selected as slab number.

How can I improve the performance of this query?

Thanks in advance.

CodePudding user response：

You can try this, should be quicker :

select id_ledger as ledger_id, customer_id, title as ledger,
     , COALESCE( sum(dr_amount) FILTER (WHERE ('2021-10-23' - transaction_date) <= (2)) OVER () 
                 - sum(clearance_amount) FILTER (WHERE ('2021-10-23' - transaction_date) <= (2)) OVER ()
               , 0
               ) as slab1
     , COALESCE(sum(dr_amount) FILTER (where ('2021-10-23' - transaction_date) <= (2 * 2) and ('2021-10-23' - transaction_date) > (2 * 1)) OVER () 
                - sum(clearance_amount) FILTER (where ('2021-10-23' - transaction_date) <= (2 * 2) and ('2021-10-23' - transaction_date) > (2 * 1)) OVER () 
               , 0
               ) as slab2
     , COALESCE(sum(dr_amount) FILTER (where ('2021-10-23' - transaction_date) <= (2 * 3) and ('2021-10-23' - transaction_date) > (2 * 2)) OVER () 
                 - sum(clearance_amount) FILTER (where ('2021-10-23' - transaction_date) <= (2 * 3) and ('2021-10-23' - transaction_date) > (2 * 2)) OVER () 
               , 0
               ) as slab3
     , COALESCE(sum(dr_amount) FILTER (where ('2021-10-23' - transaction_date) <= (2 * 4) and ('2021-10-23' - transaction_date) > (2 * 3)) OVER () 
                 - sum(clearance_amount) FILTER (where ('2021-10-23' - transaction_date) <= (2 * 4) and ('2021-10-23' - transaction_date) > (2 * 3)) OVER () 
               , 0
               ) as slab4
     , COALESCE(sum(dr_amount) FILTER (where ('2021-10-23' - transaction_date) > (2 * 4)) OVER ()
                - sum(clearance_amount) FILTER (where ('2021-10-23' - transaction_date) > (2 * 4)) OVER ()
               , 0
               ) as slab5
     , COALESCE(sum(dr_amount) - sum(clearance_amount), 0) AS balance
from ledgers l
inner join journal_voucher_details j
on j.ledger_id = l.customer_id
where cr_amount = 0
and transaction_date >= '2021-10-01'
and transaction_date <= '2021-10-24'
group by j.ledger_id