Bug report #17031

getFeatures (by Fids) extremely slow on large PostgreSQL table WITH PK ( OK if NO PK)

Added by Vincent Dionne almost 3 years ago. Updated over 1 year ago.

Status:Closed
Priority:High
Assignee:-
Category:Data Provider/PostGIS
Affected QGIS version:2.18.3 Regression?:Yes
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:not reproducable
Crashes QGIS or corrupts data:No Copied to github as #:24930

Description

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.

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.

History

#1 Updated by Giovanni Manghi almost 3 years ago

  • Status changed from Open to Feedback
  • Priority changed from Normal to High

Was ok on a previous LTR like 2.8 or 2.14?

#2 Updated by Vincent Dionne almost 3 years ago

Tested in 2.14 and it is OK

Here is the postgreSQL log showing the WHERE clause in 2.14 : (exec time : ~ 2 seconds)

DECLARE qgis_4 BINARY CURSOR FOR SELECT
...
FROM "scad"."20170322_ibus" WHERE "id" IN (3453850,3453851,5800408,3453852,5800409,5800410,5800411,1121748,1121749,1121750,1121751,1121 ...

in 2.18: (exec time : i don't know , I stopped it after 15 minutes)

DECLARE qgis_5 BINARY CURSOR FOR SELECT
...
FROM "scad"."20170322_ibus" WHERE ("id"::text='5174976' OR "id"::text='5064624' OR "id"::text='5174977' OR "id"::text='5064625' OR "id"::text='5174978' OR "id"::text='5174979' OR "id"::text='5174980' OR "id"::text='5174981' OR "id"::text='5174982' OR "id"::text='5174983' OR "id"::text='5174984' OR "id"::text='5174985' ...

#3 Updated by Giovanni Manghi almost 3 years ago

  • Status changed from Feedback to Open

#4 Updated by Jürgen Fischer almost 3 years ago

  • Description updated (diff)

#5 Updated by Jürgen Fischer almost 3 years ago

What type does your primary key have?

#6 Updated by Giovanni Manghi almost 3 years ago

  • Status changed from Open to Feedback

#7 Updated by Jürgen Fischer over 2 years ago

  • Assignee deleted (Jürgen Fischer)

#8 Updated by Giovanni Manghi over 2 years ago

Please leave further feedback.

#9 Updated by Jürgen Fischer over 1 year ago

  • Resolution set to not reproducable
  • Status changed from Feedback to Closed

closed for the lack of feedback.

Also available in: Atom PDF