Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g4gxZ-00025x-Su for pgsql-performance@arkaria.postgresql.org; Tue, 25 Sep 2018 06:36:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g4gxY-0003g9-8y for pgsql-performance@arkaria.postgresql.org; Tue, 25 Sep 2018 06:36:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g4gxX-0003g2-3G for pgsql-performance@lists.postgresql.org; Tue, 25 Sep 2018 06:36:36 +0000 Received: from sonic306-2.consmr.mail.bf2.yahoo.com ([74.6.132.41]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g4gxQ-0002XZ-23 for pgsql-performance@lists.postgresql.org; Tue, 25 Sep 2018 06:36:34 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1537857383; bh=zslzmVMVKjhwuEnEtbKws3uKfMPa9+QyIbVWOaBZBLk=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=SfpOwbC7H/zbxw7T1bxEBl8C2yZLw0mBobfN3l4n2Nwn1QsM7ghwVBdpSKU9DsCaOXbCOc2Z81BXEF3f9JN/jGpvhlA7zW/yXhYUzYFr3NZFMi7R8okAjphCXPbV7hCvpnyU3efKoiUlQpO01hp4CN9dZohRs6G7BH1wVGQvJ5y8CE+pAzc4Iyw9k/CNi0IlsnpTKByUTBn9MTKoryd4oSuGH2J9VwbfT1fWX/XQips3FQ6b9U2sUukmolAmxRwi1NL9NHX0OTUNVtbsjq9hGOUu9lFGKN8YKUN3PrrM+cte95MEcH9eWJ2WMUtQ3X6yRvT3ONZOLYlSEd+PUGnB6g== X-YMail-OSG: 71AIb70VM1kjeSsKxpLsvpZY0zgqu5SuZME0avGgNH2YBUJ8mcinNGgUMhYbrgn jNZ7fLUJoH84FZS39ntGIW35Ji0YmzSiCSCIx0KT6ztxc_3SYF.4PoTkFoSnLOlUgIrVSCRINfHh XMC9LJ6md2FHr_pn3ClTyRN_rlfmpSl38f6jisIBhYKDUaei5FLXqBttJtoM_yWJ1ETjy4pyrM7p 5hkQeSMQ.YkBEtPIuZzqFq9KQwuwTIL6qYQOjGPPn87kEQCXU8oU0URm6LI6fjiFwpqeSBBn2NC3 pp_k1qyTTEYRPbSvaHAE9BC1ZzDXFadWv_8x_ODEa0oo2Mm.9oUdwDpOzlq3yTVAr_sxnUk2P_YO l_07_m7.lzWUbOgjQ2RRWwXYvv0OYlJSZk8qaOsEkXp_viupyscC_f6lg1B0C3Xu12eu9HL2SPON Jzu0lh5uMiFR7c_cwP6XeJV0_.IpIFhLPgD5Ss7zuII9MdQh2VW1hueqNgJZQUylnAAbMS0pXl8H QuIX2e3zrl3A0B9ME5CpUoamBtqTN_zyHeUKrPK1wRc3OHUIYQG9EZsMr9rjNfekalgfO4WvCMsi qe4uXOYSKFG3gMe4GLLgPUVOR4QFvYEBdQNGE8QI154jU3sWy.V5Fzf5fe0sgvHDR6IhfCNgtK2E Eua14tDaynIWWMh2TziaKaZIbWrRfJLEQQzhxQbM13PWyjDxI8w3tsdhzCX6g.qAPJmXR_bIysiX 6SgK9OXIVilltqHiHbk5Ujbjx5kIdEXIJ6g7xte9oigXpxGHfBmAuSXQ9C4uoIxLbbJFzaJnzQnj y0fGiUP_ZEtDuxf5dLjrRMzEz..5GgW3YJU1VMc2U9e4SNrl3Vld7S_MMPsK2ZnJLaPjZsGtlx20 yU.2FW.WU3Q.L3Rak2m4zv3kxmTkyI7Ca0YJonPZD3LwLqTS303SOKhTqqxSGxp69F7OxLXA9..R cpqwC8comz.TGLoQHQwfNgXqeHe8G4g-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic306.consmr.mail.bf2.yahoo.com with HTTP; Tue, 25 Sep 2018 06:36:23 +0000 Date: Tue, 25 Sep 2018 06:36:18 +0000 (UTC) From: "Sam R." Reply-To: "Sam R." To: "Sam R." , "pgsql-performance@lists.postgresql.org" Cc: Jeff Janes Message-ID: <1685615979.2029662.1537857378862@mail.yahoo.com> In-Reply-To: <2104059362.5470282.1537368339686@mail.yahoo.com> References: <1321418371.5650982.1537346138600.ref@mail.yahoo.com> <1321418371.5650982.1537346138600@mail.yahoo.com> <954477769.6489282.1537348503194@mail.yahoo.com> <2104059362.5470282.1537368339686@mail.yahoo.com> Subject: Re: To keep indexes in memory, is large enough effective_cache_size enough? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2029660_1427550437.1537857378858" X-Mailer: WebService/1.1.12487 YahooMailNeo Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:60.0) Gecko/20100101 Firefox/60.0 Content-Length: 12207 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_2029660_1427550437.1537857378858 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi! "Index in memory" topic: After read operation starts, I think / it seems that a big part of an index gets loaded to memory quite = quickly. A lot of IDs fit to one 8 KB page in PostgreSQL. When reading oper= ation starts, pages start to be loaded to memory quickly. So, this "feature" / PostgreSQL may work well in our very long "random" IDs= cases still.=20 It is not needed / not possible to keep the whole index in memory: E.g. if = there is not enough memory / size of index is bigger than memory, it is not= even possible to keep whole index in memory.=20 ( Regarding in memory DB functionalities: I do not know would "In-memory" i= ndex / db work in such situations, if index would not fit in memory. We wou= ld like to keep most of the index in memory, but not whole index in all cas= es e.g. when there is not enough memory available. ) So, again, maybe PostgreSQL works well in our case. Regarding double buffering: I do not know how much double buffering would s= low down operations.=20 It could also be possible to turn off kernel page cache on our DB server, t= o avoid double buffering. Although, we may still keep it in use. BR Sam On Wednesday, September 19, 2018 5:50 PM, Sam R. = wrote: =20 Thanks for the comments! Sam wrote: >> The data in db table columns is not needed to be kept in memory, only th= e index. (hash index.) Jeff Janes wrote: > This sounds like speculation.=C2=A0 Do you have hard evidence that this i= s 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 ind= ex.So, no one will be able to know what part of the data (from the table) s= hould be kept in memory.=20 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 poi= nt of view (which we may still apply in practice). In generic:=20 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.=C2=A0 (This is o= ne reason for these discussions.) BR Sam =20 =20 =20 ------=_Part_2029660_1427550437.1537857378858 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi!

<= /div>
"Index in memory" topic:

After read operation starts,
I think / it seems that a big part of an index gets loa= ded to memory quite quickly. A lot of IDs fit to one 8 KB page in PostgreSQ= L. When reading operation starts, pages start to be loaded to memory quickl= y.

So, this "feature" / PostgreSQL may work w= ell in our very long "random" IDs cases still.

It is not needed / not possible to keep the who= le index in memory: E.g. if there is not enough memory / size of index is b= igger than memory, it is not even possible to keep whole index in memory. <= br>( Regarding in memory DB functionalities: I do not know would "In-memory= " index / db work in such situations, if index would not fit in memory. We = would like to keep most of the index in memory, but not whole index in all = cases e.g. when there is not enough memory available. )

So, again, maybe PostgreSQL work= s well in our case.

Regarding double buffering: I do not know how much double buffering = would slow down operations.
It could also be possible to turn off kerne= l page cache on our DB server, to avoid double buffering. Although, we may = still keep it in use.

BR Sam
<= /div>
On Wednesday, September 19, 201= 8 5:50 PM, Sam R. <samruohola@yahoo.com> wrote:

Thanks for the comments!

Sam wrote:
>> The data in db table col= umns is not needed to be kept in memory, only the index. (hash index.)

Jeff Janes wrote:

> This sounds like specula= tion.  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 no= t a "sequential" number, but random.
=
In generation phase, it is a very large random numbe= r. Our application may not even generate the random ID.
<= /span>

We use hash index over the ID.
<= /div>
=
At the moment, in "pure theory", we will read randomly through t= he 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 s= ame data is read again and again. Still: I am thinking now from a very theo= retical point of view (which we may still apply in practice).

In generic:
I am not cert= ain how PostgreSQL or hash indexes work in detail, so my claim / wish of ke= eping only the index in memory may be faulty.  (This is one reason for= these discussions.)

BR Sam



------=_Part_2029660_1427550437.1537857378858--