As stated here, I am trying to load a virtual layer from two existing PostGIS tables, using a simple
LEFT JOIN statement:
SELECT t.id, t.geometry
FROM table_t t
LEFT JOIN table_f f
ON f.fk_poly_id = t.id
WHERE fk_i_id = 10;
table_f is a join table containing an
id, and two foreign keys:
table_t is a standard table containing an
id and the
poly geometry that I'd like to see in my virtual layer for a feature
i (referenced by its foreign key
fk_i_id in the join table). This layer of polygons has roughly ~20'000'000 quite simple features, but loading the whole layer at once is painful and laggy. That's precisely why I'm searching to build a virtual layer: to limit the number of features arriving into QGIS and letting PG manage this.
This query is perfectly working in
psql or pgAdmin4 and returns me a few geometric features (ST_Polygon -
POLYGON Z) along with their ids.
But when I want to either "Test" or "Add" the virtual layer, QGIS becomes unresponsive and I finally have to force it down by killing the process.
After some tests I've noticed that testing the query without the
WHERE statement is giving encouraging results:
But when trying to actually "Add" the layer, QGIS freezes again (I tried to wait ~20min or so but everything stays frozen with a gray background, where the query returns less than 10 small polygons within psql or pgAdmin4).
On the other hand, when the
WHERE statement is present, pressing the "Test" button itself makes QGIS unresponsive, the "No error" popup window never comes on the screen and I have to kill the program.
This behaviour is the same in every cases; having the "Embedded Layers" selected or not in the upper part of the Virtual Layer window but in every cases, the required layers are already loaded in the table of content of my project.
Any hints on what's going on with this issue? Is this a know bug or am I doing something wrong?
Here is the version information:
|QGIS code revision
|EPSG Registry database version
|PostgreSQL client version
|Ubuntu 22.04 LTS