Bug report #18295

PostGIS Data Loads Extemely Slow

Added by Mickey Dietrich over 6 years ago. Updated over 5 years ago.

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

Description

I have added data from PostGIS in Version 2.18 and the one data set loads in about 2 seconds. With the new 3.0 version, it takes 2 minutes to load that same data layer. All my data sets load really slow in Version 3.0. Not sure what the difference is from 2.18 to 3.0, but it has really caused problems with using the newer version, since we utilize the PostGIS database for accessing most all of our data. Is there something I am missing to make it load faster like in 2.18 or is there something behind the scenes causing this problem? Thanks, Mickey

History

#1 Updated by Keith Jenkins over 6 years ago

I was able to test this using a point layer of just 1124 points in Mickey's PostGIS database, and can confirm his observations (fast in 2.x, but very slow in 3.0.0). I've connected to other remote PostGIS databases without any slowness in 3.0.0.

Some other observations that might help diagnose the problem:

Using DB Manager to run a simple "select * from curb_stops" query directly on the PostGIS layer is very fast -- the result table appears in DB Manager almost instantly. But then when checking the box to "load as new layer", it renders very slowly.

Using DB Manager to run the same query via QGIS Virtual Layers is very slow, even to show the result table.

#2 Updated by Nyall Dawson over 6 years ago

  • Status changed from Open to Feedback

Can you check the postgres logs and see if there's any difference in the queries sent from 2.18 to 3.0?

#3 Updated by Mickey Dietrich over 6 years ago

Do you know where I can access the postgres logs showing queries on Amazon Web Services RDS? I only see error logs. Thanks,
Mickey

*I believe I have figured it out, but have to wait for reboot before I can get the sql statements.

#4 Updated by Giovanni Manghi over 6 years ago

  • Operating System deleted (Windows 7)

Nyall Dawson wrote:

Can you check the postgres logs and see if there's any difference in the queries sent from 2.18 to 3.0?

I have not checked the logs yet, but in the meantime I confirm the issue, the difference is huge: tested with 1000 points layer we go from almost instantaneous to several tens of seconds.

#5 Updated by Nyall Dawson over 6 years ago

We need logs to do any meaningful diagnose here.

#6 Updated by Mickey Dietrich over 6 years ago

I have to wait for Amazon Web Services to reboot the server for where my PostGIS instance is installed. Looks like I had to change DB parameters so I can see query logs. I think the reboot is set for tomorrow: Here was link I found about the DB parameters: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.PostgreSQL.html. Hopefully this works, otherwise I don't know how else to get the query logs. Every one of our staff are having the same issue and I have tried from home with the same results. QGIS 2.18 is fast and 3.0 is very slow. We were just about to make the switch for our staff over to QGIS from Esri, but need to figure out this issue first. Will keep my fingers crossed that the DB parameter thing works. Thanks for everyone's input so far, hopefully we will get things figured out.

#7 Updated by Giovanni Manghi over 6 years ago

  • Status changed from Feedback to Closed
  • Resolution set to fixed/implemented

I cannot see this anymore, at least on master. Reopen of necessary.

#8 Updated by Regis Haubourg over 5 years ago

  • Status changed from Closed to Open

I've seen such issues today on 3.4.5. Opening a 2.18 project is fast. Opening it in QGIS3 takes up to 7 minutes (!).

We tested with --noplugins options, no success.
Then we checked at the logs. One query was seen as isle, and the target table doesn't exists:

select count(*)

from information_schema.tables

where table_name = 'qgis_editor_widget_styles' ;

Running the same query runs in 1,7 s, which is slow but in fact the database is huge (170 schemas) and has a lot of tables. (maybe it's not the right query to use index efficiently)
I failed to understand what is that "qgis_editor_widget_styles" table used for, even if I found the related PR an commits. I never encountered what use case triggers its creation. If this rings some bells to someone, I'd be happy to ear about it.

We didn't see anything clear on postgresql logs, still we need to investigate more seriously the log options.

On the client side, QGIS seems to mostly ... wait. So I suspect some timeout to occur in PG for each layer. To be followed.

#9 Updated by Giovanni Manghi over 5 years ago

  • Resolution deleted (fixed/implemented)
  • Subject changed from PostGIS Data Loads Extemely Slow in Version 3.0 to PostGIS Data Loads Extemely Slow
  • Affected QGIS version changed from 3.0.0 to 3.4.5

Also available in: Atom PDF