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?

1 Answer 1


You may try this:

CREATE VIEW user_1_road AS
    f.pk_id, f.geom, f.name, f.type, f.importance,
    f.notes, f.label_x, f.label_y, f.label_angle
    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.

  • Thank a lot! The new query takes just 336ms on my table. Could you explain the difference between my query and yours so that the speed was so improved, or point me to a source where I can read that? Thanks. May 22, 2013 at 1:34
  • 1
    I'm not a postgresql pro, my answer was based on "instinct". In your query, it seamed to me that the query (SELECT geom FROM region where user_id =1) would be run once for each row in roads. In mine, the same query is performed only once in the beginning. Maybe if you compare both with EXPLAIN. May 22, 2013 at 8:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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