1

So, I've a table in postgresql, that is joined to other spatial table.

If I've run a simple query in pgAdmin, let's say SELECT * FROM table1 WHERE "area" > '5000' and it takes less, than 1s to output a result.


Completely different story is in QGIS.

I'm developing plugin, that needs to fetch some data by query. The code is simple, more less like this:

table1 = iface.activeLayer()

attribute = 'area'
size = 5000

q = "\"{}\" > '{}'".format(attribute, size)

expr = QgsExpression(q)
result = table1.getFeatures(QgsFeatureRequest(expr))
ids = [i.id() for i in result]

and it works just fine, but performance is the issue. It takes about 15-20s to run the same query, where in pgAdmin took less than 1s.

How is that possible? Is that normal, that QGIS is that slow?

At first, I thought about that join is making QGIS so slow, but I can't figure out, why it's not affecting pgAdmin, if that is the case?

My spec:
QGIS 3.4.8
PostgreSQL 10.7
PostGIS 2.5.2
pgAdmin 4

EDIT: Removed 'AND' typo as @Joseph mentioned.

  • PostgreSQL is a dedicated database management system which specialises in such queries. QGIS is not - so there will always be a performance gap. – Joseph Aug 22 at 12:07
  • Generally speaking, Qgis is not super fast for huge query I would say. If you have Postgressql and you want to script things in python. why don't you use the SGBD to perform your query ? You can try with psycopg2 : wiki.postgresql.org/wiki/Psycopg2_Tutorial – Maximilien jaffrès Aug 22 at 12:07
  • @Maximilienjaffrès I've used psycopg2 in the plugin as well, but in this case is much more easier to use QGIS's layer. It's connected to permissions, logging, etc. and I want to avoid that. But If QGIS's slowness is 'normal', than I have to change my mind I guess. – DIV Aug 22 at 12:19
  • I also make a Python plugin in Qgis. And by my experience, we always ask PostgreSQL to perform queries because native Qgis is doomed to be slow, sorry. – Maximilien jaffrès Aug 22 at 12:25
  • @Joseph I acknowledge that, but I tested something as well. I've used same table, but without that join. And weirdly, QGIS run query almost as fast as pgAdmin. Do you think that a table joining might be too much for QGIS in general? – DIV Aug 22 at 12:30
0

One method could be to set flags when requesting data such as ignoring geometry data of features and only focusing on retrieving data for a single attribute field. Also I think there might be a typo in your expression as you included "AND" at the very end of your expression.

You could try testing the following to see if it makes any difference:

table1 = iface.activeLayer()
attribute = 'area'
size = 5000
q = "\"{}\" > '{}' ".format(attribute, size)

expr = QgsExpression(q)
result = table1.getFeatures(QgsFeatureRequest(expr).setFlags(QgsFeatureRequest.NoGeometry).setSubsetOfAttributes([attribute], table1.fields()))
ids = [i.id() for i in result]
  • I've tried that already, and there was no much difference. I'm starting to believe tbh, that query on table with joined data is just too much for QGIS. Without joined data works as fast as pgAdmin. And yes, there was a type. Fixed. – DIV Aug 22 at 13:04
  • @DIV - Could you create a virtual field in your main layer and copy the values from the joined field? Then run the query on the new virtual field and see if this makes any difference? – Joseph Aug 22 at 13:07
  • I see your point, but I don't think it will tell as much more, than what I've done already. I have ca. 30 attribute columns and only 3 joined ones. As I said, if I get rid of joined fields everything works fine. Normal field or virtual, as far as there's no join, it doesn't matter, it will be fast anyway. Please elaborate, if I'm wrong. – DIV Aug 22 at 13:25
  • @DIV - Were you using memory cache when the join was made? I forgot to mention that the point of using a virtual field to copy the values would be that you could remove the join straight after. So first you join the table, create a virtual field and copy values over, remove the join and then run the query. – Joseph Aug 23 at 9:36
  • Yeah, I surmised that you forgot to mention that removing part, but I still think it would perform much like that layer without join, so. If you talking about something more than QGIS build-in cache system, then no. Do I have to set it up by myself with third party libraries or QGIS can help me with that with some more advanced settings? – DIV Aug 23 at 13:19

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.