-1

Query:

with firstData as
(select rm.display_name as register, br.createdat, br.status, br.id as runid
from bad_run br 
inner join bad_master bm on bm.id = br.badmasterid
inner join regis_master rm on rm.id = bm.registerid
where rm.display_name in ('abc', 'pqr')
order by br.createdat desc limit 100)

select fi.runid,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action = 'Add'  and type = 'BANK') as add,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action = 'Modify' and type = 'BANK') as modify,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action = 'Delete' and type = 'BANK') as delete,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action is null and type = 'BANK') as pending,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND (status = 'FAILED' or status = 'Failed') and type = 'BANK') as fail
from firstData fi 

Index Details:

 INDEX composite_1_idx ON public.bigTable USING btree (runid, action, type) WHERE (runid IS NOT NULL)

 INDEX composite_2_idx ON public.bigTable USING btree (runid, status, type) WHERE (runid IS NOT NULL)

 INDEX runid_idx ON public.bigTable USING btree (runid) WHERE (runid IS NOT NULL)

Table Details:

TABLE bigTable (
    id SERIAL PRIMARY KEY,
    createdAt TIMESTAMPTZ,
    registerId BIGINT REFERENCES bank(id),
    status TEXT,
    runid BIGINT REFERENCES customer(id)
)

Problem:


It supposed to use index only scan for all scenario (Pending, Add, failed) 

It is only using index only scan for (delete, modify)   

portion of Query:
select fi.runid,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action = 'Add'  and type = 'BANK') as add,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action = 'Modify' and type = 'BANK') as modify,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action = 'Delete' and type = 'BANK') as delete,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action is null and type = 'BANK') as pending,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND (status = 'FAILED' or status = 'Failed') and type = 'BANK') as fail
from firstData fi 
    

Execution Plan:


Subquery Scan on fi  (cost=79.27..21812.52 rows=100 width=44) (actual time=3.117..111.762 rows=100 loops=1)
  ->  Limit  (cost=79.27..79.52 rows=100 width=76) (actual time=2.119..2.194 rows=100 loops=1)
        ->  Sort  (cost=79.27..79.90 rows=250 width=76) (actual time=2.118..2.170 rows=100 loops=1)
              Sort Key: br.createdat DESC
              Sort Method: top-N heapsort  Memory: 30kB
              ->  Hash Join  (cost=2.84..69.72 rows=250 width=76) (actual time=0.093..1.630 rows=2500 loops=1)
                    Hash Cond: (br.badmasterid = bm.id)
                    ->  Seq Scan on bad_run br  (cost=0.00..55.00 rows=2500 width=20) (actual time=0.017..0.253 rows=2500 loops=1)
                    ->  Hash  (cost=2.79..2.79 rows=4 width=4) (actual time=0.063..0.065 rows=40 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 10kB
                          ->  Hash Join  (cost=1.27..2.79 rows=4 width=4) (actual time=0.045..0.058 rows=40 loops=1)
                                Hash Cond: (bm.registerid = rm.id)
                                ->  Seq Scan on bad_master bm  (cost=0.00..1.40 rows=40 width=12) (actual time=0.009..0.012 rows=40 loops=1)
                                ->  Hash  (cost=1.25..1.25 rows=2 width=4) (actual time=0.023..0.024 rows=20 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                      ->  Seq Scan on regis_master rm  (cost=0.00..1.25 rows=2 width=4) (actual time=0.011..0.016 rows=20 loops=1)
                                            Filter: (display_name = ANY ('{abc,pqr}'::text[]))
  SubPlan 1
    ->  Aggregate  (cost=11549..11549.60 rows=1 width=8) (actual time=38.433..38.43 rows=1 loops=100)
          ->  Index Scan using runid_idx on bigTable b  (cost=0.56..11475.63 rows=29585 width=0) (actual time=0.008..24.212 rows=17888 loops=100)
                Index Cond: ((runid = fi.runid))
                Filter ((type='BANK'::text) AND (action'Add'::text))
                Rows Removed by Filter: 17428
  SubPlan 2
    ->  Aggregate  (cost=19.86..19.87 rows=1 width=8) (actual time=0.120..0.120 rows=1 loops=100)
          ->  Index Only Scan using composite_1_idx on bigTable b_1  (cost=0.56..18.33 rows=612 width=0) (actual time=0.012..0.081 rows=598 loops=100)
                Index Cond: ((runid = fi.runid) AND (action = 'Modify'::text) AND (type = 'BANK'::text))
                Heap Fetches: 0
  SubPlan 3
    ->  Aggregate  (cost=19.61..19.62 rows=1 width=8) (actual time=0.122..0.122 rows=1 loops=100)
          ->  Index Only Scan using composite_1_idx on bigTable b_2  (cost=0.56..18.11 rows=602 width=0) (actual time=0.011..0.082 rows=603 loops=100)
                Index Cond: ((runid = fi.runid) AND (action = 'Delete'::text) AND (type = 'BANK'::text))
                Heap Fetches: 0
  SubPlan 4
    ->  Aggregate  (cost=11549..11549.60 rows=1 width=8) (actual time=38.433..38.43 rows=1 loops=100)
          ->  Index Scan using runid_idx on bigTable b  (cost=0.56..11475.63 rows=29585 width=0) (actual time=0.008..24.212 rows=17888 loops=100)
                  Index Cond: ((runid = fi.runid))
                  Filter ((action is null)AND (type='BANK'::text) )
                  Rows Removed by Filter: 1
  SubPlan 5
    ->  Aggregate  (cost=11549..11549.60 rows=1 width=8) (actual time=38.433..38.43 rows=1 loops=100)
          ->  Index Scan using runid_idx on bigTable b_4  (cost=0.56..11475.63 rows=29585 width=0) (actual time=0.008..24.212 rows=17888 loops=100)
                Index Cond: ((runid = fi.runid))
                Filter: (type='BANK'::text) AND ((status = 'FAILED'::text) OR (status = 'Failed'::text)))
                Rows Removed by Filter: 12730
Planning Time: 2.223 ms
Execution Time: 12282.836 ms              

2
  • I don't understand this question; there is too little text in it. Also, the execution plan is incomplete. Feb 28 at 13:33
  • @LaurenzAlbe updated the Execution plan. Feb 28 at 13:51

1 Answer 1

0

The subqueries in the SELECT list are what is expensive; they are executed for each result row.

Use the power of filtered aggregates:

SELECT fi.runid,
       count(*) FILTER (WHERE action = 'Add' and type = 'BANK') AS add,
       count(*) FILTER (WHERE action = 'Modify' and type = 'BANK') AS modify,
       count(*) FILTER (WHERE action = 'Delete' and type = 'BANK') AS modify,
       count(*) FILTER (WHERE status IN ('FAILED', 'Failed') and type = 'BANK') AS fail,
from firstData fi
   LEFT JOIN bigTable b ON b.runid=fi.runid;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.