From: Sam R. <samruohola@yahoo.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: To keep indexes in memory, is large enough effective_cache_size enough?
Date: Wed, 19 Sep 2018 14:45:39 +0000 (UTC)
Message-ID: <2104059362.5470282.1537368339686@mail.yahoo.com> (raw)
In-Reply-To: <CAMkU=1zSpXBPOeZDXoiFOtFHid+cmmAKLpV1Mh2mH=1TurPA3w@mail.gmail.com>
References: <1321418371.5650982.1537346138600.ref@mail.yahoo.com>
<1321418371.5650982.1537346138600@mail.yahoo.com>
<954477769.6489282.1537348503194@mail.yahoo.com>
<CAMkU=1zSpXBPOeZDXoiFOtFHid+cmmAKLpV1Mh2mH=1TurPA3w@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: samruohola@yahoo.com, jeff.janes@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: To keep indexes in memory, is large enough effective_cache_size enough?
In-Reply-To: <2104059362.5470282.1537368339686@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox