Re: First query on each connection is too slow

Hi,

On 2018-06-13 10:49:39 -0400, Tom Lane wrote:
> Andres Freund <andres@anarazel.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