Re: CPU Consuming query. Sequential scan despite indexing.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,
Kindly requesting for help on this. Thanks.

-Aditya.



On Tue, Oct 20, 2020 at 6:00 PM aditya desai <admad123@xxxxxxxxx> wrote:
Hi Laurenz,
I created

On Fri, Oct 16, 2020 at 2:06 PM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote:
On Thu, 2020-10-15 at 20:34 +0530, aditya desai wrote:
> Below query always shows up on top in the CPU matrix. Also despite having indexes it does sequential scans
> (probably because WHERE condition satisfies almost all of the data from table). This query
> runs on the default landing page in application and needs to fetch records in less that 100 ms
>  without consuming too much CPU.
>
>  Any opinions? Table is very huge and due to referential identity and business requirements we could not
>  implement partitioning as well.
>
> There is index on (countrycode,facilitycode,jobstartdatetime)
>
> explain (analyze,buffers) with JobCount as ( select jobstatuscode,count(1) stat_count from job j
>  where 1=1 and j.countrycode = 'TH'
> and j.facilitycode in ('THNPM1','THPRK1','THCNT1','THSPN1','THKRI1','THPKN1','THSBI1','THUTG1','THLRI1','THSRI1','THSUR1','THSKM1')
>  and ((j.jobstartdatetime  between '2020-08-01 00:00:00' and '2020-09-30 00:00:00' ) or j.jobstartdatetime IS NULL )  group by j.jobstatuscode)
>  select js.jobstatuscode,COALESCE(stat_count,0) stat_count from JobCount jc right outer join jobstatus js on jc.jobstatuscode=js.jobstatuscode;
>
>                           QUERY PLAN
>
>  Hash Right Join  (cost=98845.93..98846.10 rows=10 width=12) (actual time=1314.809..1314.849 rows=10 loops=1)
>                              ->  Parallel Seq Scan on job j  (cost=0.00..96837.93 rows=200963 width=4) (actual time=13.010..1144.434 rows=163200 loops=3)
>                                    Filter: (((countrycode)::text = 'TH'::text) AND (((jobstartdatetime >= '2020-08-01 00:00:00'::timestamp without time zone) AND (jobst
> artdatetime <= '2020-09-30 00:00:00'::timestamp without time zone)) OR (jobstartdatetime IS NULL)) AND ((facilitycode)::text = ANY ('{THNPM1,THPRK1,THCNT1,THSPN1,THKRI1
> ,THPKN1,THSBI1,THUTG1,THLRI1,THSRI1,THSUR1,THSKM1}'::text[])))
>                                    Rows Removed by Filter: 449035
>                                    Buffers: shared hit=60086 read=11834
>                                    I/O Timings: read=59.194
>

You should rewrite the subquery as a UNION to avoid the OR:

  ... WHERE j.countrycode = 'TH'
        and j.facilitycode in ('THNPM1','THPRK1','THCNT1','THSPN1','THKRI1','THPKN1','THSBI1','THUTG1','THLRI1','THSRI1','THSUR1','THSKM1')
        and j.jobstartdatetime between '2020-08-01 00:00:00' and '2020-09-30 00:00:00'

and

  ... WHERE j.countrycode = 'TH'
        and j.facilitycode in ('THNPM1','THPRK1','THCNT1','THSPN1','THKRI1','THPKN1','THSBI1','THUTG1','THLRI1','THSRI1','THSUR1','THSKM1')
        and j.jobstartdatetime IS NULL

These indexes could speed up the resulting query:

  CREATE INDEX ON job (countrycode, facilitycode);
  CREATE INDEX ON job (countrycode, jobstartdatetime);
  CREATE INDEX ON job (countrycode, facilitycode) WHERE jobstartdaytime IS NULL;

I created the indexes you suggested and changed the query with the UNION operator. Please see explain plan below. Performance of the query(execution time has improved mostly because I ran vacuum full). Cost of the query is still high.This is Dev envrionment and has 2 vCPU and 8 GB RAM.

explain (analyze,buffers) with JobCount as ( (select jobstatuscode,count(1) stat_count from job j where 1=1 and j.countrycode = 'TH'   and j.facilitycode in ('THNPM1','THPRK1','THCNT1','THSPN1','THKRI1','THPKN1','THSBI1','THUTG1','THLRI1','THSRI1','THSUR1','THSKM1') and j.jobstartdatetime  between '2020-08-01 00:00:00' and '2020-09-30 00:00:00'    group by j.jobstatuscode) UNION (select jobstatuscode,count(1) stat_count from job j where 1=1 and j.countrycode = 'TH'   and j.facilitycode in ('THNPM1','THPRK1','THCNT1','THSPN1','THKRI1','THPKN1','THSBI1','THUTG1','THLRI1','THSRI1','THSUR1','THSKM1') and j.jobstartdatetime is null  group by j.jobstatuscode))
lmp_delivery_jobs->  select js.jobstatuscode,COALESCE(stat_count,0) stat_count from JobCount jc right outer join jobstatus js on jc.jobstatuscode=js.jobstatuscode;
                                                                                                               QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------
 Hash Right Join  (cost=79010.89..79011.19 rows=10 width=12) (actual time=444.241..444.256 rows=10 loops=1)
   Hash Cond: ((jc.jobstatuscode)::text = (js.jobstatuscode)::text)
   Buffers: shared hit=8560
   CTE jobcount
     ->  HashAggregate  (cost=79002.35..79002.48 rows=13 width=24) (actual time=444.211..444.213 rows=6 loops=1)
           Group Key: j.jobstatuscode, (count(1))
           Buffers: shared hit=8558
           ->  Append  (cost=78959.64..79002.28 rows=13 width=24) (actual time=444.081..444.202 rows=6 loops=1)
                 Buffers: shared hit=8558
                 ->  Finalize GroupAggregate  (cost=78959.64..78961.41 rows=7 width=12) (actual time=444.079..444.101 rows=6 loops=1)
                       Group Key: j.jobstatuscode
                       Buffers: shared hit=8546
                       ->  Gather Merge  (cost=78959.64..78961.27 rows=14 width=12) (actual time=444.063..444.526 rows=18 loops=1)
                             Workers Planned: 2
                             Workers Launched: 2
                             Buffers: shared hit=17636
                             ->  Sort  (cost=77959.61..77959.63 rows=7 width=12) (actual time=435.748..435.750 rows=6 loops=3)
                                   Sort Key: j.jobstatuscode
                                   Sort Method: quicksort  Memory: 25kB
                                   Worker 0:  Sort Method: quicksort  Memory: 25kB
                                   Worker 1:  Sort Method: quicksort  Memory: 25kB
                                   Buffers: shared hit=17636
                                   ->  Partial HashAggregate  (cost=77959.44..77959.51 rows=7 width=12) (actual time=435.703..435.706 rows=6 loops=3)
                                         Group Key: j.jobstatuscode
                                         Buffers: shared hit=17620
                                         ->  Parallel Bitmap Heap Scan on job j  (cost=11528.22..76957.69 rows=200351 width=4) (actual time=47.682..281.928 rows=163200
loops=3)
                                               Recheck Cond: (((countrycode)::text = 'TH'::text) AND ((facilitycode)::text = ANY ('{THNPM1,THPRK1,THCNT1,THSPN1,THKRI1,T
HPKN1,THSBI1,THUTG1,THLRI1,THSRI1,THSUR1,THSKM1}'::text[])))
                                               Filter: ((jobstartdatetime >= '2020-08-01 00:00:00'::timestamp without time zone) AND (jobstartdatetime <= '2020-09-30 00
:00:00'::timestamp without time zone))
                                               Heap Blocks: exact=6633
                                               Buffers: shared hit=17620
                                               ->  Bitmap Index Scan on job_list_test1  (cost=0.00..11408.01 rows=482693 width=0) (actual time=49.825..49.826 rows=48960
0 loops=1)
                                                     Index Cond: (((countrycode)::text = 'TH'::text) AND ((facilitycode)::text = ANY ('{THNPM1,THPRK1,THCNT1,THSPN1,THKR
I1,THPKN1,THSBI1,THUTG1,THLRI1,THSRI1,THSUR1,THSKM1}'::text[])))
                                                     Buffers: shared hit=1913
                 ->  GroupAggregate  (cost=40.50..40.68 rows=6 width=12) (actual time=0.093..0.094 rows=0 loops=1)
                       Group Key: j_1.jobstatuscode
                       Buffers: shared hit=12
                       ->  Sort  (cost=40.50..40.54 rows=16 width=4) (actual time=0.092..0.092 rows=0 loops=1)
                             Sort Key: j_1.jobstatuscode
                             Sort Method: quicksort  Memory: 25kB
                             Buffers: shared hit=12
                             ->  Index Scan using job_list_test3 on job j_1  (cost=0.14..40.18 rows=16 width=4) (actual time=0.081..0.082 rows=0 loops=1)
                                   Index Cond: (((countrycode)::text = 'TH'::text) AND ((facilitycode)::text = ANY ('{THNPM1,THPRK1,THCNT1,THSPN1,THKRI1,THPKN1,THSBI1,T
HUTG1,THLRI1,THSRI1,THSUR1,THSKM1}'::text[])))
                                   Buffers: shared hit=12
   ->  CTE Scan on jobcount jc  (cost=0.00..0.26 rows=13 width=24) (actual time=444.215..444.221 rows=6 loops=1)
         Buffers: shared hit=8558
   ->  Hash  (cost=8.29..8.29 rows=10 width=4) (actual time=0.016..0.016 rows=10 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: shared hit=2
         ->  Index Only Scan using jobstatus_jobstatuscode_unq on jobstatus js  (cost=0.14..8.29 rows=10 width=4) (actual time=0.006..0.010 rows=10 loops=1)
               Heap Fetches: 0
               Buffers: shared hit=2
 Planning Time: 0.808 ms
 Execution Time: 444.819 ms
(53 rows)


 

Yours,

Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux