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 1g2dnc-0003Lm-I6 for pgsql-performance@arkaria.postgresql.org; Wed, 19 Sep 2018 14:49:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g2dna-0001Fa-IR for pgsql-performance@arkaria.postgresql.org; Wed, 19 Sep 2018 14:49:50 +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 1g2dnZ-0001FG-EX for pgsql-performance@lists.postgresql.org; Wed, 19 Sep 2018 14:49:50 +0000 Received: from sonic312-47.consmr.mail.bf2.yahoo.com ([74.6.128.109]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g2dnV-0003FK-E4 for pgsql-performance@lists.postgresql.org; Wed, 19 Sep 2018 14:49:48 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1537368583; bh=bn/0UNJrFLZ9B7bjQGzcWdCu7bdoc99truE1imAmHNA=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=pJmJb1h16qsdbfQe7lMZCv0NW8KK6qrcR7flOn2bS3PuZEkOyKHdDYk4xI685AByzKYPTw0lmXYzkBBfG85KNXmVsFRqreiYYwhpvDF7SUk/relH5bT6EhW4R/4mC3iGYV0piGzAzCefSj9Ozq6fqiR1Mctg4FGLAMQ6pYtB2s4mqdO/0OClNJvpG8SUTvp9fra6F+cH4C0J0HLJvFJeQXGEyCYyP5mw9JsMVHPMlpIlOzPVWMWU3HAU51Gm+5yc6gIqFlKAwSzDPJhPnDmPloXxBAFH+sAyHlc6cY3lhe1AyqEHaUJ1OIe9sarAhFZstmqiAYvcDSAGkGsyGjQnrg== X-YMail-OSG: lWEMaREVM1kQqC0jRwiT7JC_PKiF4Qa6wfCk2SdoNNJNii5Lwww4LBKhXx7EuaN 5uLtubZ45cPIHZamPOSg13lrgM4D0WKp.Iplqx53c6prItDhNtm3L6.S_KRtomnVIsvhEVmU9bqz CW.e_2_wSSZR76jSe2dyK_NjsNLPKSg9F0SPqjLiYEvu7hgPWzSv1ADg77GuCgisgsPHf0ictonl czIwNfaVwzt0mJxlq5HwjGTFsOn6Kpd3wshMpCrIFBXGY4HDsz3TWItE7C9g4labjytFoSqTlc9Q kPKc7GrGjmd_eVCVRX9QaXsZzkO2rOYSMzg37RbMIed4bARPa3KYOFVxtjFCFIwwXkEeNlnG90K2 CnNmot4CI_ijmhSUONY5xfWSEsBdVDGiJtDcaZYOchrHSmwjdJO1ScDB2MP7YkRtoy6d7Dh7Yntz HyhH4nLalQ3XY5paSgsnlGk1bpWwtBZh1mg.Vltc_3XohRzO3IRCzb1XES5xgW4NWTkxQ7T.LlbY Rq_iDMpJ00pgxfZj4xc2J4DK6voW0Kbe17YKaBGEt7FpDbNj69ciyiWoe4t6NnKnnS0GMypp.2zW nSSuN3hkrG7TxpM.iCRyYlDbHNWn08e_gMUUWBYaB1DOyErOdnFLUBvmzbrHDcTF3xnjRnZJQEKT PaSu9ytAMKQm.L3BDCBm0ZAFqj7EbD0Jd6_DRgXr98wAfwXM9s8XEIpR.5dhVC_eTLAqPalHZ1KS xUOLu9z837dQMrvvzOFS_UyTFDfTHCA6RuFAplynM4W2.Ar_NdmqspSPRqFzXBzD416UPkRChT0D uVMI9W8kMAnF4GNsW5cRYxiwsqjtOTdOSgsVfjpHfdYZqxg5hWHi3lh1DIKQ.W3LUdBVw51YKwE. lwdNDxo0iwqQnzSTIX4G9dqFkYVrvN2RmxkrspOntt.YCt0woZAhIlLDHUb.54ssiT7QdgnBPuyg S.ViRl2WzPxbG2MQeqsMg8v8F Received: from sonic.gate.mail.ne1.yahoo.com by sonic312.consmr.mail.bf2.yahoo.com with HTTP; Wed, 19 Sep 2018 14:49:43 +0000 Date: Wed, 19 Sep 2018 14:45:39 +0000 (UTC) From: "Sam R." Reply-To: "Sam R." To: Jeff Janes Cc: "pgsql-performance@lists.postgresql.org" Message-ID: <2104059362.5470282.1537368339686@mail.yahoo.com> In-Reply-To: References: <1321418371.5650982.1537346138600.ref@mail.yahoo.com> <1321418371.5650982.1537346138600@mail.yahoo.com> <954477769.6489282.1537348503194@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_5470281_129438428.1537368339684" 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: 5958 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_5470281_129438428.1537368339684 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 ------=_Part_5470281_129438428.1537368339684 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
<= span id=3D"yui_3_16_0_ym19_1_1537343373622_100011">Thanks for the comments!=

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

Jeff Janes wro= te:
> This sounds lik= e speculation.  Do you have hard evidence that this is actually the ca= se?

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 lar= ge random number. Our application may not even generate the random ID.
<= /span>

We use hash index over the ID.

At the moment, in "pure theory", we w= ill read randomly through the hash index.
So, no one will be able to know what part of the data (from the tabl= e) 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 think= ing 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


------=_Part_5470281_129438428.1537368339684--