I have an ever growing read-only geometry table of many point events (+1.2M). This db table has corresponding spatial indices to speed querying up & was also spatially partitioned by geo-hash to reduce volume of irrelevant data. I created several db views on this table to filter rows by event type. On the PostgreSQL side basic queries for large number of records on the views return in "reasonable" runtimes (around 10K in 20 sec). I assigned a QGIS layer to each event view to visualize this dataset on the map. For speedup, the QGIS layers are configured to not check for unicity & estimating meta data.
QGIS 3.x projects with these layers of db views is excessively slow. Is this the recommended setup? I'm not sure what QGIS's estimating metadata setting does - perhaps its trying to estimate meta extents on my dynamic data every time? Is the use of
PostgreSQL 13.x views or partitioning a problem here?
Here is a distilled subset of my setup. The view used by QGIS layers links between two tables: each event to a possibly reusable location geometry to save space.
CREATE VIEW events_viewX(id, geom) AS SELECT events.id, locations.geom -- of type Geometry(GEOMETRY, 3347) FROM events -- partition parent table JOIN locations ON events.location_id = locations.id AND events.type = 'X';
I do not see PostgreSQL side queries while QGIS project is idle loading, but invoking the view over its extent like this yields:
EXPLAIN SELECT * FROM events_viewX WHERE geom && ST_MakeEnvelope( -19633036.30776, -38744182.68524, 30470056.55624, 20457347.97000, 3347 ); Gather (cost=63416.82..3122575.41 rows=1589954 width=1436) Workers Planned: 2 -> Parallel Hash Join (cost=62416.82..2962580.01 rows=662481 width=1436) Hash Cond: (events.location_id = locations.id) -> Parallel Append (cost=8073.72..2798506.07 rows=662481 width=386) -> Parallel Bitmap Heap Scan on events_part_gf2 events_38 (cost=8214.64..663786.27 rows=100142 width=460) Recheck Cond: ((type)::text = 'X'::text) -> Bitmap Index Scan on events_part_gf2_type_idx (cost=0.00..8154.55 rows=240340 width=0) Index Cond: ((type)::text = 'X'::text) -> Parallel Bitmap Heap Scan on events_part_gc3 events_9 (cost=8073.72..532150.86 rows=85684 width=293) Recheck Cond: ((type)::text = 'X'::text) -> Bitmap Index Scan on events_part_gc3_type_idx (cost=0.00..8022.31 rows=205641 width=0) Index Cond: ((type)::text = 'X'::text) [.. TRUNCATED ..] -> Parallel Seq Scan on events_part_gdp events_33 (cost=0.00..489780.38 rows=282948 width=381) Filter: ((type)::text = 'X'::text) -> Parallel Seq Scan on events_part_gfn events_54 (cost=0.00..7.34 rows=27 width=339) Filter: ((type)::text = 'X'::text) [.. TRUNCATED ..] -> Parallel Hash (cost=17463.55..17463.55 rows=223724 width=1220) -> Parallel Seq Scan on locations (cost=0.00..17463.55 rows=223724 width=1220) Filter: (geom && '0103000020130D00000100000005000000C095ECC438B972C11C5F7BB5837982C1C095ECC438B972C1B81E853F78827341EA5BE688FA0E7D41B81E853F78827341EA5BE688FA0E7D411C5F7BB5837982C1C095ECC438B972C11C5F7BB5837982C1'::geometry)
Turns out most of the slow QGIS loading time is due to the temporal-controller issuing the following queries to find the view's min/max timestamps for each layer & due to partitioning my temporal indices don't get used by Postgres query planner.
SELECT "start_time"::text FROM ( SELECT min("start_time") AS "start_time" FROM "my_schema"."events_viewX" ) foo; SELECT "finish_time"::text FROM ( SELECT max("finish_time") AS "finish_time" FROM "my_schema"."events_viewX" ) foo;
I wonder if the temporal-controller has a setting to avoid or pre-compute these. Or if I can somehow force use or restructure my temporal indices.