Início / user-287676

ankush chauhan's questions

Martin Hope
ankush chauhan
Asked: 2024-02-28 21:21:29 +0800 CST
  • 4

Consulta:

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

Detalhes do índice:

 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)

Detalhes da tabela:

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

Problema:


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

Plano de execução:


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
postgresql
Martin Hope
ankush chauhan
Asked: 2024-02-27 18:58:03 +0800 CST
  • 6

Consulta:

with firstData as (
   select r.display_name as counter,
          r.createdat,
          r.status,
          r.id as runid
   from run r
      inner join customer c on c.id = r.badmasterid
      inner join bank b on b.id = c.registerid
   where b.displayname in ('abc', 'pqr')
   order by r.createdat desc limit 100
),
secondData as (
   select fi.runid,
          count(1) filter (where action = 'Add' and type = 'Bank') as addcount,
          count(1) filter (where action = 'Modify' and type = 'Bank') as modifycount,
          count(1) filter (where action = 'Delete' and type = 'Bank') as deletecount,
          count(1) filter (where action is null and type = 'Bank') as pendingcount,
          count(1) filter (where (status = 'FAILED' or status = 'FAILED') and type = 'Bank') as failcount
   from bigTable fi
   where fi.runid in
      (select runid from firstData limit 100)
   group by fi.runid
)
select r1.*,
       case when c1.add_count is null then 0 else c1.add_count end,
       case when c1.modify_count is null then 0 else c1.modify_count end,
       case when c1.delete_count is null then 0 else c1.delete_count end,
       case when c1.pending_count is null then 0 else c1.pending_count end,
       case when c1.fail_count is null then 0 else c1.fail_count end
from firstData r1
   left join secondData c1 on r1.runid = c1.runid
order by r1.createdat;

**Indexs on Table:**
run : id column
customer : id column
bank : id column
bigTable : (id, status, type, runid) columns

Plano de execução:

Sort  (cost=839936.19..839939.32 rows=1250 width=116) (actual time=7838.265..7838.275 rows=100 loops=1)
  Sort Key: r1.createdat
  Sort Method: quicksort  Memory: 34kB
  CTE result
    ->  Limit  (cost=79.27..79.52 rows=100 width=52) (actual time=1.601..1.615 rows=100 loops=1)
          ->  Sort  (cost=79.27..79.90 rows=250 width=52) (actual time=1.600..1.607 rows=100 loops=1)
                Sort Key: br.createdat DESC
                Sort Method: top-N heapsort  Memory: 35kB
                ->  Hash Join  (cost=2.84..69.72 rows=250 width=52) (actual time=0.101..1.083 rows=2500 loops=1)
                      Hash Cond: (r.runid = b.id)
                      ->  Seq Scan on run r  (cost=0.00..55.00 rows=2500 width=28) (actual time=0.029..0.213 rows=2500 loops=1)
                      ->  Hash  (cost=2.79..2.79 rows=4 width=36) (actual time=0.066..0.068 rows=40 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 10kB
                            ->  Hash Join  (cost=1.27..2.79 rows=4 width=36) (actual time=0.046..0.061 rows=40 loops=1)
                                  Hash Cond: (b.counterid = rm.id)
                                  ->  Seq Scan on bank b  (cost=0.00..1.40 rows=40 width=12) (actual time=0.011..0.014 rows=40 loops=1)
                                  ->  Hash  (cost=1.25..1.25 rows=2 width=36) (actual time=0.026..0.027 rows=20 loops=1)
                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                        ->  Seq Scan on customer c  (cost=0.00..1.25 rows=2 width=36) (actual time=0.012..0.018 rows=20 loops=1)
                                              Filter: (display_name = ANY ('{abc,pqr}'::text[]))
  ->  Hash Right Join  (cost=839720.50..839792.38 rows=1250 width=116) (actual time=7838.151..7838.230 rows=100 loops=1)
        Hash Cond: (fi.runid = r1.runid)
        ->  HashAggregate  (cost=839717.25..839742.25 rows=2500 width=48) (actual time=7836.481..7836.521 rows=100 loops=1)
              Group Key: fi.runid
              Batches: 1  Memory Usage: 145kB
              ->  Hash Semi Join  (cost=3.25..810917.25 rows=720000 width=31) (actual time=615.639..7395.089 rows=719888 loops=1)
                    Hash Cond: (fi.runid = firstData.runid)
                    ->  Seq Scan on bigTable fi  (cost=0.00..755654.00 rows=18000000 width=31) (actual time=20.792..5093.711 rows=18000000 loops=1)
                    ->  Hash  (cost=2.00..2.00 rows=100 width=4) (actual time=0.046..0.047 rows=100 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 12kB
                          ->  Limit  (cost=0.00..2.00 rows=100 width=4) (actual time=0.004..0.023 rows=100 loops=1)
                                ->  CTE Scan on firstData   (cost=0.00..2.00 rows=100 width=4) (actual time=0.003..0.017 rows=100 loops=1)
        ->  Hash  (cost=2.00..2.00 rows=100 width=76) (actual time=1.660..1.661 rows=100 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 15kB
              ->  CTE Scan on firstData r1  (cost=0.00..2.00 rows=100 width=76) (actual time=1.603..1.630 rows=100 loops=1)

Planning Time: 4.387 ms
Execution Time: 7838.458 ms
postgresql