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 1g2Yda-0001Ij-Gn for pgsql-performance@arkaria.postgresql.org; Wed, 19 Sep 2018 09:19:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g2YdY-0001Ig-Ro for pgsql-performance@arkaria.postgresql.org; Wed, 19 Sep 2018 09:19:08 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g2YdY-0001IZ-GS for pgsql-performance@lists.postgresql.org; Wed, 19 Sep 2018 09:19:08 +0000 Received: from sonic310-40.consmr.mail.bf2.yahoo.com ([74.6.135.214]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g2YdV-000453-1p for pgsql-performance@lists.postgresql.org; Wed, 19 Sep 2018 09:19:07 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1537348743; bh=jPXoGJbsuxg35sY0+fOx4nd/XMtZxgx6mHNWh76Hpvw=; h=Date:From:Reply-To:To:In-Reply-To:References:Subject:From:Subject; b=LpObUfOrTBcdGjnfUutLoMXOl6I6Dzv4MSlqwxJp8xnhl5mAGWpZV3I1IysmGdD3VClr+ErsNEEMvMjLkgGLZWmO34hHQpGUbRrkg4NW1GpRbar9ABezjkTjCAltLD1kd0RoK6Y6/tgOLLFy+yz90k+ExW778upwbs/SfnfX0Z2XwSNvkPaVddPyJyHMxv0ZlDxrP4Y+NaIeZK7mX+0fOmGYD6tecSrhgErKYaXTizCIVch8iM8ABjSrFWHBI7cWIAVV89e+eNCdEdujz+pTCXS5z0L4HUGN14mzfDpH1VrIq3cg6yANUQj2Ujq4N3vCIr6Jmbl3Eflp2Eqt4XPRag== X-YMail-OSG: 9yqxDjsVM1m9WU3dibAbQKO_hBonaoSw._OteVLbBlGWY67lodWW.uK_jj.AulA N6eNuB0J_6cJYYzAP5OMcnrhTZX4f9jwSgp6qpbafTBrr3pI7SC.Ozg2NipUCNpP19N83xuRxXAi MxTcX2A.YTLHrYLZ0nWEfvlM09W67yjHILhBuxmju16UhZmY4LgnhKOr7a3JFQ1ElzNX3bBBdM0D EeMs1p_tKgmooypey28vv42IqjGm5WCOm7hXkVDcLacQjda4_wS.OGkfm.RJCglQ966Fh60IhPIR zJE8PS65hzKbx4.EdYi.hQGsEqiwAybjkwlMJjTs44BXwQzRH8szTlg7R47GUQww6m243C7mKawr ULf_Hke6qqmkifWKXyuwndpWdYvvioQaXkVaM_cXQffHoUF2NlXYsaNdSp3gAN915vK5TpBrm2Kd r0fShTVW7Ey5CTcKIlcmo_.giwAzU0c8mwDGYsWs3fuGns9q5VN1jTrBzaWBB09tvPfwvPuL10sy aLgz5GNlgXyPNai0vB9wYPwE7OsN85uDjcTP_b4oRisaezDqxrmmrDagdf7JIsEjgN.6afDR_RWL cHjyS9anciUnmKL.gkmdECM7c_4Z3Jn_4amHK5AGu4T.3FBs_EVpvJtCzeqv1dLmdz0nDDpAt7W6 3SqoOyJ.yzjgb5nI9qOzsOanvb6MpYTxSdkYRr.p_M2MTO3m9eoC_gNkhkJMpJq24.tF1UNarmSG TpZTwV0n5FRbOrl2lgetBvdM7t4_eGjVCgTxZVVzFHYO7MjvR_NeRl0BgbSbUT8lD7kFldrgwNi5 YD8jXjqHBUrvrStAbEyHQUuJdtwu_ZAR7Nqm8SZc9r6ZIS.h2U4UI95ODXH5Tf1WO8ReXN9yRaA9 piish1ciCygynag_hqT4E8nFGQIoTZjdB2IVsAUmGazdif46y7aeHyE70YjODkGytmC3pHm.uUmQ Uk9q0sfbSHROzBiw.z0iRTiM2ZJSbgMHb Received: from sonic.gate.mail.ne1.yahoo.com by sonic310.consmr.mail.bf2.yahoo.com with HTTP; Wed, 19 Sep 2018 09:19:03 +0000 Date: Wed, 19 Sep 2018 09:15:03 +0000 (UTC) From: "Sam R." Reply-To: "Sam R." To: "Sam R." , "pgsql-performance@lists.postgresql.org" Message-ID: <954477769.6489282.1537348503194@mail.yahoo.com> In-Reply-To: <1321418371.5650982.1537346138600@mail.yahoo.com> References: <1321418371.5650982.1537346138600.ref@mail.yahoo.com> <1321418371.5650982.1537346138600@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_6489281_175662574.1537348503191" X-Mailer: WebService/1.1.12406 YahooMailNeo Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:60.0) Gecko/20100101 Firefox/60.0 Content-Length: 7706 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_6489281_175662574.1537348503191 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Hi! Is is possible to force PostgreSQL to keep an index in memory? The data in db table columns is not needed to be kept in memory, only the index. (hash index.) It would sound optimal in our scenario.I think Oracle has capability to keep index in memory (in-memory db functionality). But does PostgreSQL have such a functionality? (I keep searching.) I have read: Tuning Your PostgreSQL Server - PostgreSQL wiki (effective_cache_size, shared_buffers) I have seen responses to: PostgreSQL Index Caching Should I actually set shared_buffers to tens of gigabytes also, if I want to keep one very big index in memory? I ma also reading a PG book. Best Regards, Sam On Wednesday, September 19, 2018 11:40 AM, Sam R. wrote: Hi! Related to my other email (size of index in memory), Other questions, Q: To keep _index(es)_ in memory, is large enough effective_cache_size enough? Q: Size of shared_buffers does not matter regarding keeping index in memory? Or have I missed something, does it matter (to keep indexes in memory)? Background info: I have plans to use hash indexes: very large amount of data in db tables, but (e.g. hash) indexes could be kept in memory. I am using PostgreSQL 10. I could start to use PostgreSQL 11, after it has been released. Best Regards, Sam ------=_Part_6489281_175662574.1537348503191 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi!

=
Is is possible to force PostgreSQL to keep an ind= ex in memory? The data in db table columns is not needed to be kept in memo= ry, only the index. (hash index.)

It would sound optimal in our s= cenario.
I think Oracle h= as capability to keep index in memory (in-memory db functionality). But doe= s PostgreSQL have such a functionality? (I keep searching.)

I have read:
(effective_cache_size, shared_buffers)

I ha= ve seen responses to:

=


Should I actually set shared_bu= ffers to tens of gigabytes also, if I want to keep one very big index in me= mory?

I ma also rea= ding a PG book.
=
Best Regards, Sam


=


On Wednesday, September 19, 2018 11:40 AM, Sam R. <samruohola@y= ahoo.com> wrote:


Hi!

Rel= ated to my other email (size of index in memory),

Other questions,
Q: To keep _index(es)_ in memory, is large enoug= h effective_cache_size enough?
Q: Size of shared_buffers does not matter regarding keepin= g index in memory?

Or have I missed something, does it matte= r (to keep indexes in memory)?

Background info: I have plans to use hash indexe= s: very large amount of data in db tables, but (e.g. hash) indexes could be= kept in memory.

I am using= PostgreSQL 10. I could start to use PostgreSQL 11, after it has been relea= sed.

Best Regards, Sam
<= /div>


------=_Part_6489281_175662574.1537348503191--