I believe you can use pg_prewarm to pin index or table to cache. https://www.postgresql.org/docs/current/static/pgprewarm.html
On Wed, 19 Sep 2018 at 22:50, Sam R. <samruoh...@yahoo.com> 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
