User talk:Species/PostGIS Tuning

I've been doing these INDEXes too, but nowadays I prefer a different method. I'm using a hstore column with all tags (osm2pgsql can create this for you when you add --hstore --hstore-all as parameters). And then I create a multicolumn index using way and tags:

CREATE INDEX planet_osm_point_index ON planet_osm_point USING GIST(way, tags);
CREATE INDEX planet_osm_line_index ON planet_osm_point USING GIST(way, tags);
CREATE INDEX planet_osm_polygon_index ON planet_osm_point USING GIST(way, tags);

When selecting you always have to use the tags column, which looks like:

-- select all points which amenity=restaurant in the current bbox (Mapnik replaces !bbox! by the current bounding box)
SELECT * FROM planet_osm_point WHERE way && !bbox! AND tags @> 'amenity=>restaurant';

-- select all points with amenity=restaurant or amenity=bar
SELECT * FROM planet_osm_point WHERE way && !bbox! AND (tags @> 'amenity=>restaurant' OR tags @> 'amenity=>bar');

-- select all lines with a highway tag
SELECT * FROM planet_osm_line WHERE way && !bbox! AND tags ? 'highway';

Adding the tags column to the database has the additional advantage, that all tags are imported to the database, even those which are not listed in the default.style. You can access them using:

-- select all lines with a highway tag and return the "foobar" tag as additional column:
SELECT *, tags->'foobar' AS "foobar" FROM planet_osm_line WHERE way && !bbox! AND tags ? 'highway';

More about hstore: http://www.postgresql.org/docs/9.1/static/hstore.html

This is what the tags column looks like:

SELECT osm_id, tags FROM planet_osm_point WHERE way && '010300002031BF0D000100000005000000F1FD98CB53353A410EFF660D4DB55641F1FD98CB53353A41FD3E13E2BEB75641B2FD491E1B3F3A41FD3E13E2BEB75641B2FD491E1B3F3A410EFF660D4DB55641F1FD98CB53353A410EFF660D4DB55641' AND (tags @> 'amenity=>restaurant' OR tags @> 'amenity=>bar');
id         | tags
-----------+---------------------------------------------------
1363478707 | "name"=>"Lendplatzl", "amenity"=>"restaurant", "cuisine"=>"regional", "wheelchair"=>"yes"
1688850484 | "fax"=>"+43 316 71 68 55", "name"=>"Zur Steirerstub'n", "email"=>"office@pension-graz.at", "phone"=>"+43 316 71 68 55", "stars"=>"3", "amenity"=>"restaurant", "cuisine"=>"regional", "tourism"=>"guest_house", "website"=>"http://www.pension-graz.at/", "operator"=>"Birgit Sommer", "addr:city"=>"Graz", "wheelchair"=>"no", "addr:street"=>"Lendplatz", "addr:country"=>"AT", "addr:postcode"=>"8020", "addr:housenumber"=>"8"
1363478713 | "name"=>"Marschallhof", "amenity"=>"restaurant", "cuisine"=>"regional", "addr:city"=>"Graz", "addr:street"=>"Marschallgasse", "addr:country"=>"AT", "addr:postcode"=>"8020", "contact:phone"=>"+43 316 713493", "addr:housenumber"=>"22"
1363478714 | "name"=>"Stern", "amenity"=>"bar", "wheelchair"=>"yes"
1438220158 | "name"=>"Omoka", "amenity"=>"restaurant", "cuisine"=>"african", "smoking"=>"separated", "addr:city"=>"Graz", "wheelchair"=>"no", "addr:street"=>"Keplerstraße", "addr:country"=>"AT", "addr:postcode"=>"8020", "contact:phone"=>"+43 664 4852296", "opening_hours"=>"Mo,We-Su 14:00-24:00", "addr:housenumber"=>"12"
 568129694 | "name"=>"Gasthaus zu den 3 goldenen Kugeln", "amenity"=>"restaurant", "smoking"=>"isolated", "website"=>"http://3goldenekugeln.at/", "addr:city"=>"Graz", "wheelchair"=>"limited", "addr:street"=>"Griesplatz", "addr:country"=>"AT", "addr:postcode"=>"8020", "addr:housenumber"=>"34"
