QGIS form performance

Question

Edit: tl;dr QGis apparently casts my primary key on postgresql to text when using the identify tool. This causes the form to be slow in startup because it disregards the index on it. A (tested) quick fix is to create an extra index like: create index on belmap.address USING btree (cast(stableid as text));

I still wonder if there is a proper solution for this.

Original post

I am using a QGIS 3.10.3 (but also tested with QGIS 3.4) directly connected to a PostgreSQL 11 with PostGIS.

I am building a project and data model on which our data operators can do asset management. The project uses several large tables (5.000.000+ rows per table, 4-7 tables). I limit the amount of features fetched via a maximum zoom setting.

I am extremely satisfied with the performance in general, browsing around the map is great. But I am surprised by the performance of the form widget.

If I use the identify tool and drag over a layer to select multiple features, I can see the attributes of the features in the identification result tab in miliseconds. But opening the form, to do actual editing, of a single feature takes +- 5 seconds. Which is a lot, because our data operators will need to open a lot of features.

There is an index on the primary key, there is a spatial index, all foreign key's are indexed, the database is vacuumed.... The only thing that I can think of of doing now is to create an action in QGIS that opens up a custom form. Oddly enough, I've done a small POC using that workaround, and that seems to be fast. But it seems a lot of work to get the form up just right, and I'd like to limit the amount of code for ease of maintenance purposes. It is slow for all form types (auto generated, drag and drop design or ui file).

Another solution that seemed to work at first, was using a GeoServer + WFS that only fetched the assets in the current screen, but that doesn't work when I try to connect relations, since it does not fetch childeren through the WFS. (note that with a direct DB connection, it goes slow, even without relations).

Does anyone have any advice?

This is an example ddl of one of the slower tables.

CREATE TABLE belmap.cadastralparcel (
    stableid int8 NOT NULL,
    id int8 NULL,
    versionid int8 NULL,
    validfrom timestamp NULL,
    validto timestamp NULL,
    inuse bool NULL,
    beginlifespan timestamp NULL,
    endlifespan timestamp NULL,
    status varchar NULL,
    nationalreference varchar NULL,
    municipality varchar NULL,
    municipality_stableid int8 NULL,
    geometry geometry(MULTIPOLYGON, 4326) NULL,
    CONSTRAINT cadastralparcel_pkey PRIMARY KEY (stableid)
);

CREATE INDEX idx_cadpar_geom ON belmap.cadastralparcel USING gist (geometry);

This table contains +- 5 million records.

Edit

Screenshots of forms Basic one (from the table above)

and one of the advanced ones:

The other panels of the advanced form are more or less the name. But contain confidential info.

EDIT 2 I've added the query that is slow here:

SELECT
  st_asbinary("original_geometry",'NDR'),
  "stableid"::text,
  "id"::text,
  "versionid"::text,
  "validfrom"::text,
  "validto"::text,
  boolout("inuse")::text,
  "beginlifespan"::text,
  "endlifespan"::text,
  "type"::text,
  "municipality"::text,
  "municipality_stableid"::text,
  "submunicipality_stableid"::text,
  "road"::text,"subaddressof"::text,
  "status"::text,
  "original_designator"::text,
  "designators"::text,
  "datasources"::text,
  array_out("replace")::text,
  st_asewkt("streetaxis_geometry"),
  st_asewkt("building_geometry"),
  st_asewkt("mailbox_geometry")
FROM
  "belmap"."address"
WHERE (
  (
    ("subaddressof" is null
     and status in ('In use','Not official','Reserved','Suggested')
    )
  )
)
AND ("stableid"::text='136981247')

It is casting the primary key to a text field! Why would it do that? it is clearly disregarding the index!

Answer 1

It seems to be a bug in QGIS that has existed for some time. I've tested versions 2.18, 3.4 and 3.10.

When using a bigint as a primary key, QGIS will cast the primary key to a text field. This causes the index on the primary key to be skipped causing a huge slow down in large tables.

This does not happen with if the primary key is of type:

• Int
• serial
• bigserial

So a workaround is to either using these field types as your primary key. Or to create an index cast to a text field if this is not possible:

create index on belmap.address USING btree (cast(stableid as text));

Bug report can be found on: https://github.com/qgis/QGIS/issues/34077

Answer 2

If you have a problem of QGIS form performance you can also check if you have relations or joined layers which are slowing the form display. In my case the form took up to 30 second to appear.

You can try to remove relations or joined layers to target the problem. In my case it was an empty joined layers.