Performance issue with production database

[michalnawrockidj]

Service name
calculate-journey-variable-payments-prod

Impact on the service
504 Gateway Timeout on production services

Problem description

Hi I need to ask to investigate the performance issue for production database 'db33ac10144c48c3ea'
When we use a query select count(js.journey) as journey_count, sum(js.price_in_pence) as total_price_in_pence, sum (js.volume_unlocationed) as count_without_locations, sum(js.volume_unpriced) as count_unpriced from( select distinct CONCAT(j.from_nomis_agency_id, ' ', j.to_nomis_agency_id) as journey, max(case when jfl.location_id is null or jtl.location_id is null then 1 else 0 end) as volume_unlocationed, sum(case when j.billable then COALESCE(pe.price_in_pence, p.price_in_pence) else 0 end) as price_in_pence, max(case when p.price_in_pence is null then 1 else 0 end) as volume_unpriced from MOVES m inner join JOURNEYS j on j.move_id = m.move_id left join LOCATIONS jfl on j.from_nomis_agency_id = jfl.nomis_agency_id left join LOCATIONS jtl on j.to_nomis_agency_id = jtl.nomis_agency_id left join PRICES p on jfl.location_id = p.from_location_id and jtl.location_id = p.to_location_id and j.effective_year = p.effective_year and p.supplier = 'SERCO' left join PRICE_EXCEPTIONS pe on p.price_id = pe.price_id and pe.month = 1 where m.move_month = 1 and m.move_year = 2025 and m.supplier = 'SERCO' and m.move_type is not null and m.drop_off_or_cancelled is not null GROUP BY journey ) as js)

Execution Time for this query is around: 53692.466 ms
Comparing to the preprod database 'db399f96f0fefe2b16' where we copied the data from production. The execution time is around 3518.202 ms what is 15 times faster.
We have couple of queries like that and as a result end-user experience a lot of timeout waiting for the data.
I attached output of explain (analyze,buffers) from preprod and prod below

preprod_results.json

prod_results.json

[michalnawrockidj]

Running
VACUUM (VERBOSE, ANALYZE) JOURNEYS; VACUUM (VERBOSE, ANALYZE) MOVES; VACUUM (VERBOSE, ANALYZE) LOCATIONS; VACUUM (VERBOSE, ANALYZE) PRICES; VACUUM (VERBOSE, ANALYZE) PRICE_EXCEPTIONS; on production sorted the issue
Is it any way we can do it automatically on all Postgres DB - https://www.postgresql.org/docs/current/routine-vacuuming.html#:~:text=PostgreSQL%20has%20an%20optional%20but,inserted%2C%20updated%20or%20deleted%20tuples.