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?