0

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?

Rodrigo
  • 4,706
  • 6
  • 51
  • 94
  • Are you storing between requests or is this in the same http request? If it's the same one I would go with storing the result in PHP and reusing it. You could always just [benchmark it](http://stackoverflow.com/questions/535020/tracking-the-script-execution-time-in-php). – Mike Dec 03 '15 at 21:13
  • unless it's a huge amount of data, cache in php. even with the DB query cache, you're still forcing the DB and PHP to encode/decode the data for on-the-wire transfer, plus all the overhead of building internal data structures to hold the query data, blah blah blah. – Marc B Dec 03 '15 at 21:14

2 Answers2

2

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.

Jeff Lambert
  • 24,395
  • 4
  • 69
  • 96
  • 1
    It doesn't move over the network if the DB server is the same as the web server. – Mike Dec 03 '15 at 21:15
  • That's a fair point, but I imagine sockets will still be involved (this I don't know for sure about Postgre in particular). _Still_ slower than RAM. – Jeff Lambert Dec 03 '15 at 21:19
1

If you are wondering whether postgresql has an equivalent of mysql query cache, the short answer is no. The long answer is that it's available through various means such as pgpool.

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.

e4c5
  • 52,766
  • 11
  • 101
  • 134