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

On Sat, Jun 27, 2015 at 7:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> writes:
> > 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.
>
> I don't like this too much because it will fail badly if the caller
> is wrong about the maximum possible page number for the table, which
> seems not exactly far-fetched. (For instance, remember those kernel bugs
> we've seen that cause lseek to lie about the EOF position?)

Considering we already have exclusive lock while doing this operation
and nobody else can perform write on this file, won't closing and
opening it again would avoid such problems. In patch that is already
done (smgrexists()) for 2 kind of forks and can be done for the third kind
as well.

> It also
> offers no hope of a fix for the other operations that scan the whole
> buffer pool, such as DROP TABLESPACE and DROP DATABASE.
>

True, but it is not foreclosing if any body has idea to optimize those
paths.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com