Re: response time is very long in PG9.5.5 using psql or jdbc

On 02/13/2018 10:31 PM, Andres Freund wrote:
> Hi,
>
> On 2018-02-13 13:24:47 -0800, David Gould wrote:
>> I see this problem fairly frequently. Typically the problem catalog is
>> pg_attribute as it has the most rows. However the problem really arises when
>> the catalogs become bloated. Once the total size of the catalogs that get
>> read at start up approaches the size of shared buffers, and especially if
>> several processes start at the same time, it becomes quite noticeable.
>
> I can obviously see problems with a bloated catalog, but how does that
> cause massive slowdowns in *individual* queries as we see here? The
> OP's problem isn't the connection establishment performance afaict, but
> the first query using specific pieces of catalog data. And that should
> be ok-ish performancewise, even if there's bloat.
>

What if the first connection does not actually go to PostgreSQL, but to
some sort of connection pool (like pgbouncer)? That would be consistent
with the observed behavior, I think - the first connection would likely
have to open connection to a backend, paying all the price.

Obviously, this is just a stab in the dark ...

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services