10

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) basic form

and one of the advanced ones: Advanced form panel 1

advanced form panel 2

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!

  • 1
    Are these polygon features, and how many attributes does your table have (just to create a comparable sample data set...)? – Jochen Schwarze Jan 27 at 10:04
  • 2
    Can you share a screenshot of a form that takes long to open up? – Matthias Kuhn Jan 27 at 11:50
  • 2
    Could you show us the db log? – Michal Zimmermann Jan 27 at 13:13
  • 2
    I think you are on to something. I didn't think the log would say anything aside from "Fetch from qgis_201" or something. But It showed me the following query (added above). It is casting the primary key to a text field for some reason! Disregarding the index – Lennert De Feyter Jan 27 at 14:02
  • 1
    Have you filed a bugreport on the QGIS Issue Tracker? I feel like this is something they'd want to know, especially since they're working over the database providers and this would be a huge win for QGIS' database performance/capabilities. github.com/qgis/QGIS/issues – Saijin_Naib Jan 27 at 16:36
11

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

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.