Skip site navigation (1) Skip section navigation (2)

Re: Slow connect due to some queries

From: Guillaume Lelarge <guillaume(at)lelarge(dot)info>
To: Tzvi R <sefer(at)hotmail(dot)com>
Cc: pgadmin-support(at)postgresql(dot)org
Subject: Re: Slow connect due to some queries
Date: 2010-01-14 20:45:59
Message-ID: 4B4F8287.4010605@lelarge.info (view raw or flat)
Thread:
Lists: pgadmin-support
Hi,

Le 13/01/2010 17:16, Tzvi R a écrit :
> [...]
> I've been using pgAdmin for a long time but over time it has become more and more frustrating to use it.
> It's a great application that performs everything I need, however, when first connecting to servers with many databases, each with many tables, views and other objects (over a VPN) it can often take 20-30 seconds while pgAdmin is hung before it completes running all the prefetch queries.
> 

Actually, pgAdmin only looks at your maintenance database or at your
last connected database.

How many databases/objects do you have?

> I've analyzed its logs and it appears that some queries are the main culprit.
> 
> For example (15-20 seconds to run this):
> SELECT oid, format_type(oid, NULL) AS typname FROM pg_type
> 
> We have quite a few entries there (every toasted table, every view and every table have a couple of entries there).
> SELECT count(*) AS typname FROM pg_type
> 35468
> 

That, for sure, is a huge number. A newly created database only have 283
types (on 8.4).

> Also, this same query gets apparently issued twice:
> 2010-01-13 09:44:15 QUERY : Set query (pgdb:5432): SELECT oid, format_type(oid, NULL) AS typname FROM pg_type
> 2010-01-13 09:44:31 QUERY : Set query (pgdb:5432): SELECT oid, format_type(oid, NULL) AS typname FROM pg_type
> 2010-01-13 09:44:52 STATUS : Restoring previous environment... (37.31 secs)
> 
> Causing the application a long time to "connect".
> I was wondering if there's either a way to bring a partial subset of the data (is all of it needed?) or perhaps fetch it lazily (on demand).
> 

Partial subset, no. On demand... don't know.

What I do know is that we have a fix for the 1.12 release that will make
you happy:

Cache datatypes in dlgTable, so they don't have to be
reloaded by dlgColumn, which may be used multiple times
when creating a table [Sachin Srivastava].

We still load all pgtypes, but we use a cache to stop loading all the
data each time a new column is added;


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

In response to

Responses

pgadmin-support by date

Next:From: Guillaume LelargeDate: 2010-01-14 20:47:05
Subject: Re: can't get pgpass.conf to work with pgagent
Previous:From: Guillaume LelargeDate: 2010-01-14 20:36:56
Subject: Re:

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group