I have two very similar datasets of 27 million polygons stored in PostgreSQL / PostGIS.
One is loaded as a single table, with spatial indexes and regular indexes on key fields.
The other is partitioned based on a grid of geographic areas. Each parition has a spatial index and regular indexes, and has been configured in accordance with the user manual Partitioning guidance; http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html
If I load the single table into QGIS, it loads data into the map window and starts rendering polygons very quickly (when zoomed in to 1:10000).
If I use the master partitioned table and do the same, it takes over 8 minutes before it starts to display data. After the 8 minutes it then is able to render the data at a similar speed to the single table as I zoom and pan.
I am really looking for a way to resolve this 8 plus minutes, as QGIS appears not be reading the partitioned table efficiently. I have thought it may be due to; - QGIS not picking up any spatial index - QGIS loading the entire 27 million records into its cache / memory - QGIS not getting the correct bounding box for my window view and loading all the data in
Am I missing some setting or configuration? I have loaded it within GeoMedia without issue, and performed a bounding box query within FME on both the single table and partitioned (both performed at similar speeds). QGIS can connect to PostgreSQL / PostGIS and get the schema / table listings quite quickly with 'Use estimated table metadata' checked. My issue is adding it to the map.
UPDATE 2016/05/18 15.20
On advice from user30184 I enablled PostgreSQL logging to identify the queries hitting the database server.
They showed the following;
For the Single table;
2016-05-18 14:47:23 BST QGISLOG: statement: SELECT st_estimatedextent('schema1','table1','geometry')
2016-05-18 14:47:24 BST [unknown]LOG: statement: SET application_name='QGIS'
2016-05-18 14:47:24 BST QGISLOG: statement: BEGIN READ ONLY
2016-05-18 14:47:24 BST QGISLOG: statement: DECLARE qgis_1 BINARY CURSOR FOR SELECT st_asbinary("geometry",'NDR'),"primaryindex" FROM "schema1"."table1" WHERE "geometry" && st_makeenvelope(minx,miny,maxx,maxy,27700)
2016-05-18 14:47:24 BST QGISLOG: statement: FETCH FORWARD 2000 FROM qgis_1
2016-05-18 14:47:32 BST QGISLOG: statement: FETCH FORWARD 2000 FROM qgis_1
2016-05-18 14:47:32 BST QGISLOG: statement: FETCH FORWARD 2000 FROM qgis_1
2016-05-18 14:47:32 BST QGISLOG: statement: CLOSE qgis_1
2016-05-18 14:47:32 BST QGISLOG: statement: COMMIT
For the Partitioned table;
2016-05-18 14:49:57 BST QGISLOG: statement: SELECT st_extent("geometry") FROM "schema2"."table2"
2016-05-18 14:58:23 BST [unknown]LOG: statement: SET application_name='QGIS'
2016-05-18 14:58:23 BST QGISLOG: statement: BEGIN READ ONLY
2016-05-18 14:58:23 BST QGISLOG: statement: DECLARE qgis_1 BINARY CURSOR FOR SELECT st_asbinary("geometry",'NDR'),"poly_id" FROM "schema2"."table2" WHERE "geometry" && st_makeenvelope(minx,miny,maxx,maxy,27700)
2016-05-18 14:58:23 BST QGISLOG: statement: FETCH FORWARD 2000 FROM qgis_1
2016-05-18 14:58:24 BST QGISLOG: statement: FETCH FORWARD 2000 FROM qgis_1
2016-05-18 14:58:24 BST QGISLOG: statement: FETCH FORWARD 2000 FROM qgis_1
2016-05-18 14:58:24 BST QGISLOG: statement: CLOSE qgis_1
2016-05-18 14:58:24 BST QGISLOG: statement: COMMIT
The thing I noticed is that the Single table is using an estimated extent (st_estimatedextent) whereas the Partitioned table is using the full (st_extent). I suspect this is where the time delay is coming in. In pgAdminIII I ran the query st_estimatedextent in a worksheet for the Single table and it brought back a result instantly, whereas for the Partition it had the following;
ERROR: stats for "nds.geometry" do not exist ********** Error **********
ERROR: stats for "nds.geometry" do not exist SQL state: XX000
I guess it hasn't picked up the stats on the child partitions at all. I Analysed the master partition table, but that hasn't appeared to have populated any statistics. Any thoughts?