Re: To keep indexes in memory, is large enough effective_cache_size enough?

2018-09-19 Thread David Rowley
On 20 September 2018 at 15:19, Wei Shan wrote: > I believe you can use pg_prewarm to pin index or table to cache. > > https://www.postgresql.org/docs/current/static/pgprewarm.html I think the key sentence in the document you linked to is: "Prewarmed data also enjoys no special protection from ca

Re: To keep indexes in memory, is large enough effective_cache_size enough?

2018-09-19 Thread Wei Shan
I believe you can use pg_prewarm to pin index or table to cache. https://www.postgresql.org/docs/current/static/pgprewarm.html On Wed, 19 Sep 2018 at 22:50, Sam R. wrote: > Thanks for the comments! > > Sam wrote: > > >> The data in db table columns is not needed to be kept in memory, only > the

Re: To keep indexes in memory, is large enough effective_cache_size enough?

2018-09-19 Thread Sam R.
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 ran

Re: To keep indexes in memory, is large enough effective_cache_size enough?

2018-09-19 Thread Jeff Janes
On Wed, Sep 19, 2018 at 5:19 AM Sam R. wrote: > Hi! > > Is is possible to force PostgreSQL to keep an index in memory? > It might be possible to put the indexes in a separate tablespace, then do something at the file-system level to to force the OS cache to keep pages for that FS in memory. >

Re: To keep indexes in memory, is large enough effective_cache_size enough?

2018-09-19 Thread Sam R.
Size of the index of one huge table has been e.g. 16-20 GB (after REINDEX). Size of such an index is quite big.   BR Samuli On Wednesday, September 19, 2018 2:01 PM, Sam R. wrote: Hi! Thanks for all of the comments! David wrote:> if you mention > how muchRAM the server has and how bi

Re: To keep indexes in memory, is large enough effective_cache_size enough?

2018-09-19 Thread Sam R.
Hi! Thanks for all of the comments! David wrote:> if you mention > how muchRAM the server has and how big the data is now Let's say for example: RAM: 64 GB Data: 500 GB - 1.5 TB, for example. ( RAM: Less would of course be better, e.g. 32 GB, but we could maybe go for an even little bit bigger v

Re: To keep indexes in memory, is large enough effective_cache_size enough?

2018-09-19 Thread David Rowley
On 19 September 2018 at 22:12, Kaixi Luo wrote: > Does a large shared_buffers impact checkpoint performance negatively? I was > under the impression that everything inside shared_buffers must be written > during a checkpoint. Only the dirty buffers get written. Also having too small a shared buf

Re: To keep indexes in memory, is large enough effective_cache_size enough?

2018-09-19 Thread Kaixi Luo
Does a large shared_buffers impact checkpoint performance negatively? I was under the impression that everything inside shared_buffers must be written during a checkpoint.

Re: To keep indexes in memory, is large enough effective_cache_size enough?

2018-09-19 Thread David Rowley
On 19 September 2018 at 21:18, Sam R. wrote: > Ok. So, we should set also shared_buffers big. It might not be quite as beneficial as you might think. If your database is larger than RAM often having a smaller shared_buffers setting yields better performance. The reason is that if you have a very

Re: To keep indexes in memory, is large enough effective_cache_size enough?

2018-09-19 Thread Sam R.
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 s

Re: To keep indexes in memory, is large enough effective_cache_size enough?

2018-09-19 Thread Sam R.
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 Po

Re: To keep indexes in memory, is large enough effective_cache_size enough?

2018-09-19 Thread Sergei Kornilov
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.

Re: How to see/calculate size of index in memory?

2018-09-19 Thread Sergei Kornilov
Hello You can use pg_buffercache contrib module: https://www.postgresql.org/docs/current/static/pgbuffercache.html pg_relation_size - yes, its full size on disk regardless buffer cache regards, Sergei

To keep indexes in memory, is large enough effective_cache_size enough?

2018-09-19 Thread Sam R.
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 mem

How to see/calculate size of index in memory?

2018-09-19 Thread Sam R.
Hi! I would have following question, if someone could help. Question 1: How to see/calculate size of index in memory? BTree, hash index. I can see size of index e.g. with pg_relation_size FROM pg_class (after reindex). Does that tell size of index on disk? I would be interested how big part o