Re: Performance comparison between Pgsql 10.5 and Pgsql 11.2

On Thu, Mar 7, 2019 at 12:12 AM Nicola Contu <nicola(dot)contu(at)gmail(dot)com> wrote:
> This is instead the strace of another server running the same version compiled but that is even slower.

Huh. That's a lot of lseek(). Some of these will be for random
reads/writes and will go way in v12, and some will be for probing the
size of relations while planning, and some while executing scans. I
bet you could make some of them go away by using prepared statements.
Does the query in your test involve many partitions/tables?

% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
32.50 143.010306 7 21044095 lseek
26.21 115.354045 14 8144577 read
6.18 27.185578 16 1669889 10 sendto
5.29 23.300584 57 407528 fdatasync
4.93 21.709522 9 2313529 824174 recvfrom
3.31 14.547568 19 765897 write
2.73 12.007486 14 867088 14494 epoll_wait
2.18 9.597460 15 659871 84097 futex
1.85 8.147759 14 567414 close
1.77 7.767832 18 437656 11319 open

The other results had 1 usec lseek(), and much fewer of them relative
to the number of reads and writes. BTW, are you comparing v10 and v11
on the same hardware, kernel, filesystem? Just wondering if there
could be some change in syscall overhead on different kernel patch
levels or something like that: we see 7 usec vs 1 usec in those two
files (though I have no idea how reliable these times are) and if
we're going to call it 21 million times at some point it might
matter...

--
Thomas Munro
https://enterprisedb.com