Bug report #17031
Updated by Jürgen Fischer over 6 years ago
Hello, here is the case:
I have a PostGis Table containing 5 millions GPS point. I want to selected some of them and then call getFeatures in Python.
The table has a standard serial PK.
The call to loLayer.getFeatures(loRequest) is extremely slow. First I did a loRequest.setFilterFids(loLayer.selectedFeaturesIds()) to get the selection.
Removing the PK on the table solves the problem => super fast.
After some research, I found that the Postgre Data Provider use CTID in its WHERE clause (FAST) when no PK is found (disabling editing features, wich ,in my case is okay). And in all othe cases, the fids are cast into strings in the where clause... (SLOW - the index is not use i guess)
I understand it is related with the fix of #14262. https://issues.qgis.org/issues/14262.
At last there is a huge performance regression when working with large tables. At some point, when no editing is needed ( ie select statement), the where clause should be working with a integer PK.
I hope I was clear enough describing the problem... thanks.
I have a PostGis Table containing 5 millions GPS point. I want to selected some of them and then call getFeatures in Python.
The table has a standard serial PK.
The call to loLayer.getFeatures(loRequest) is extremely slow. First I did a loRequest.setFilterFids(loLayer.selectedFeaturesIds()) to get the selection.
Removing the PK on the table solves the problem => super fast.
After some research, I found that the Postgre Data Provider use CTID in its WHERE clause (FAST) when no PK is found (disabling editing features, wich ,in my case is okay). And in all othe cases, the fids are cast into strings in the where clause... (SLOW - the index is not use i guess)
I understand it is related with the fix of #14262. https://issues.qgis.org/issues/14262.
At last there is a huge performance regression when working with large tables. At some point, when no editing is needed ( ie select statement), the where clause should be working with a integer PK.
I hope I was clear enough describing the problem... thanks.