35

I'm trying to optimize my function.
The thing is when you run the query once you get one result.
Run the query a second time or third time the process time is much smaller.

SELECT  map.get_near_link(a.X, a.Y, a.azimuth)
FROM traffic.avl;

First time 17 seg

Total query runtime: 17188 ms.
801 rows retrieved.

Second time 11 seg

Total query runtime: 11406 ms.
801 rows retrieved.

I'm guessing there is some kind of cache doing optimization behind scene. How can i disable this behavior so i can get a more realistic runtime value?

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • I think you can assume that the second result is more realistic than the first one. – klin Jun 16 '14 at 21:28
  • 1
    Well in theory i assume x, y , azimuth will be different in each query, because is very unlike it a second user input the exact values. So i think the first value is the most realist. Anyway if i change the code in the function i will have to run all test several times to make sure wich one is the average value. – Juan Carlos Oropeza Jun 16 '14 at 21:51
  • When I was doing nontrivial tests on Postgres I learned that the standard deviation of the first result almost always was the biggest one. Hence I accepted the principle to skip the first result. I think you have to test your function with random arguments. – klin Jun 16 '14 at 22:16
  • To give you more info. Inside my engine i create a field [tProcess] to know how much time take each function call. Normally less than 10 ms for each function call is normal, but there are cases where a call register 2900 ms (x290 times). Then i want to single try that case to see what is wrong and i get the execution time is 30 ms :/ – Juan Carlos Oropeza Jun 16 '14 at 22:43
  • That is why you cannot rely on just one test. The only way is to increase the number of tests on random values. I have not heard of a method to *reset a server to the previous state*. – klin Jun 16 '14 at 22:57
  • For Windows you may use "SysInternals/RamMap" (see the [original answer](https://stackoverflow.com/a/9649439/10444579)). – Max Sidnin Mar 08 '20 at 14:21

3 Answers3

33

PostgreSQL doesn't have a "cache" optimisation, in the sense of a query result cache.

It does cache table blocks that were recently read in shared_buffers, but for most installs that only has a small effect. The main cache is the operating system's disk read cache. For more information see:

See and clear Postgres caches/buffers?

It sounds to me like you have a system with a reasonable amount of RAM and a fast CPU but a terribly slow disk. So queries that only hit the OS's disk cache are very fast, but queries that go to disk take a couple of seconds to read the data in. So caching effects are very strong.

You should explain (buffers, analyze, verbose) SELECT ... your queries. Try with a couple of different input values until you get a slow one. Compare plans.

If the plans are the same, that's probably it.

If the plans are different, you're probably hitting a situation where the query planner is making bad choices based on variations in the table statistics. Increasing the statistics targets for the columns of interest may help (see the manual). If you get different plans and are stuck / want help, feel free to post a new question on dba.stackexchange.com with details.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • After reading your answer, i did some thinking and looks like my issues are because a slow disk. I didn't have to try random values, the function is already called 500 times/min and i record the process time for each one. My main idea was try to replicate the cases when a `function(x,y,z)` call take more than 2000ms when usually only take 30ms. But after looking at the log file and checking the `explain` for each case i always got the same result and never could duplicate the slow behavior. So i guess in those cases is more like it the delay was caused by a busy disk than a bad index. Thanks. – Juan Carlos Oropeza Jun 18 '14 at 21:28
  • can you explain buffers.. basically i'm thinking it might be slow disk – Josh Mar 01 '23 at 01:51
14
sync; sudo service postgresql stop; echo 1 > /proc/sys/vm/drop_caches; sudo service postgresql start
rusllonrails
  • 5,586
  • 3
  • 34
  • 27
4

Since you said you want to optimize your function, not your disk layout, then the 2nd timings are probably the ones you want to focus on for that purpose, as they are the purest measure of the run time of your function itself, rather than the time needed to gather the data to feed into your function.

And since the 2nd-execution time is still 2/3 of the 1st execution time, that would still be where to focus your attention even if what you want to optimize total execution time.

If you really need to do this, you can clear the cache as explained in another answers, but that is usually too onerous to be used for routine work. Better would be to create a program/script that picks random (but generally realistic) values of a.X, a.Y, a.azimuth and runs the query with them. By changing the values each time from among a set of realistics examples, you get results most representative of the real world. Making such drivers is a little more work upfront but usually pays off.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • 1
    I had the same problem and, even is true that I want to optimize my function, I CAN'T change my disk layout so I need to test my function in the worst possible conditions. That is: when no data is in cache because I can change the order of querys and other stuff trying to avoid worst cases. But I need to be able to test it multiple times. – bitifet Jun 04 '15 at 05:49