Combined Hstore key/value and spatial query too slow to handle bigger OSM extracts

Question

I'm trying to compute some statistics for OSM data using PostgreSQL 9.3.5 and PostGIS 2.1.4. I started with a small bavaria extract which I downloaded from Geofabrik. The db schema is the normal API 0.6 schema, the data was imported via the dump approach into Postgres (using the pgsnapshot_schema_0.6*.sql scripts which come with osmosis). ANALYZE VACUUM was also performed.

The only custom made thing I'm using is a polygon table which contains multipolygons for all administrative boundary relations. The polygon geometry was not simplified in any way.

What I'm now trying to achieve is counting all nodes which are inside of the admin=6 boundaries of bavaria. Here is my SQL query:

SELECT relpoly.id, count(node) 
FROM bavaria.relpolygons relpoly, bavaria.nodes node
WHERE relpoly.tags @> '"boundary"=>"administrative","admin_level"=>"6"'::hstore 
AND ST_Intersects(relpoly.geom, node.geom)
GROUP BY relpoly.id;

The runtime of this query is terrible because Postgres is doing a nested loop join and scans over all nodes for every admin=6 boundary. FYI, bavaria is divided into 98 admin=6 polygons and there are about 30 million nodes in the bavaria extract.

Is it possible to avoid this sub-optimal query execution and to tell Postgres that it should scan all nodes only once (e.g., by incrementing a counter for the corresponding polygon in the result set or by using hints)?

Edit:

1) a spatial index exists on the bavaria nodes:

CREATE INDEX idx_nodes_geom ON bavaria.nodes USING gist (geom);

2) the query plan look like this:

HashAggregate  (cost=284908.49..284908.75 rows=26 width=103)
  ->  Nested Loop  (cost=111.27..283900.80 rows=201537 width=103)
        ->  Bitmap Heap Scan on relpolygons relpoly  (cost=4.48..102.29 rows=26 width=5886)
              Recheck Cond: (tags @> '"boundary"=>"administrative", "admin_level"=>"6"'::hstore)
              ->  Bitmap Index Scan on relpolygons_geom_tags  (cost=0.00..4.47 rows=26 width=0)
                    Index Cond: (tags @> '"boundary"=>"administrative", "admin_level"=>"6"'::hstore)
        ->  Bitmap Heap Scan on nodes node  (cost=106.79..10905.50 rows=983 width=127)
              Recheck Cond: (relpoly.geom && geom)
              Filter: _st_intersects(relpoly.geom, geom)
              ->  Bitmap Index Scan on idx_nodes_geom  (cost=0.00..106.55 rows=2950 width=0)
                    Index Cond: (relpoly.geom && geom)

3)

I created the following two indexes, but the query plan (and runtime) did not change

CREATE INDEX relpolygons_tags_boundary on bavaria.relpolygons( (tags->'boundary') );
CREATE INDEX relpolygons_tags_admin on bavaria.relpolygons( (tags->'admin_level') );
ANALYZE bavaria.relpolygons;

Answer 1

The best way to index hstore tags is using GIN or GIST indexes, which from the docs, support the @>, ?, ?& and ?| operators, that is, searches on keys and key/value pairs. You approach of using a function to extract the tags for a B-tree index is reasonable, but because you are also checking for specific key/value pairs, the analyzer has chosen a full table scan.

I don't have access to bavaria.relpolygons, but based on a similar query for OSM UK on speed limits and highway tags, I get this for my explain on the following query:

SELECT count(*) 
 FROM ways 
WHERE tags @> 'highway=>motorway'::hstore 
 AND tags @> 'maxspeed=>"50 mph"'::hstore;


Aggregate  (cost=48.66..48.67 rows=1 width=0)
    ->  Index Scan using ix_ways_tags_gist on ways  (cost=0.42..48.64 rows=11 width=0)
     Index Cond: ((tags @> '"highway"=>"motorway"'::hstore) AND (tags @> '"maxspeed"=>"50 mph"'::hstore))

which shows a direct index scan (using the gist index), which for a table with 10 million rows is encouraging. The index was created with the simple:

CREATE INDEX ix_ways_tags_gist ON ways USING gist (tags);

While I can't check you spatial condition, I am guessing it is less selective than

WHERE relpoly.tags @> '"boundary"=>"administrative","admin_level"=>"6"'::hstore.

and would therefore only be used for a recheck condition.

There is also this great SO answer on the difference between GIN and GIST indexes. The general finding is that GIN indexes while bigger and slower to build, are much faster on text retrieval problems.

Sorry to answer so late, but I have recently being doing similar work on OSM and hstore, and discovered that not only did I once star this question, but that I could now answer it :D.