Very poor performance of QGIS and PostGIS 2. User error or feature? [closed]

Question

I have a large table in PostGIS 2. It has >500 million polygon, line and point features all happily living in there and the table performs brilliantly in other GIS.

I am now trying to just add that table to a project in QGIS but here is the thing, why does it take so long?

When I try to 'Add PostGIS overlay' it says detecting for about four hours to tell there are three geometry types. OK, I can take that if I only have to do it once...

Once it finishes that I select all three parts click OK... So far I'm two days into trying to get it to load into the canvas.

What is going on? I am zoomed in so only a few polygons should be rendered on screen.

Is this an error or a 'feature' of QGIS? Is there something I need to specifically do to enhance the performance for QGIS?

I am using MapInfo and Cadcorp SIS both of which love the "everything in one place" model rather than the splitting it up, join it back later with queries model. And thanks @AndrewJoost, the estimated metadata saved about an hour but still it takes a few hours too long and misses one of the geometry types off the list... QGIS not supporting mixed geometries in one overlay is no problem, as long as it supports them in one table.

And the way it does this with a filter is great for small tables but just fails when you have a large one it seems.

I am unable to separate the table into three parts as this does not make sense for the other systems using the data, and would also break them.

@NathanW How can I get 3 records for a single table into the geometry_columns view now that it is no longer a physical table? any ideas? Sorry about all the newbie questions

Any other ideas to stop QGIS trying to be helpful and scanning a table before allowing me to add it? I don't mind choosing geometry types by hand for every overlay if that's what it takes?

Answer 1

To my knowledge, GIS as Arcmap or QGIS can't support layers with several primitives (point, polygon or polyline). What GIS do you use?

You can try to create tables for each type:

Point:

SELECT * INTO newtablepoints FROM initial_table 
WHERE ST_GeometryType(geom) = 'ST_Point' ;

polygon :

SELECT * INTO newtablepolygones FROM initial_table 
WHERE ST_GeometryType(geom) = 'ST_MultiPolygon' or ST_GeometryType(geom) = 'ST_Polygon' ;

polyline :

SELECT * INTO newtablelines FROM initial_table 
WHERE ST_GeometryType(geom) ='ST_MultiLineString' or ST_GeometryType(geom) = 'ST_LineString';

You can try to add a geometric index :

CREATE INDEX idx_spatial_points ON newtablepoints USING GIST (geom);
CREATE INDEX idx_spatial_polygones ON newtablepolygones USING GIST (geom);
CREATE INDEX idx_spatial_lines ON newtablelines USING GIST (geom);

It will accelerate geographic queries.

Then these three tables will be more easily support by QGIS

Answer 2

As previously mentioned, mixed Geometries in one layer are not supported by QGIS.

If you have strong use case for keeping all of the data in a single table, another option to creating new tables (as suggested by @Benno) would be to create views referencing each geometry type from the initial table:

CREATE VIEW vwPoints 
SELECT <fields> 
FROM initial_table 
WHERE ST_GeometryType(geom) = 'ST_Point'

I am not sure about is the performance implications of going with views vs. individual tables, but that is something you could easily experiment with and decide which route you want to take.

Answer 3

When setting up the Postgis connection, you can check "use estimated table metadata". This will prevent QGIS from looking through all features every time you load the layer.

Unfortunately, you can't change it later on in an easy way.

EDIT

Mixed Geometries in one layer are not supported by QGIS.