Postgresql – Same postgres query in two different instances with the same data but with different times

amazon-rdsgoogle-cloud-platformpostgresqlpostgresql-11

I have the same postgres query running in two different instances restored with the same dump file:

  1. one instance in aws rds => https://explain.depesz.com/s/USMO ('PostgreSQL 11.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit')
  2. one instance in compute engine vm in gcp => https://explain.depesz.com/s/LTUL ('PostgreSQL 11.10 (Debian 11.10-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit')

But the query in 1) (40s) is faster than 2) (400s) (execution time calculated in python)

What I tried:

  • ran without cache (restart the on prem gcp instance)
  • changed the where clause values
  • ran the query in different computers
  • analyze the basics of explain command (same plan and same indexes)

What are the main reasons for this?

My main hypothesis now is the network traffic. How can I test that?

Thanks in advance

  • both have the same postgres version and hardware configuration
  • I am not sure, but these times are the first run of each query and cache (maybe) is not involved
  • traceroute 1) = 13.864 ms / traceroute 2) = 32.469 ms

Best Answer

The difference is shared hits.

Shared hit means it read the blocks from ram (shared buffers).

Check for shared_buffers parameter on both servers.