<div dir="ltr"><div>Hi Chris, thanks for the suggestions.</div><div>I have opened a bug with QGIS that was accepted <a href="https://github.com/qgis/QGIS/issues/36167">https://github.com/qgis/QGIS/issues/36167</a> .</div><div>In the meantime I'l have to go with one of the alternatives (still figuring out which one is best for my case)</div><div><br></div><div>Thanks again,</div><div>Nuno Lopes<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, May 3, 2020 at 5:02 PM chris hermansen <<a href="mailto:clhermansen@gmail.com">clhermansen@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="auto"><div>Nuno and list<br><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, May 3, 2020, 04:50 Nuno Lopes <<a href="mailto:nlopes@gmail.com" target="_blank">nlopes@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>Hi Chris and list,</div><div><br></div><div>This is the minimal setup where I could reproduce my performance issue. The final idea is to have multiple 1:0-1 tables that have different mandatory and optional fields. So putting all this in a single table would be very difficult (I can see it done using triggers and checks on the database but UX would be bad)</div></div></blockquote></div></div><div dir="auto"><br></div><div dir="auto">Normally one would allow nulls for optional attributes rather than separate them into different tables.</div><div dir="auto"><br><span style="font-family:sans-serif">"UX would be bad" - how so? If you are building CRUD forms they can view a semantic type field on the table and respond to that.</span><br></div><div dir="auto"><br></div><div dir="auto">Another possibility with PostgreSQL if the nature of your problem is the kind of thing where the semantic type of point is different (retail stores, clinics, churches, service stations, post offices, etc) resulting in a different collection of attributes in each case is to use table inheritance (composition). Then the spatial table with the semantic type indicator is the base "class" and all the different types are built as extensions from that.</div><div dir="auto"><br></div><div dir="auto"><br></div><div dir="auto"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div><br></div><div>Unfortunately the index doesn't solve my problem, because in this minimal setup the table is empty and I get this performance issue. This problem exists with a database on my own computer, if I move the database to a server (even a close one) QGIS will freeze for so much time that I have to kill it.</div></div></blockquote></div></div><div dir="auto"><br></div><div dir="auto">Ok, so you have to get rid of the separate query for each point then.</div><div dir="auto"><br></div><div dir="auto">Which to me means letting PostgreSQL manage the table joins, using a view or using composition or just putting the attributes on your table since they are 1:0-1 which can be managed by null values.</div><div dir="auto"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div><br></div><div>Thanks a lot,</div><div>Nuno Lopes<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, May 3, 2020 at 12:43 AM chris hermansen <<a href="mailto:clhermansen@gmail.com" rel="noreferrer" target="_blank">clhermansen@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr">Nuno and list<br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sat, May 2, 2020 at 11:58 AM Nuno Lopes <<a href="mailto:nlopes@gmail.com" rel="noreferrer" target="_blank">nlopes@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>Hello, I have already posted this on <a href="https://gis.stackexchange.com/questions/360171/adding-feature-to-postgis-layer-that-has-joins-is-really-slow" rel="noreferrer" target="_blank">https://gis.stackexchange.com/questions/360171/adding-feature-to-postgis-layer-that-has-joins-is-really-slow</a> , just posting here to see if someone else has ideas.</div><div><br></div><div>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.<br><br>I have reduced the problem to a minimal setup that has only two layers with a one to one (or zero) relation:<br><br> 1. `point_layer` - a layer with a geometry (points) that has 64k entries<br> 2. `attribute_layer` - a layer with one text field and a foreign key to the `point_layer`<br><br>On the `point_layer` I added a join to the `attribute_layer` so that we can edit the attributes directly in the feature form.<br><br>After doing this whenever I try to create a point on the `point_layer` **it takes more than 1 min to create the feature**.<br><br>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.<br><br>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.<br><br>I've also tested older QGIS versions 3.4 and 3.8 without success.<br><br>Am I doing anything wrong? Is there any way to work around this?</div><div><br></div><div>The schema used is:<br></div><div><br> CREATE TABLE point_layer (<br> ogc_fid integer PRIMARY KEY,<br> geom public.geometry(GeometryZ,3763),<br> height double precision<br> );<br> <br> CREATE INDEX point_layer_geom_idx ON point_layer USING gist (geom);<br> <br> CREATE TABLE attribute_layer (<br> id SERIAL PRIMARY KEY,<br> fid integer REFERENCES point_layer(ogc_fid),<br> some_value text<br> );<br><br>The query that is made repeatedly is (the fid changes):<br><br> SELECT "id","some_value"::text,"fid"::text <br> FROM "public"."attribute_layer" <br> WHERE ("fid" = ?) LIMIT 1<br><br>The explain analyze is (the table is empty):<br><br> QUERY PLAN <br> ---------------------------------------------------------------------------------------------------------------<br> Limit (cost=0.00..4.17 rows=1 width=68) (actual time=0.014..0.014 rows=0 loops=1)<br> -> Seq Scan on test_relation (cost=0.00..25.03 rows=6 width=68) (actual time=0.011..0.011 rows=0 loops=1)<br> Filter: (fid = 29853)<br> Planning Time: 0.506 ms<br> Execution Time: 0.086 ms<br></div><div><br></div><div><br></div><div><br></div></div></blockquote><div>Why not just put your attributes on the same table as the spatial one?</div><div><br></div><div>If you don't like that idea you could consider putting an index on the fid on the attribute_layer. But it's odd to me to see the select on the attribute_layer table given that your fid in your attribute_layer is a foreign key referencing the ogc_fid field in point_layer.<br></div></div><br clear="all"><br>-- <br><div dir="ltr"><div dir="ltr">Chris Hermansen · clhermansen "at" gmail "dot" com<br><br>C'est ma façon de parler.</div></div></div>
</blockquote></div><br clear="all"><br>-- <br><div dir="ltr"><div dir="ltr"><div>--<br></div>Nuno Lopes<br></div></div>
</blockquote></div></div></div>
</blockquote></div><br clear="all"><br>-- <br><div dir="ltr" class="gmail_signature"><div dir="ltr"><div>--<br></div>Nuno Lopes<br></div></div>