2

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?

UPDATE: As requested by vince here is the table schema:

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
  • There really isn't any information in this question that could be used to diagnose the issue. No table defintions. No CREATE INDEX statements. No CREATE VIEW. No query plan of the generated queries. No way to tell what is wrong. – Vince Apr 30 at 23:42
  • @Vince I added the query plans but I believe the problem lies in the fact that qgis is making lots of queries, not in the performance of each single query. If you think there's a chance you can help I think this can be generated randomly to the same effect. I can do that... – NuLo May 1 at 0:20
  • Did I miss it? Where is the CREATE VIEW? – BJW May 2 at 23:56
  • @BJW I am not using any view, this is just a basic example of a performance problem I have with using joins in QGIS. In the final use case there will be multiple tables with relations of 1: 0 - 1 . These tables have mandatory and optional fields so if you can see any other way to do this in QGIS I would be very thankfull – NuLo May 3 at 11:33
  • I have now opened a bug on QGIS will close this when the bug is fixed – NuLo May 5 at 18:24
0

This has now been solved in qgis 3.14 by closing this bug

| improve this answer | |

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.