[osmosis-dev] pgsnapshot composite index results

[Paweł Paprota]

On 01/22/2013 10:53 AM, Paul Norman wrote:
> On case where it's a clear winner is where*all*  queries involve both a
> spatial and tag component and there isn't a need for a separate gin index if
> the composite index is used.

This sounds like a big win for WMS servers that use OpenStreetMap data. 
I have an installation where GeoServer serves layers from a database 
based on the osm2pgsql schema and layers are defined similarly to the 
default imposm setup so there's a layer for roads, waterways, emergency 
etc. and of course these definitions are based on tags.

So every WMS map request generates something like this:

SELECT "type",encode(ST_AsBinary(ST_Force_2D("way")),'base64') as "way" 
FROM "public"."view_railways" WHERE  ("way" && ST_GeomFromText('POLYGON 
((2031543.337480622 6414748.28685562, 2031543.337480622 
6578323.527363132, 2195118.5779881305 6578323.527
363132, 2195118.5779881305 6414748.28685562, 2031543.337480622 
6414748.28685562))', 900913) AND "type" = 'rail')

Right now I have separate indexes (GIN on hstore, GIST on geom) but it's 
interesting that a composite index could work well too.

I will try that, thanks for describing your findings - perhaps this 
stuff could be documented on the wiki somewhere because on the mailing 
list it will be soon forgotten in the archives and it's very valuable 
content.

Paweł