Date: Thu, 18 Jan 2018 10:55:32 +0900
From: Michael Paquier <michael.paquier@gmail.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Nandakumar M <m.nanda92@gmail.com>,
	"pgsql-performa." <pgsql-performance@postgresql.org>
Subject: Re: Query is slow when run for first time; subsequent execution is
 fast
Message-ID: <20180118015532.GE29962@paquier.xyz>
References: 
 <CANcFUu4e1zZQCvt9rjQGNUFV-kxv7F8Somk5Zgq2tXCO9dZhDg@mail.gmail.com>
 <CAMkU=1yiULP9Gdy1pT2bpVomi4cinNGz8d7-7y+chBOQFg3f6Q@mail.gmail.com>
 <CANcFUu611kaPfH+2F3pxGNX3x9jM-QiCTuS+4pzTeLL50+5s3w@mail.gmail.com>
 <CAMkU=1zFFxF74BVpwbsT1s-yY-KmW8Ya6CZkM0PDfvZ8uupF9A@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha256;
	protocol="application/pgp-signature"; boundary="N1GIdlSm9i+YlY4t"
Content-Disposition: inline
In-Reply-To: 
 <CAMkU=1zFFxF74BVpwbsT1s-yY-KmW8Ya6CZkM0PDfvZ8uupF9A@mail.gmail.com>
Precedence: bulk


--N1GIdlSm9i+YlY4t
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Jan 16, 2018 at 09:18:25PM -0800, Jeff Janes wrote:
> Oh.  I've not seen that before.  But then again I don't often restart my
> server and then immediately run very large queries with a stringent time
> deadline.
>=20
> You can try pg_prewarm, on pg_statistic table and its index.  But I'd
> probably just put an entry in my db startup script to run this query
> immediately after startng the server, and let the query warm the cache
> itself.
>=20
> Why do you restart your database often enough for this to be an issue?

Another thing that you could use here is pg_buffercache which offers a
way to look at the Postgres shared buffer contents in real-time:
https://www.postgresql.org/docs/current/static/pgbuffercache.html

As Jeff says, pg_prewarm is a good tool for such cases to avoid any kind
of warmup period when a server starts..
--
Michael

--N1GIdlSm9i+YlY4t
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCAAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAlpf/pQACgkQnvQgOdby
QH1GHg/9FoGt8kjKRW1G0ZskfjArJJ5Go4/wMZfiCYcoJ2Vx6xCLNPv5ccsxev02
VCLL29ta23u7Gj60P+jf3xUqEhkJdzGd3TnKHnLxp1dtXH1dnHQbyZGb5t0NGg59
UIrzIVq1vGEqpsc/VLEK4RLQ9HkUrUjIncmVRkjJiwWjkWehvumfExNzxDxjC1ww
XelrYQC07bLnY5dP3WnoduIWuz+uuHyJj0hWBMSVRafFj58jj0k1F7Ii5iSyWtRt
7/QDnptozkoWGcW3GyBNVA34ChKtdY1eTwCKgDBvcm9+Bvho1OqN3bFTNr4pCpaZ
SvxAuLqHvkTccmyCRy5QcH7TFbRQp0vfM+4rHHjBd2HQ2lJJ3IyDJUf4ky2nEKXr
1Tye6CL4c8nLKErZX3HDjng1sozU7mKQHw8XDqhGkQnHowlXlq21gBtewiEU5SnS
8MrTL+U4xyzS822wnro9mm86ehAgwpVB4Rrum1jKISf8SJvD9Dw4nmdiLXuwOtCO
c2clQEh9Ae0q3jqZtic34XhZDiO7sXcEm9KCeaj637UbJr+vG5Svf3Oxregdj294
RqBHodu6RDhCdJJ9gUZqGb1SLEo503XAWeJu+Malij/Dvs+sZq9pkWUOoOEjmJNS
4EsdeIB8IW7eO01mo8wsnoZ9WhQwCWjLXrxm7AXFXzTvG7aq6ZU=
=gaz+
-----END PGP SIGNATURE-----

--N1GIdlSm9i+YlY4t--