Re: Cache relation sizes?

On Fri, Nov 9, 2018 at 4:42 PM David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On 7 November 2018 at 11:46, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > On 2018-11-07 11:40:22 +1300, Thomas Munro wrote:
> >> PostgreSQL likes to probe the size of relations with lseek(SEEK_END) a
> >> lot. For example, a fully prewarmed pgbench -S transaction consists
> >> of recvfrom(), lseek(SEEK_END), lseek(SEEK_END), sendto(). I think
> >> lseek() is probably about as cheap as a syscall can be so I doubt it
> >> really costs us much, but it's still a context switch and it stands
> >> out when tracing syscalls, especially now that all the lseek(SEEK_SET)
> >> calls are gone (commit c24dcd0cfd).
> >
> > I'd really really like to see some benchmarking before embarking on a
> > more complex scheme. I aesthetically dislike those lseeks, but ...
>
> I agree. It would be good to see benchmarks on this first. Those
> could be as simple as just some crude local backend cache that stuff
> the return value of RelationGetNumberOfBlocks in estimate_rel_size
> into a hashtable and does not take into account the fact that it might
> change. Should be okay to do some read-only benchmarking.

Oh, I just found the throw-away patch I wrote ages ago down the back
of the sofa. Here's a rebase. It somehow breaks initdb so you have
to initdb with unpatched. Unfortunately I couldn't seem to measure
any speed-up on a random EDB test lab Linux box using pgbench -S (not
"prepared"), but that test doesn't involve many tables, and also it's
an older kernel without KPTI mitigations. Attached in case anyone
else would like to try it.

--
Thomas Munro
http://www.enterprisedb.com