I use mapnik to render map with datasources from a postgis database. One query is a join on two table, one with a geometry (a polygon) and the other one with some fields I want to use for rendering. Unfortunately, this query is very slow...
Here is the slow query:
SELECT table1.id, table1.geometry, table2.fk_table1, table2.field FROM table1
INNER JOIN table2 ON (table1.id = table2.fk_table1)
WHERE table1.geometry && SetSRID('BOX3D(x1 y1, x2 y2)'::box3d,900913);
Here is the query plan I got from an EXPLAIN:
Hash Join (cost=118896.94..262210.34 rows=27942 width=1099)
Hash Cond: (table1.id = table2.fk_table1)
-> Seq Scan on table2 (cost=0.00..73740.89 rows=4786689 width=16)
-> Hash (cost=113876.63..113876.63 rows=33865 width=1083)
-> Bitmap Heap Scan on table1 (cost=1112.15..113876.63 rows=33865 width=1083)
This query can be faster if the bbox is first applied on table1.geometry. However, mapnik does not help me there. Indeed, mapnik builds a query from a table/subquery by adding a WHERE clause on it
SELECT AsBinary("geometry") AS geom
FROM subquery
WHERE "geometry" && SetSRID('BOX3D(x1 y1,x2 y2)'::box3d,900913)
With the following subquery:
( SELECT table1.id, table1.geometry, table2.fk_table1, table2.field FROM table1
INNER JOIN table2 ON (table1.id = table2.fk_table1) ) as subquery
Basically, I need to enhance the speed of this query knowing that mapnik will add a where clause on the subquery. Thx for your help.
