Re: Need help on Query Tunning and Not using the Index Scan

On Fri, 2022-05-20 at 07:37 +0000, Kumar, Mukesh wrote:
> We are facing an issue in running the query which takes at least 30 sec to run in PostgreSQL.
>  
> We have tried to create the indexes and done the maintenance and still that query is taking same time.
>  
> Below are the explain plan for the query.
>  
> https://explain.depesz.com/s/sPo2#html
>  
> We have noticed that maximum time it is takin is do a Seq Scan on Table ps_delay_statistic which consist of approx.
35344812records .
 
>  
> Can anyone please help on the above issue.

The problem is probably here:

->  GroupAggregate  (cost=0.57..18153.25 rows=2052 width=23) (actual time=13.764..13.765 rows=1 loops=1)
      Group Key: ds_1.fleet_object_number_f"
      ->  Index Scan using ndx_delay_stat_equipment on ps_delay_statistic ds_1  (cost=0.57..18050.67 rows=16412
width=23)(actual time=0.026..10.991 rows=18180 loops=1)
 
            Index Cond: (fleet_object_number_f = (COALESCE(NULLIF('4000100000000000277313'::text,
''::text)))::numeric)
            Filter: (activity_code_f IS NOT NULL)

which comes from this subquery:

SELECT max(dp1.daily_production_id) prodId
 FROM ps_daily_production_v dp1
WHERE dp1.fleet_object_number = cast(coalesce(nullif (cast(4000100000000000277313 AS varchar), ''), NULL) AS numeric)
  AND dp1.activity_code IS NOT NULL
GROUP BY dp1.fleet_object_number

Remove the superfluous GROUP BY clause that confuses the optimizer.

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