1

I have a 228GB index on a 1.86TB table. My shared_buffers are 225GB on a host with 768GB RAM. The index is bloated by 75%. After reindexing it, I get about 85GB. I'm on PostgreSQL 10.9.

I think my current query times on the old index are good as-is. Postgres docs do say that Btree indexes require no maintenance [reference missing].

Given that I have other large indexes on other large tables, will Postgres work better given that the new index fits into shared buffers better?

1

Given that I have other large indexes on other large tables, will Postgres work better given that the new index fits into shared buffers better?

There is probably little if any benefit. If all the other indexes are rarely used and this one is intensively used, then maybe there will be. The gold standard would be try it and see. But unless all your scans are index-only scans, the index and the pages of the much larger table the index points at will be fighting over shared_buffers, as they certainly both won't fit.

Just because something will fit in the shared_buffers, doesn't mean it will get loaded into shared_buffers (you could force it with pg_prewarm) and then stay there forever (PostgreSQL doesn't provide a way to "nail" things into shared_buffers). And the buffer eviction algorithm is not all that clever, so don't count on them staying in shared_buffers just because it seems obvious to you that they should.

I think my current query times on the old index are good as-is.

So why worry about it then?

| improve this answer | |
  • My thought process is that I have other large indexes and other large tables on this database and they are all used, therefore if I shrink down my indexes they will play better together given the fixed shared_buffers space. Do you think that makes sense? – Lev Apr 3 at 17:53
  • If you have several tables and indexes of similar sizes to those, and used a similar amount, I think it is probably a "lost cause" to get shared_buffers to cache them better. But it is also unlikely to hurt, other than the time it takes to execute the reindex. – jjanes Apr 3 at 18:57
  • Another, perhaps not very relevant, consideration is that caching blocks from a bloated object wastes RAM. But I agree that it is not necessary to act if the performance is good and one can live with the wasted space. – Laurenz Albe Apr 3 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.