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 1g2Ygy-0001U0-SQ for pgsql-performance@arkaria.postgresql.org; Wed, 19 Sep 2018 09:22:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g2Ygw-0003Sn-PB for pgsql-performance@arkaria.postgresql.org; Wed, 19 Sep 2018 09:22:38 +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 1g2Ygw-0003Sg-EK for pgsql-performance@lists.postgresql.org; Wed, 19 Sep 2018 09:22:38 +0000 Received: from sonic316-42.consmr.mail.bf2.yahoo.com ([74.6.130.216]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g2Ygp-0004B1-TK for pgsql-performance@lists.postgresql.org; Wed, 19 Sep 2018 09:22:37 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1537348949; bh=0ACO2x4amyk1GLhJfx2LlAoEIcmDxqAy5z4p0Jo5QEY=; h=Date:From:Reply-To:To:In-Reply-To:References:Subject:From:Subject; b=IVkDABvKhNTVHnXeffABENf7I0bwzx71FIdnH7Y+SqEIROSre894nOV4JjbiWx+ofUBJaTB4hkrFGd4c9ud30kzoL9fQcOBdMj+Pc9WofDG16r16KiQRQ1f9I09HJvdIJGU7Qqc/6mCphc8bCKkV5o/zy96jsRN5ooWlfgVx1C1gnZ/msN7pTsi/8VUAXCnplMnVblKeaoQzcxGbA5uh5v5dA/B5ETREp0I2QHaUrnaJahqNt47kJq5BA7jf8rN0xEshJUxGNY4IogYMF56h3j8lpg+pZgd/sJXedhRe09AjXwr5+aeJR3UKSUbKz1SsK5mZXIQiT9GKjOL806/7Aw== X-YMail-OSG: UTl65fUVM1kOW2_4D38C0f9Ub3dgS5iipRy7QVASXceG_VNJ.VcdEMltPPRO4yu m1tjxXUkE9eBTxiihrZLxev9fy0XCVN5jul2E8ontxZhPLsDuyGGQtsBTLAP8l51coO0ZvXoDj7p CrtSFOoEitBDTg4qeXqpdaqxgeU91Izw4ewmVfMdG.eEUcHmJV5..LJ5oixclpw9uEGUzYSPzD7n jYITZsdYzw3cx6_VDb4W4.bVOKv7J_CANxPnlnRP4eny3ilyhkpSQY4eri3jjuRF0GbGucciFoQh ae9gT_1nKueput3Nnse4IboGjp3pzOkVy_7blrPugREoZgunz46jddblRz78xy0Tky6AhoqxHAEr VxPiWMVsNACPrEQ524yW.ONuq82bVzKOgIqM9g47tP5TaCpYHggJrFJhhDKNh2Ix9VtoDKJcxYHr dEUKeVaL1XL.oUVqg2MPtXD6XKepZCy8mmz7yVZxZbdsiv6R4Wfnc1mhtJeIHj8NV6z1aODYqMh_ fGxqHrnN9ahowtfFfKRFa4xgBjRM_9T7O1AduqDtmTsyb5J7Otcyi0uJa4Ou9JbL2pxDjUrctjhx qhVvtY_6oDWxIgMDLNeJ_zJ7eQmffrTg9uNxVzFglF.4DqrqE2HdyS5SKC0WK_oAIIBgXu_fJ4ue rOQQuHxXc_AE2JYwCtS.CvEtTGdJUuOPVxQXIyih1VAlmlyhaQQNxfoOsi4kpX2RqqwU42e3Sk9U NEWUuRg8SJKH0tfPQ3SCdvAwIfvKLR18xzRlPmh81gYFQz2svOXq_ucBA4c42x2UdG451TkyrJ_d gBxWNWUBl8EZB3B_a9kcY.G10lqzLCvOCetLPD.HmSJ6MNkaQFnTKVNiTOY.RyI6c93pEN6QnVPZ T9KR0pXGB3_qCAPtcGGupAzeT2QvPe_rR4hqCVKi8rtCUMpW99YBQoll4ICghdg69M33p7tXiCGt rGa_u9iR0oc4_Z_XPzdLA8w-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic316.consmr.mail.bf2.yahoo.com with HTTP; Wed, 19 Sep 2018 09:22:29 +0000 Date: Wed, 19 Sep 2018 09:18:24 +0000 (UTC) From: "Sam R." Reply-To: "Sam R." To: Sergei Kornilov , "pgsql-performance@lists.postgresql.org" Message-ID: <1476619436.6435500.1537348704720@mail.yahoo.com> In-Reply-To: <5456011537348252@sas1-fed4e4c8a570.qloud-c.yandex.net> References: <1321418371.5650982.1537346138600.ref@mail.yahoo.com> <1321418371.5650982.1537346138600@mail.yahoo.com> <5456011537348252@sas1-fed4e4c8a570.qloud-c.yandex.net> Subject: Re: To keep indexes in memory, is large enough effective_cache_size enough? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6435499_160050449.1537348704718" 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: 4677 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_6435499_160050449.1537348704718 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Sergei wrote: > You can not pin any table or index to shared buffers. Thanks, this is answer to my other question! In our case, this might be an important feature. (Index in memory, other data / columns not.) > shared_buffers is cache for both tables and indexes pages. Ok. So, we should set also shared_buffers big. BR Sam On Wednesday, September 19, 2018 12:10 PM, Sergei Kornilov wrote: Hi effective_cache_size is not cache. It is just approx value for query planner: how many data can be found in RAM (both in shared_buffers and OS page cache) > Q: Size of shared_buffers does not matter regarding keeping index in memory? shared_buffers is cache for both tables and indexes pages. All data in tables and indexes are split to chunks 8 kb each - pages (usually 8kb, it can be redefined during source compilation). Shared buffers cache is fully automatic, active used pages keeps in memory, lower used pages may be evicted. You can not pin any table or index to shared buffers. regards, Sergei ------=_Part_6435499_160050449.1537348704718 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Sergei = wrote:
> You can not pin any table = or index to shared buffers.

Thanks, thi= s is answer to my other question!

In our case, this might be an important feature.
(Index in memor= y, other data / columns not.)
> shared_buffers is cache for both tables and indexes pages.

Ok. So, we should set also shared_buffers big= .

BR Sam


On Wednesday,= September 19, 2018 12:10 PM, Sergei Kornilov <sk@zsrv.org> wrote:


Hi=

effective_cache_size is not cache. It= is just approx value for query planner: how many data can be found in RAM = (both in shared_buffers and OS page cache)


> Q: Size of share= d_buffers does not matter regarding keeping index in memory?

shared_buffers is cache for both tables and indexes pages. All da= ta in tables and indexes are split to chunks 8 kb each - pages (usually 8kb= , it can be redefined during source compilation).
Shared = buffers cache is fully automatic, active used pages keeps in memory, lower = used pages may be evicted. You can not pin any table or index to shared buf= fers.

regards, Sergei



=
------=_Part_6435499_160050449.1537348704718--