[postgis-users] Multicolumn queries: GiST and strings

[Andy Turk]

I'd like to be able to create an index that uses two columns, one geometry 
and one string. I have a very slow query that uses only a geometry index 
right now.

It's functionally correct, but expensive because the GiST index (correctly) 
returns tens of thousands of rows which are then scanned sequentially to find 
matching records.

The perfect solution would be a multi column GiST index that both the 
geometry column and the string column to quickly get to the right results.

Here's what I tried to do (where "stringcol" is a VARCHAR):

CREATE INDEX foo ON mytable USING
  GIST (the_geom GIST_GEOMETRY_OPS, stringcol);

ERROR: data type character varying has no default operator class for access 
method "gist"
You must specify an operator class for the index or define a default operator 
class for the data type

I listed out the operator classes and the only gist-related class I found was 
the geometry stuff--nothing for strings.

Is there a package of string operations for GiST somewhere? Has anyone 
successfully used a multicolumn GiST index with geometry objects before?

Thanks,

Andy Turk
andy at streetlight.com