Thread: Need help on Query Tunning and Not using the Index Scan
Hi Team,
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.
Thanks and Regards,
Mukesh Kumar
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