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

On 07/02/2015 04:18 PM, Amit Kapila wrote:
> On Thu, Jul 2, 2015 at 6:38 PM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>>
>> 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,
>
> Don't you think the approach discussed (delayed cleanup of buffers
> during checkpoint scan) is sufficient to fix the issues raised.

Dunno, but there is no patch for that.

- Heikki