1

Is it possible to create a multicolumn index on a geometry point column and a varchar column? For example, say I have a geometry point column called "location_point" and a varchar column called "zip". I have tried this...

CREATE INDEX contacts_80_zip_location_index ON contacts_80 USING GIST (location_point, zip);

This did not work. I received this error...

 ERROR:  data type character varying has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
SQL state: 42704
Improve this question
5
  • there's no way to include a varchar column in a spatial (gist) index, but you can have any non-spatial index you want ON (CAST(location_point TO VARCHAR), zip) – geozelot Oct 21 '19 at 11:38
  • 1
    Let's note that Postgres 12 now accepts an include clause for gist indexes. While the values are not indexed, they are copied in the main index so you could work with the index only and avoid having to read the underlying table. – JGH Oct 21 '19 at 15:44
  • Hmm. For some reason this is not working... CREATE INDEX contacts_80_zip_location_index ON contacts_80 USING GIST (zip) include (location_point); PostgreSQL gives this error: ERROR: syntax error at or near "include" LINE 1: ...ip_location_index ON contacts_80 USING GIST (zip) include (l... – GNG Oct 21 '19 at 16:02
  • @JGH interesting...but an uncompressed, unindexed column written into the index? Just doesn't seem right for anything else than PK integers...have you had a chance to work with a covering GIST? – geozelot Oct 21 '19 at 18:51
  • @ThingumaBob here is an interesting post on this topic. Without a covering index, one would read the index (spatial or attribute), then read the table for the corresponding row, then apply a filter / return the selected columns. With the covering index, it could be all done from the index. I don't know the real impact if one is using an SSD disk though.. – JGH Oct 21 '19 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.