Skip to main content
Database Administrators

Return to Question

Bumped by Community user
denoise
Source Link
Erwin Brandstetter
Erwin Brandstetter
  • 182.1k
  • 28
  • 457
  • 620

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

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

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?

  • 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
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Added Postgres version
Link
user212533
user212533
Source Link
Igor Gois
Igor Gois
  • 1
  • 1

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

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