Andres Freund <andres@xxxxxxxxxxx> 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
