I want to set up a postgis database that have some base tables, then the users interact with them through views (which restrict their viewable and acessible regions).
The server runs PostgreSQL 9.2 on Ubuntu 12.04 and has been configured as recommended here.
"POSTGIS="2.0.1 r9979" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6 March 2012" LIBXML="2.8.0""
I tested the above model with a road layer (polyline) with 6040 rows but the time it takes to select all rows through view is 13 seconds v.s. 2.2 seconds for selecting all rows on original table (nearly 6 times as much - this makes working with multiple postgis layer in Qgis at the same time terribly slow, except when zoomed in to a small region).
My create view sql:
CREATE VIEW user_1_road AS SELECT pk_id, geom, name, type, importance, notes, label_x, label_y, label_angle FROM road WHERE st_contains(( SELECT geom FROM regions where user_id = '1'), road.geom);
Is there anyway to improve execution speed?