Re: Enumeration of tables is very slow in largish database

2012/1/11 Kirill Müller <kirill(dot)mueller(at)ivt(dot)baug(dot)ethz(dot)ch>:
> 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?

log_min_duration_statement = 0 in postgresql.conf and after reload, pg
logs all query to log

Regards

Pavel

>
>
>
> 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(dot)mueller(at)ivt(dot)baug(dot)ethz(dot)ch
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general