Benefits of reindexing large bloated indexes in PostgreSQL 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?
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?