Bug report #14030

Filter expressions to postgres provider

Added by Edward H almost 5 years ago. Updated almost 5 years ago.

Status:Closed
Priority:High
Assignee:Matthias Kuhn
Category:Data Provider/PostGIS
Affected QGIS version:master Regression?:No
Operating System:Windows 7 - 32bits Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:22043

Description

Option "Execute expressions on postgres server side if possible" (in Options>Data Sources) works perfectly in QGIS 2.12.x
But in QGIS master, this option does not work and the query is no longer optimal : 2 seconds in QGIS 2.12.x vs 2 minutes in QGIS master

In my case, there is 4 tables (2 with geometry and 2 without geometry) with 1-n relations (in Project Properties > Relations) storing in PostgreSQL 9.3 and Postgis 2.2

Relations1.png (676 KB) Edward H, 2016-01-07 01:48 AM

Relations2.png (649 KB) Edward H, 2016-01-07 01:48 AM

Issue_Large_Relationship_Tables.JPG (142 KB) Edward H, 2016-01-12 09:11 AM

History

#1 Updated by Matthias Kuhn almost 5 years ago

  • Status changed from Open to Feedback

Which expression?

#2 Updated by Edward H almost 5 years ago

In fact, on my map canvas, i use the button "Identify Features" to visualize some datas edited with the "drag and drop designer". And the result is a combinaison of "LEFT JOIN".

Without the check of "Execute expressions on postgres server side if possible" the result is very slow, but fast with this option enable.
Now, check or not check (in QGIS Master), the result is very slow.

Sorry if this description is not very understandable.

#3 Updated by Matthias Kuhn almost 5 years ago

I think that the forms with relations were never actually using the expression compiler IIRC.

#4 Updated by Edward H almost 5 years ago

Hi, sorry for the long time to respond.
I will try to be more explicit about this issue :

I load 2 "big" tables in QGIS master and QGIS 2.12 = table one (461010 rows) / table two (415130 rows)

I join these tables with the project Properties > Relations (look picture "Relations1")

"Execute expressions on server side" is enable for the two projects
I use the button "Identify Features" to visualize an object (there is a french cadastral plot)
Less than 2 seconds for QGIS 2.12 / More than 1 minute for QGIS master (look picture "Relations2")

IF I disable "Execute expressions on server side" in QGIS 2.12, there is like QGIS master, more than 1 minute to display the form with the "Identify Features" button

So I thought that the expression compiler is linked to the forms with relations...

This issue may be replicate easily with 2 big tables.
Thank you if you can watch this issue

#5 Updated by Giovanni Manghi almost 5 years ago

  • Status changed from Feedback to Open

#6 Updated by Edward H almost 5 years ago

@Matthias Kuhn,

I found this post on the blog http://blog.vitu.ch/10112013-1201/qgis-relations that explains the role of the "server-side index" with an answer from you.
I have the same problem as mentionned in this post : I tried to build a relationship between PostgreSQL tables where the related table has about half million rows
When these relations works perfectly in QGIS 2.12 (with the option "Execute expressions on server side" enable), it does not work in qgis master...
Thank you again if you can have a look at my problem and I hope what i'm seeing make sense

#7 Updated by Nyall Dawson almost 5 years ago

  • Status changed from Open to Feedback

Please test with tomorrow's nightlies - likely fixed with ed54e3cc0be4b9a79ac23adf8bffc858d315c01e

#8 Updated by Edward H almost 5 years ago

OK, thanks a lot. I will test with the next QGIS nightly build and then close this issue.

#9 Updated by Edward H almost 5 years ago

  • Status changed from Feedback to Closed

perfect ! it works very well

Also available in: Atom PDF