Re: drop/truncate table sucks for large values of shared buffers

On 06/27/2015 07:45 AM, Amit Kapila wrote:
> Sometime back on one of the PostgreSQL blog [1], there was
> discussion about the performance of drop/truncate table for
> large values of shared_buffers and it seems that as the value
> of shared_buffers increase the performance of drop/truncate
> table becomes worse. I think those are not often used operations,
> so it never became priority to look into improving them if possible.
>
> I have looked into it and found that the main reason for such
> a behaviour is that for those operations it traverses whole
> shared_buffers and it seems to me that we don't need that
> especially for not-so-big tables. We can optimize that path
> by looking into buff mapping table for the pages that exist in
> shared_buffers for the case when table size is less than some
> threshold (say 25%) of shared buffers.
>
> Attached patch implements the above idea and I found that
> performance doesn't dip much with patch even with large value
> of shared_buffers. I have also attached script and sql file used
> to take performance data.

I'm marking this as "returned with feedback" in the commitfest. In
addition to the issues raised so far, ISTM that the patch makes dropping
a very large table with small shared_buffers slower
(DropForkSpecificBuffers() is O(n) where n is the size of the relation,
while the current method is O(shared_buffers))

I concur that we should explore using a radix tree or something else
that would naturally allow you to find all buffers for relation/database
X quickly.

- Heikki