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