Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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. –  Cao Minh Tu May 22 '13 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. –  Alexandre Neto May 22 '13 at 8:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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