0

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

  • Is it a read only database, or it grows daily? – McNets Nov 17 '17 at 11:44
  • 1
    Table bloat? There are some good scripts for detection at github.com/pgexperts/pgx_scripts/tree/master/bloat. If that proves to be the case, a CLUSTER of the affected table should fix the query performance issues. – bma Nov 17 '17 at 14:18
  • Seq scan on 30Gb server has almost 5 times as many rows as on 64Gb server (according to explain). Are you sure that there are equally many rows on both servers? – Lennart Nov 17 '17 at 15:33
  • @Lennart, the two tables show a similar numbers of rows. See the second rows= part, after actual. The first rows= numbers are estimates. – bma Nov 17 '17 at 15:39
  • @McNets, it grows daily – user2274074 Nov 22 '17 at 12:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.