I have postgresql server install on two different servers. one server RAM is 30GB and another server RAM is 64 GB. i am executing below simple query.

SELECT  id,         hour,         dow,         count(r_e_id) AS volume FROM test GROUP BY id,hour,dow 

Server having 64GB ram is executing above query in 1.5 seconds where as server with 30 GB ram is taking 7 seconds. There are total 2.3 million records on both the servers. This query was used to take 1.5 seconds on 30 GB server 3 to 4 months ago but it is now taking 7 seconds. i have vaccume the table but still taking 7 seconds.

Below is the explain analyze output of 64GB RAM server for the above query

   "HashAggregate  (cost=122240.58..122348.10 rows=10752 width=16) (actual time=1482.720..1483.911 rows=5726 loops=1)" "  Group Key: id, hour, dow" "  ->  Seq Scan on test  (cost=0.00..98330.79 rows=2390979 width=16) (actual time=1.210..290.304 rows=2390970 loops=1)" "Planning time: 0.184 ms" "Execution time: 1484.253 ms" 

Below is the explain analyze output of 30GB RAM server for the above query

"HashAggregate  (cost=3208512.50..3208616.66 rows=10416 width=16) (actual time=8009.038..8011.166 rows=5615 loops=1)" "  Group Key: id, hour, dow" "  ->  Seq Scan on test  (cost=0.00..3101655.75 rows=10685675 width=16) (actual time=2.139..6780.604 rows=2296651 loops=1)" "Planning time: 0.126 ms" "Execution time: 8011.987 ms" 

Why my query is taking more time on 30GB Ram server. it was not taking more time two months before but i don’t know what went wrong suddenly.

What could be the reason for the slow execution of the query

Thanks