I have developed a QGIS python plugin that heavily deals with many Postgresql tables. It joins the data from these tables, and allows viewing this data, adding, updating, and deleting.

In my plugin I am accessing the tables as QgsVectorLayer using:

layer1 = QgsProject.instance().mapLayersByName('layer1_name') 

Of course I had to add all my tables as vector layers to the QGIS project first.

As I mentioned I am doing some joins between the tables, but I am doing this manually; for example if I have a Customers table that has a one-to-many relationship to Bills table, then in order to display the bills of some given customerIds I loop over all these customerIds, one by one, filter the Bills layer by the customerId and get the returned features from the Bills layer. This is just and example, but in reality I am doing that between many tables.

My problem is the performance of my plugin is very bad; it takes too long time to load/add/update the data.

My question is: is my approach correct? or should I access the DB tables directly (e.g using psycopg2), and create joins using a normal JOIN SQL query?

  • 1
    This is so strange for me ... You load your layers with Python and after, for find again your layer, you use mapLayersByName but ... Why don't you keep your layer object from the start ? The same object before the loading in the project ? And for your plugin, with what you say, I understand you manage updates / joins / deletions on the QGIS side, I advise you, as you have already a PostgreSQL DB, to do this on the DB side (I strongly recommend you to use an ORM). Dec 19 '20 at 6:56
  • @J.Monticolo So you are with accessing the DB tables directly using e.g. psycopg2 or an ORM, right?
    – devfaz
    Dec 19 '20 at 7:09
  • yes, exactly, with PostGIS for spatial functions and storage. If you don't want to overload your DB server, you can do computing, data and geometries management on the "client" side but do it with tools like shapely, GeoPandas, Fiona, QGIS processing, etc. if visualization is not important. Dec 19 '20 at 8:40

If you are familiar with SQL, direct connection to PostGIS (e.g. through psycopg2) is more effective, IMHO.

All that you need is to get the name of the underlying table as you did:

 layer1 = QgsProject.instance().mapLayersByName('layer1_name')

And then compose an SQL of arbitrary complexity based on table names. Build indexes if necessary along the way. You can always create a new QGIS layer based on any new tables that you computed.

On the other hand, I am not familiar with QGIS's join etc. Someone with better knowledge than me can speak to that. I think the advantage with the QGIS approach is that your plugin will work on non-PostGIS sources (on a greatest common divisor basis). Otherwise, I guess PostGIS will be more powerful and efficient, if the database backend is the data source you target.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.