2

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.

7
  • 2
    I regularly work with unpartitioned tables with orders of magnitude more points, and rarely have queries returning 10k features take more than 2 seconds. Maybe you would be better off partitioning by event type and clustering by geohash instead.
    – Vince
    Dec 28 '21 at 16:40
  • 2
    I guess you may be right that QGIS is checking some metadata. Turn on statement logging on the PostgreSQL side and check what queries QGIS makes. Optimizing is easier when you know what really happens.
    – user30184
    Dec 28 '21 at 17:06
  • 1
    Please post the EXPLAIN ANALYZE of a typical SELECT on one of those Views, and, if you can, table structure and View creation statement.
    – geozelot
    Dec 28 '21 at 20:07
  • 1
    Your partitioning scheme is, and will be, highly ineffective; as it stands no partition pruning can be faciliated, and likely never can with the given setup and query requirements (outside of implementing PL/pgSQL functions to prepare statements). You'd do yourself a favor with either no partitioning, having only a single table for all data (potentially with partitioning on a different dimension, e.g. time) or an identical partitioning on both tables by event_type (with less but still plenty of complication).
    – geozelot
    Dec 30 '21 at 12:49
  • 1
    Consider that the benefit of partitioning will ever only show for tables of which a high cardinality index cannot fit into the available memory. Spatial partitioning is especially difficult to implement for the common case of multi-dimensional range filters (i.e. when trying to adress PostGIS' GIST index).
    – geozelot
    Dec 30 '21 at 13:02
2

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.

1
  • Thanks @JGH! Yes, QGIS panning is jerky but not as bad as loading perhaps because DB scans only adjacent partitions. I had a hunch it was the generic GEOMETRY type! Will post back with results.
    – eliangius
    Dec 29 '21 at 14:13
1

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.

2
  • Good to know. Out of curiosity I assume materialized views copy their base data right?
    – eliangius
    Dec 29 '21 at 17:01
  • 1
    I'm not quite sure what you mean by "copy their base data", but this is a good explanation of ho wmat. views work: enterprisedb.com/postgres-tutorials/… Dec 31 '21 at 5:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.