Query in QGIS is significantly slower, than in pgAdmin

Question

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.

Answer 1

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]

Answer 2

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.

https://github.com/luisCartoGeo/PostGIS_Expression

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

import psycopg2

#Parameters:
port = '5432'
base = 'CABA'
host = 'localhost'
user= 'postgres'
password='12345678'

try:
    conn = psycopg2.connect(dbname=base, 
                            port=int(port), 
                            user=user,
                            password=password, 
                            host=host)
    cur = conn.cursor()
except:
    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);'
    
cur.execute(sql)
    
salida=cur.fetchall()
cur.close()
conn.close()

print(salida[0][0])