Re: First query on each connection is too slow

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.

If that doesn't fix it, (b) could perhaps be alleviated by adopting
connection pooling, though that has costs of its own.

regards, tom lane