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. 35344812 records . > > 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
