[Qgis-user] Adding feature to PostGIS layer that has joins is really slow

[Nuno Lopes]

Hello, I have already posted this on
https://gis.stackexchange.com/questions/360171/adding-feature-to-postgis-layer-that-has-joins-is-really-slow
, just posting here to see if someone else has ideas.

I have a large project that I've been setting up to work with QGIS 3.12 and
I have now hit a problem with using "joins" to join several attribute
tables with a geometric one.

I have reduced the problem to a minimal setup that has only two layers with
a one to one (or zero) relation:

 1. `point_layer` - a layer with a geometry (points) that has 64k entries
 2. `attribute_layer` - a layer with one text field and a foreign key to
the `point_layer`

On the `point_layer` I added a join to the `attribute_layer` so that we can
edit the attributes directly in the feature form.

After doing this whenever I try to create a point on the `point_layer` **it
takes more than 1 min to create the feature**.

By examining the PostgreSQL log I found out that QGIS is querying each
feature on the `point_layer` for it's relation on the `attribute_layer`.
These queries are made one by one, so even though I'm running the database
on my own computer, the sheer number of queries leads to very poor
performance.

I've already tried tweaking the cache config on the joins, hiding the form
on add feature and enabling the "automatically create transaction groups
where possible" to no avail.

I've also tested older QGIS versions 3.4 and 3.8 without success.

Am I doing anything wrong? Is there any way to work around this?

The schema used is:

    CREATE TABLE point_layer (
        ogc_fid integer PRIMARY KEY,
        geom public.geometry(GeometryZ,3763),
        height double precision
    );

    CREATE INDEX point_layer_geom_idx ON point_layer USING gist (geom);

    CREATE TABLE attribute_layer (
        id SERIAL PRIMARY KEY,
        fid integer REFERENCES point_layer(ogc_fid),
        some_value text
    );

The query that is made repeatedly is (the fid changes):

    SELECT "id","some_value"::text,"fid"::text
    FROM "public"."attribute_layer"
    WHERE ("fid" = ?) LIMIT 1

The explain analyze is (the table is empty):

                                                      QUERY PLAN


---------------------------------------------------------------------------------------------------------------
     Limit  (cost=0.00..4.17 rows=1 width=68) (actual time=0.014..0.014
rows=0 loops=1)
       ->  Seq Scan on test_relation  (cost=0.00..25.03 rows=6 width=68)
(actual time=0.011..0.011 rows=0 loops=1)
             Filter: (fid = 29853)
     Planning Time: 0.506 ms
     Execution Time: 0.086 ms

Thanks
--
Nuno Lopes
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20200502/a06b1991/attachment.html>