4

My point postgresql table has about 500k rows and about 20 columns

People have about 5 fields that they update, the other fields are static

Selecting the points is fairly instant, however using the modify selected features, changing 2-3 fields and then hitting OK, it freezes QGIS for a few minutes. Many users are experiencing the same thing

Is QGIS just unable to cope with large tables or am I missing something in terms of optimization?

Saving changes onto the layer is fairly quick, less than 10 seconds.

  • 1
    QGIS necessarily has a more involved way to interact with it's DB data sources; this is likely better implemented on the DB side (e.g. using triggers). PostgreSQL generally benefits from narrow tables and a relational data model; I'd suggest to separate static from dynamic data into linked tables and have users work with and update a View instead. UPDATEs are resource heavy in comparison, as the transitional state of the old and new table data has to be saved until the transaction commit. with triggers, you can fine tune what and how much data will be hold in transition. – ThingumaBob Nov 1 '19 at 11:31
  • 1
    Do you happen to also have the table open? If yes it might help to have it limited to features that are on the display. (note: a test on a 2M rows table takes minutes to open the table, but the Modified Selected Feature is instantaneous) – JGH Nov 1 '19 at 11:43
  • @JGH due to the nature of the data, people usually won't have more than 50k features showing up at a time. When they need to do updates they will zoom in and this figure will frequently reduce to 10k. Spatial index works pretty decent – Luffydude Nov 1 '19 at 11:59
  • 1
    No, I was referring to the attribute table. By default, when you display the geometries, a spatial filter is applied and only the relevant subset of the table is returned to QGIS from the DB. For the attribute table, by default ALL rows are returned to QGIS. If you choose to only show (in the attribute table) the features whose geometries are on the map display, then the spatial filter is also applied to the rows in the attribute table, which then loads much faster (menu settings/options/data source/ attribute table behavior) – JGH Nov 1 '19 at 12:59
  • 1
    Regarding the spatial view, the index that exists on the underlying table should automatically be used when querying the view – JGH Nov 1 '19 at 13:00
6

Expanding on my suggestion to use Views; the core improvement is the splitting of the table:

A View is a stored query, and any index of the base tables can be accessed (there are exceptions). There are two types of Views when it comes to updates:

  • atomatically updateable Views
  • complex/composite Views

The first type needs to be a simple, non-aggregating SELECT directly on tables, or a similarly simple sub-query, where each column expression can be directly mapped to a base table column, e.g. (note that I assume you have split your table into <static_data> & <dynamic_data>):

CREATE VIEW <view> AS
  SELECT a.<static_attribute>,
         b.<dynamic_attribute>,
         b.<dynamic_geom>
  FROM   <static_data> AS a
  JOIN   <dynamic_data> AS b
  USING  (<PKEY>)
;

This is likely all you need. All indexes on both tables are used, and UPDATEs work just as on a normal table.

This is simple and practical, and would benefit from splitting the tables, as then only a single table with a few columns has to be updated (and its data hold in a transitional state). In any case, it should (untested) boost reaction time in QGIS.

Only if you plan to have more complex View definition it is necessary to have an INSTEAD OF trigger in place to reroute data to the respective base tables (probably with intermediate pre-processing).


It could be beneficial to have a trigger on your base table with a ON BEFORE UPDATE OF <dynamic_column_a>, <dynamic_column_b> statement and a WHEN (OLD.* IS DISTINCT FROM NEW.*) filter that handles the actual UPDATE for when its conditions are met (and only then).

  • I thought I had written a comment before. Your answer works like a charm, thanks! In order to speed things up, I've kept the static data as a join in QGIS itself, so the view is actually a lot smaller – Luffydude Nov 4 '19 at 10:57

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.