Re: First query on each connection is too slow

Hi,

On 2018-06-13 10:49:39 -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On 2018-06-13 12:55:27 +0300, Vadim Nevorotin wrote:
> >> I have a very strange problem. I'm using PostgreSQL 9.6 with PostGIS 2.3
> >> (both from Debian Strecth repos) to store DB for OSM server (but actually
> >> it doesn't matter). And I've noticed, that on each new connection to DB
> >> first query is much slower (10x) than all others. E.g.:
> >> test_gis=# SELECT srid FROM geometry_columns WHERE
> >> f_table_name='planet_osm_polygon' AND f_geometry_column='way';
>
> > What you're seeing is likely a mix of
> > a) Operating system overhead of doing copy-on-write the first time
> > memory is touched. This can be reduced to some degree by configuring
> > huge pages.
> > b) Postgres' caches over catalog contents (i.e. how your tables look
> > like) having to be filled on the first access. There's not really
> > much you can do about it.
>
> Seeing that this query seems to involve PostGIS, I suspect that there
> might be a third cause: time to load the PostGIS shared library.
> If so, you could probably alleviate the issue by adding postgis
> to shared_preload_libraries.

Ah, good point. It recursively depends on quite a number of other
shared libraries, several of them large:

$ ldd /usr/lib/postgresql/10/lib/postgis-2.4.so|grep '=>'|awk '{print $3}'|xargs readlink -f|xargs size
text data bss dec hex filename
523851 9512 864 534227 826d3 /usr/lib/liblwgeom-2.4.so.0.0.0
191008 4528 296 195832 2fcf8 /usr/lib/x86_64-linux-gnu/libgeos_c.so.1.10.2
474429 12904 512 487845 771a5 /usr/lib/x86_64-linux-gnu/libproj.so.13.1.0
37408 1048 24 38480 9650 /lib/x86_64-linux-gnu/libjson-c.so.3.0.1
30775 768 8 31551 7b3f /usr/lib/x86_64-linux-gnu/libprotobuf-c.so.1.0.0
1795735 37356 5272 1838363 1c0d1b /usr/lib/x86_64-linux-gnu/libxml2.so.2.9.4
1769027 20992 17152 1807171 1b9343 /lib/x86_64-linux-gnu/libc-2.27.so
1643118 956 12 1644086 191636 /lib/x86_64-linux-gnu/libm-2.27.so
1706242 60760 568 1767570 1af892 /usr/lib/x86_64-linux-gnu/libgeos-3.6.2.so
1511723 47328 13504 1572555 17fecb /usr/lib/x86_64-linux-gnu/libstdc++.so.6.0.25
90261 944 688 91893 166f5 /lib/x86_64-linux-gnu/libgcc_s.so.1
100418 1840 16768 119026 1d0f2 /lib/x86_64-linux-gnu/libpthread-2.27.so
8106 792 112 9010 2332 /lib/x86_64-linux-gnu/libdl-2.27.so
2691066 58376 3168 2752610 2a0062 /usr/lib/x86_64-linux-gnu/libicui18n.so.60.2
1715333 73880 7008 1796221 1b687d /usr/lib/x86_64-linux-gnu/libicuuc.so.60.2
26901016 544 8 26901568 19a7c40 /usr/lib/x86_64-linux-gnu/libicudata.so.60.2
113334 1272 8 114614 1bfb6 /lib/x86_64-linux-gnu/libz.so.1.2.11
148548 2040 8 150596 24c44 /lib/x86_64-linux-gnu/liblzma.so.5.2.2

and with sizable writable mappings, too. So yea,
shared_preload_libraries should help quite a bit.

Greetings,

Andres Freund