| 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
| 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? |