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 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.

share|improve this question
    
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. –  mogo Feb 14 '12 at 14:05

1 Answer 1

up vote 1 down vote accepted

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.

/Nicklas

share|improve this answer
    
You're absolutely right, I just posted an answer here. –  mogo Feb 14 '12 at 14:03

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.