I believe you can use pg_prewarm to pin index or table to cache.
On Wed, 19 Sep 2018 at 22:50, Sam R. <samruohola@xxxxxxxxx> wrote:
Thanks for the comments!Sam wrote:>> The data in db table columns is not needed to be kept in memory, only the index. (hash index.)Jeff Janes wrote:> This sounds like speculation. Do you have hard evidence that this is actually the case?In our case the "ID" is randomly generated random number. (Large ID.)
It is not a "sequential" number, but random.In generation phase, it is a very large random number. Our application may not even generate the random ID.We use hash index over the ID.At the moment, in "pure theory", we will read randomly through the hash index.So, no one will be able to know what part of the data (from the table) should be kept in memory.Side note: Of course there may be (even many) use cases, where same data is read again and again. Still: I am thinking now from a very theoretical point of view (which we may still apply in practice).In generic:
I am not certain how PostgreSQL or hash indexes work in detail, so my claim / wish of keeping only the index in memory may be faulty. (This is one reason for these discussions.)BR Sam
Regards,
Ang Wei Shan
Ang Wei Shan
