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