[osmosis-dev] Non-standard pgsnapshot indexes

[Paul Norman]

I've talked in other places about the non-standard indexes that I have on my
pgsnapshot database, but I don't believe I've ever produced a full listing.
I believe the following are all the non-standard indexes I have, with the
size and applicable comments in []
	
On nodes:

btree (changeset_id) [37GB, DWG stuff tends to a lot of changeset queries]

gist (geom, tags) [153GB,
http://lists.openstreetmap.org/pipermail/osmosis-dev/2013-January/001485.htm
l]

gin (tags) [24GB, xapi]

btree (array_length(akeys(tags), 1)) WHERE array_length(akeys(tags), 1) > 10
[92MB, for finding weirdly tagged stuff]

On ways: 

btree (changeset_id) [5.9GB]

btree ((tags -> 'name'::text) text_pattern_ops) WHERE tags ? 'name'::text
[1.3GB, for running tags -> 'name' LIKE queries as well as potentially
quicker name queries]

btree ((tags -> 'name_1'::text) text_pattern_ops) WHERE tags ?
'name_1'::text [49MB]

btree ((tags -> 'name_2'::text) text_pattern_ops) WHERE tags ?
'name_2'::text [4.2MB]

gin (tags) [19GB, xapi]

btree (array_length(akeys(tags), 1)) WHERE array_length(akeys(tags), 1) > 10
[274MB]

On relations:
gin (tags)
btree (array_length(akeys(tags), 1)) WHERE array_length(akeys(tags), 1) > 10
[1.9MB]