Re: Enumeration of tables is very slow in largish database

On Wednesday, January 11, 2012 2: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).

Would seem that they both have the same issue, namely pulling over the table
names and the meta data is resource intensive.

Not a QGis user but I did find this in the manual:

http://download.osgeo.org/qgis/doc/manual/qgis-1.7.0_user_guide_en.pdf

See in-line comment:

4.2.2. Loading a PostGIS Layer

Once you have one or more connections defined, you can load layers from the
PostgreSQL database.
Of course this requires having data in PostgreSQL. See Section 4.2.4 for a
discussion on importing data into
the database.
To load a layer from PostGIS, perform the following steps:
– If the Add PostGIS Table(s) dialog is not already open, click on the Add
PostGIS Layer toolbar button.
– Choose the connection from the drop-down list and click Connect .
– Select or unselect Also list tables with no geometry
– Optionally use some Search Options to define which features to load from the
layer or use the
Build query icon to start the Query builder dialog.

^^^^^^^^^^^^^^^
Wonder if it would be possible to restrict the dataset(tables) by using the
above?

– Find the layer(s) you wish to add in the list of available layers.
– Select it by clicking on it. You can select multiple layers by holding down
the shift key while clicking.
See Section 4.6 for information on using the PostgreSQL Query Builder to further
define the layer.
– Click on the Add button to add the layer to the map.

>
> The problems occurred only after adding that many schemas to the
> database. Before, with only 10+ schemas, the performance was acceptable.
>
> Is this a known limitation of Postgres, or perhaps a misconfiguration of
> our installation? What would you suggest to improve performance here? We
> currently don't have administration rights for the database or login
> rights for the server machine (Linux), but I think we'll need to take
> care about that.
>
>
> Best regards
>
> Kirill

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com