Enhancing performance of join query for mapnik using PostGIS

Question

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.

• mogo: As Nickas said it, the main issue comes from the seq scan of the table2 even if there is an index on this table. To solve this, I change the parameter random_page_cost to favor the index scan: ALTER TABLESPACE pg_global SET ( random_page_cost = 2, seq_page_cost=1); This solved my performance issue.

Answer 1

I don't think a bbox anywhere can make the join faster. WHat is needed is a working index on table1.id and table2.fk_table1.

The bbox gist index filter will be more costly than joining on indexed integer columns.

• mogo: You're absolutely right, I just posted an answer here.