Re: Query is slow when run for first time; subsequent execution is fast

Hi,

I tried pg_prewarm as suggested by Jeff Janes and it works - thanks a lot
Jeff. Now the query planning is fast on the first execution.

Here is the list of tables that needed to be pre warmed (or you could just
pre warm all the 'pg_%' tables. :-) ).

select pg_prewarm('pg_statistic');
select pg_prewarm('pg_trigger_tgrelid_tgname_index');
select pg_prewarm('pg_trigger');
select pg_prewarm('pg_statistic_relid_att_inh_index');
select pg_prewarm('pg_index_indrelid_index');
select pg_prewarm('pg_index_indexrelid_index');
select pg_prewarm('pg_index');
select pg_prewarm('pg_constraint_conrelid_index');
select pg_prewarm('pg_constraint');
select pg_prewarm('pg_class_relname_nsp_index');
select pg_prewarm('pg_class_oid_index');
select pg_prewarm('pg_attribute_relid_attnum_index');
select pg_prewarm('pg_attribute');
select pg_prewarm('pg_attrdef_adrelid_adnum_index');
select pg_prewarm('pg_attrdef');
select pg_prewarm('pg_amproc_fam_proc_index');
select pg_prewarm('pg_namespace_oid_index');

Regards,
Nanda

On 18 Jan 2018 07:25, "Michael Paquier" <michael(dot)paquier(at)gmail(dot)com> wrote:

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.
>
> 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.
>
> 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