Re: Enumeration of tables is very slow in largish database

On 01/11/2012 02:44 PM, Andres Freund wrote:
> On Wednesday, January 11, 2012 11:07:23 AM Kirill Müller wrote:
>> Hi all,
>>
>> we have a Postgres/PostGIS database with 100+ schemas and 200+ tables in
>> each schema, generated automatically. When adding a new PostGIS layer in
>> QGis, the application obviously enumerates all tables, and this takes
>> minutes. Even browsing the database in pgAdmin3 is horribly slow -- it
>> takes several seconds to e.g. open a schema (click on a schema's "+" in
>> the tree view).
> Are you actually sure its the database and not just pgadmin thats getting
> really slow?
>
> If you connect via psql and use \dt (see \? for a list of commands) and
> consorts, is it that slow as well?
\dt is quick, I haven't tried the other commands, though. I have built
qgis from source and will try to find out this way which query stalls. I
wonder if Postgres has a profiling tool like MS SQL Server that would
allow tracing the queries and their runtime while they are executed. Or
perhaps there are logs? Could you give me some pointers, please?


Kirill

--
_________________________________________________
ETH Zürich
Institute for Transport Planning and Systems
HIL F 32.2
Wolfgang-Pauli-Str. 15
8093 Zürich

Phone:       +41 44 633 33 17
Fax:         +41 44 633 10 57
Secretariat: +41 44 633 31 05
E-Mail:      kirill.mueller@ivt.baug.ethz.ch