0

Suppose I do this ten thousand times in quick succession:

SELECT * FROM table WHERE id = $1;

That is, not as a prepared statement which is executed, but as individual queries.

Will PG actually execute the query each and every time, or will it somehow keep track of the fact that it was recently run, and that nothing has changed in between, and instead of running it again, simply return the last result which it has kept around in RAM?

Or do I need to do this client-side with my own application code?

Many years ago, I heard of "memcached" and stuff like that, but could never figure out how to use it, so I never did. I don't use any kind of "abstraction layer" either which might do this on its own, so I'm purely wondering if PG does this internally and automatically as part of some general optimization/resource-saving mechanism.

3
  • Look into the shared buffer settings. postgresql.org/docs/current/runtime-config-resource.html
    – VynlJunkie
    Dec 7, 2020 at 16:58
  • 2
    "Will PG actually execute the query each and every time" - yes Dec 7, 2020 at 18:07
  • Consider that enabling query result caching (where the feature is available) often brings more problems than it solves, unless you really know what you are doing. If you indeed are querying your database ten thousand times and don't expect the result to change, chances are you're doing something you probably shouldn't.
    – mustaccio
    Dec 7, 2020 at 23:39

1 Answer 1

1

It will execute it each time. It will cache the data it needs to do the execution (assuming that is small enough to stay in cache), but not the results. Who knows, maybe the results have even changed, what with other people being allowed to do inserts, updates, and deletions at the same time as you are looping over your 10,000 executions.

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.