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
  • Besides the configuration issue, row estimates are wrong in one of the queries. Did you run ANALYZE? – bobflux Feb 24 at 16:03
  • @bobflux, yes. the query is: EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS) SELECT "Id", "DateTime", "SignalRegisterId", "Raw" FROM "SignalRecordsBlobs" WHERE "SignalSettingId" = 103 AND "DateTime" BETWEEN '2019-11-28T14:00:12.540200000' AND '2020-07-23T21:12:32.249000000'; – Igor Gois Feb 25 at 16:05

The difference is shared hits.

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

Check for shared_buffers parameter on both servers.

  • you were right. The shared_buffers were different. I changed and tested with the same shared_buffers, min_wal_size and max_wal_size but the results were the same. Any idea how to test the network traffic? It turns out that this query returns a big size byte array. Thanks for your time – Igor Gois Feb 24 at 14:56
  • What was your shared_buffer setting? You should also set effective_cache_size correctly. Run the query twice. Second time should be faster. Also check work_mem parameter too. – Burak Yurdakul Feb 24 at 15:51
  • the shared_buffer was 128 MB and now is 950 MB. The second time the execution time was 100 ms faster, but the whole execution (run query, download data, show results) in the client was the same. The work_mem is 2.5 MB is aws and is commented in the gcp (#work_mem = 4MB) – Igor Gois Feb 25 at 16:15
  • If you have access to server run the query there and check for time. If not create a table for the result of your query (create table dummy as select * from ....) and directly query it. The difference between those queries will tell network delay. – Burak Yurdakul Feb 26 at 8:27

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.