Re: Query is slow when run for first time; subsequent execution is fast - Mailing list pgsql-performance

From Jeff Janes
Subject Re: Query is slow when run for first time; subsequent execution is fast
Date
Msg-id CAMkU=1yiULP9Gdy1pT2bpVomi4cinNGz8d7-7y+chBOQFg3f6Q@mail.gmail.com
Whole thread Raw
In response to Query is slow when run for first time; subsequent execution is fast  (Nandakumar M <m.nanda92@gmail.com>)
Responses Re: Query is slow when run for first time; subsequent execution is fast
List pgsql-performance
On Wed, Jan 10, 2018 at 3:59 AM, Nandakumar M <m.nanda92@gmail.com> wrote:

I am not using prepared statements. Postgres documentation and previous questions in the pgsql-performance mailing list mention that the query plan is cached only when prepared statements are used.

https://www.postgresql.org/message-id/15600.1346885470%40sss.pgh.pa.us

In the above thread Tom Lane mentions that the plan is never cached for raw queries. Yet, this is exactly what seems to be happening in my case. Am I missing something?

The query plan itself is not cached, but all the metadata about the (large number) of tables used in the query is cached.  Apparently reading/parsing that data is the slow step, not coming up with the actual plan.
 
> Please let me know how I can make sure the query execution for the first time is fast too.

Don't keep closing and reopening connections.  Use a connection pooler (pgbouncer, pgpool, whatever pooler is built into your language/library/driver, etc.) if necessary to accomplish this.

Cheers,

Jeff

pgsql-performance by date:

Previous
From: ghiureai
Date:
Subject: Re: PG 9.5 2 tables same DDL with diff size
Next
From: "POUSSEL, Guillaume"
Date:
Subject: Slow queries after Windows startup