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.

  • 1
    PostgreSQL is a dedicated database management system which specialises in such queries. QGIS is not - so there will always be a performance gap.
    – Joseph
    Commented Aug 22, 2019 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 Commented Aug 22, 2019 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
    Commented Aug 22, 2019 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. Commented Aug 22, 2019 at 12:25
  • 2
    Typically, DB administration tools (like pgAdmin) fetches only a subset of rows for each query (200 or so). QGIS fetches all of them in your example. That can make a huge difference on tables with large row counts...
    – DavidP
    Commented Aug 22, 2019 at 12:33

2 Answers 2


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
    Commented Aug 22, 2019 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
    Commented Aug 22, 2019 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
    Commented Aug 22, 2019 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
    Commented Aug 23, 2019 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
    Commented Aug 23, 2019 at 13:19

It seems to me that the problem is that you are using the QGIS API to perform the query, if you want to have a performance similar to PgAdmin you must implement the PostgreSQL engine in QGIS.

That is, establish a connection to the database and implement a SQL statement to the Database.
You can achieve this in several ways, using the QGIS API, specifically QgsDataSourceUri although I have used it when I need to produce a layer product of a query.
When I require a query that returns alphanumeric or boolean values I use the psycopg2 library, which comes pre-installed in QGIS.
I am working on a plugin that creates expressions to query a PostGIS database using the PostGIS engine, I think the code will be useful for you.


Here is an example of the use of the library for a spatial consultation:

import psycopg2

port = '5432'
base = 'CABA'
host = 'localhost'
user= 'postgres'

    conn = psycopg2.connect(dbname=base, 
    cur = conn.cursor()
    print("I am unable to connect to the database")  

sql= 'select count(*) from public.parcelas as a,'+'\n'+\
    '(select * from division_administrativa.barrios where barrio='+"'PALERMO')  as b"+'\n'+\
    'WHERE ST_Intersects(a.geom,b.geom);'


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.