mapnik/mapnik — Issue #1080: Performance issue on a postgis query with a join
Author: fmassot
Created: 2012-02-13 17:59:17+00:00
State: open

I use mapnik to render map with a postgis layer. The table of the postgis layer is defined by the following query:

( SELECT table1.id, table1.geometry, table2.fk_table1, table2.field FROM table1
INNER JOIN table2 ON (table1.id = table2.fk_table1) ) as subquery

When I use mapnik to render a map, it will add a where clause such like this:

WHERE table1.geometry && SetSRID('BOX3D(x1 y1, x2 y2)'::box3d,900913);

Unfortunately such a query is slow because the join is made on the whole tables before selecting the relevant geometries (I posted here a question http://gis.stackexchange.com/questions/20248/enhance-performance-of-a-join-query-for-mapnik-using-postgis and gave the results of EXPLAIN command).

Maybe I am doing something wrong, is there a solution to handle this issue ?

Thx