How to make working with Postgis vector layers in QGIS faster?

Question

I have just imported a Spatialite database into Postgresql/Postgis. The problem is: using QGIS to work with them is much slower in Postgis (spatial indexes created) than doing the same in Spatialite.

Is there any way to improve Postgis layers speed in QGIS?

EDIT:

Postgis is running on an external server while Spatialite database is on my local PC. Previously, I thought QGIS would cache all my loaded layers onto local disk, I just checked and found that I was wrong - every time I pan around in QGIS, network traffic on my Postgis database increase (right now I am the only one using this experimenting server).

Is there any way to make QGIS cache data locally to improve speed?

Answer 1

Make a local PostGIS database.

The primary issue that is slowing you down is network latency. If you want to continue using the data in PostGIS and you want greater connection speeds, you can install PostgreSQL and PostGIS on your local computer, then load the portion of the data you would like to work with locally.

ogr2ogr can be used to create a single command that transfers data to your local database. Examples of using ogr2ogr are here, and here

When you are done working with it, you can transfer the data back into your remote database.