Re: To keep indexes in memory, is large enough effective_cache_size enough?

From: Wei Shan <weishan(dot)ang(at)gmail(dot)com>
To: samruohola(at)yahoo(dot)com
Cc: jeff(dot)janes(at)gmail(dot)com, pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Re: To keep indexes in memory, is large enough effective_cache_size enough?
Date: 2018-09-20 03:19:43
Message-ID: CAFe9ZTqZSA571KT9+Qw2w4LRC0D3ThSUu39PcLVJvVRNprR_pQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I believe you can use pg_prewarm to pin index or table to cache.

https://stream.boxwatch.ru/docs/current/static/pgprewarm.html

On Wed, 19 Sep 2018 at 22:50, Sam R. <samruohola(at)yahoo(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message David Rowley 2018-09-20 05:17:25 Re: To keep indexes in memory, is large enough effective_cache_size enough?
Previous Message Sam R. 2018-09-19 14:45:39 Re: To keep indexes in memory, is large enough effective_cache_size enough?