On Fri, Nov 9, 2018 at 4:42 PM David Rowley <david.row...@2ndquadrant.com> wrote: > On 7 November 2018 at 11:46, Andres Freund <and...@anarazel.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
0001-Cache-file-sizes-to-avoid-lseek-calls.patch
Description: Binary data
