Which is faster: to run the same sql query twice or run only once and store the result in arrays?
Suppose I have a SQL query that gives me some complex data, and in order to display it properly in HTML, I came up with a method where I run the same (or similar) query twice.
To avoid running the same query twice, I can create a table in memory (max a few hundred lines), using array of arrays or anything like that.
My question is: does Postgresql has some sort of cache for the last operation, where it could be more advantageous to run the exact same query twice, instead of creating an array of arrays?
Storing the result in RAM will almost undoubtedly be faster, but will use more RAM. It's a tradeoff. Even if Postgre does have some last query cache, you will still need to move data over the network in order to access it, which will still be slower than simply going to RAM to get the data.
However it should be noted that if sufficient memory has been allocated to postgresql it will cache indexes and data in memory and the second time you execute a query it will be much faster than the first time even though the resultset is not being cached by default.
Even if you are saving the resultset in memory it might not be more efficient than fetching it from the database it's hard to say without bench marking.
If you know for a fact that your query is slow and it cannot be optimized with better indexes, and you want to use the result in different http requests, you ought to invest in a caching mechanism like redis or memcached. You will see a big performance boost.