Speedup PostgreSQL views with QGIS layers

Question

SETUP

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.

PROBLEM

Loading 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?

DETAILS

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)

---

UPDATE

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.

Answer 1

The view is not optimized for QGIS, because QGIS needs to scan the entire table to find out the geometry type and projection. Instead, you can cast the geometry column to the desired type/crs.

CREATE VIEW events_viewX(id, geom) AS 
SELECT
   events.id,
   locations.geom::geometry(POINT, 3347) as geom
FROM ...

Also you say that "Loading QGIS 3.x projects with these layers of db views is excessively slow.", which reveals that the query itself is fine - else QGIS would be slow each time you pan the map, not only when loading the project.

Answer 2

Another postgres option you might want to consider is creating Materialized Views. They get about the same performance as tables, but are still connected to the underlying tabular data. The downside is you need to "refresh" the materialized views periodically, it doesn't happen automatically like a traditional View does.

I have had great success with these, and highly recommend trying them out if you have data that can be refreshed on-demand.