How to speed up query through view in postgis?

Question

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. Result of SELECT PostGIS_full_version();

"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?

Answer 1

You may try this:

CREATE VIEW user_1_road AS
SELECT
    f.pk_id, f.geom, f.name, f.type, f.importance,
    f.notes, f.label_x, f.label_y, f.label_angle
FROM
    road AS f JOIN
    (SELECT geom FROM regions where user_id = '1') AS g
    ON st_contains(g.geom, f.geom);

I tested with my data and it went from 1847 miliseconds to 245 miliseconds.