1363478720 | "name"=>"Miran Schaweder", "amenity"=>"restaurant", "cuisine"=>"seafood"
2141640916 | "name"=>"Sportwetten", "amenity"=>"bar", "addr:city"=>"Graz", "wheelchair"=>"no", "addr:street"=>"Griesplatz", "addr:country"=>"AT", "addr:postcode"=>"8020", "addr:housenumber"=>"36"
1826545351 | "name"=>"Pierre's", "amenity"=>"bar", "smoking"=>"yes", "wheelchair"=>"yes", "opening_hours"=>"Mo-Sa 17:00-02:00; Sa 10:00-14:00"
2141640902 | "name"=>"Grill am Gries", "amenity"=>"restaurant", "cuisine"=>"cevapcici;burek", "smoking"=>"yes", "addr:city"=>"Graz", "addr:street"=>"Griesgasse", "addr:country"=>"AT", "addr:postcode"=>"8020", "addr:housenumber"=>"50"

Let's look at the query plan:

-- select all points with amenity=restaurant or amenity=bar
Bitmap Heap Scan on planet_osm_point  (cost=9.30..25.20 rows=4 width=1160)
  Recheck Cond: (((way && '010300002031BF0D000100000005000000F1FD98CB53353A410EFF660D4DB55641F1FD98CB53353A41FD3E13E2BEB75641B2FD491E1B3F3A41FD3E13E2BEB75641B2FD491E1B3F3A410EFF660D4DB55641F1FD98CB53353A410EFF660D4DB55641'::geometry) AND (tags @> '"amenity"=>"restaurant"'::hstore)) OR ((way && '010300002031BF0D000100000005000000F1FD98CB53353A410EFF660D4DB55641F1FD98CB53353A41FD3E13E2BEB75641B2FD491E1B3F3A41FD3E13E2BEB75641B2FD491E1B3F3A410EFF660D4DB55641F1FD98CB53353A410EFF660D4DB55641'::geometry) AND (tags @> '"amenity"=>"bar"'::hstore)))
  ->  BitmapOr  (cost=9.30..9.30 rows=4 width=0)
        ->  Bitmap Index Scan on planet_osm_point_way_tags  (cost=0.00..4.65 rows=2 width=0)
              Index Cond: ((way && '010300002031BF0D000100000005000000F1FD98CB53353A410EFF660D4DB55641F1FD98CB53353A41FD3E13E2BEB75641B2FD491E1B3F3A41FD3E13E2BEB75641B2FD491E1B3F3A410EFF660D4DB55641F1FD98CB53353A410EFF660D4DB55641'::geometry) AND (tags @> '"amenity"=>"restaurant"'::hstore))
        ->  Bitmap Index Scan on planet_osm_point_way_tags  (cost=0.00..4.65 rows=2 width=0)
              Index Cond: ((way && '010300002031BF0D000100000005000000F1FD98CB53353A410EFF660D4DB55641F1FD98CB53353A41FD3E13E2BEB75641B2FD491E1B3F3A41FD3E13E2BEB75641B2FD491E1B3F3A410EFF660D4DB55641F1FD98CB53353A410EFF660D4DB55641'::geometry) AND (tags @> '"amenity"=>"bar"'::hstore))
(7 rows)

-> Yes, it's using the multicolumn index.

-- Skunk (talk) 09:11, 6 February 2014 (UTC)

I've though about using hstore too - but having to rewrite all queries in the carto-css frightened me. How much faster is using hstore compared to traditional columns? -- Species

according to #OSM-DEV IRC, hstore is not faster.

I also don't think that using hstores is faster (can't remember if I did performance tests) - hstores have other advantages:

• All tags (can be / are) in the database - with traditional osm2pgsql only those which were listed in the style-file are in the database
• Fewer indexes - which might make database updates faster (only one index per table to update - on the other hand this index is more complex, therefore maybe slower)
• If you change database queries, you have to adapt your indexes

All in all, I'm happy with using hstores :-) -- Skunk (talk) 12:45, 12 February 2014 (UTC)