layerdefs db index

[ltclm]

in order to boost the performance of custom attribute search (layerdefs) in the queryable_attributes we have to make sure that all the attributes in the chsdi data models which are configured as queryable_attributes have a btree and a gin index:
p.e. stopo_master.vd.streetnames

-- __queryable_attributes__ = ['label', 'plzo', 'gdename', 'gdenr', 'type']

-- btree index for  = > < 
CREATE INDEX streetnames_label
  ON vd.streetnames
  USING btree
  (label);

CREATE INDEX streetnames_plzo
  ON vd.streetnames
  USING btree
  (plzo);

CREATE INDEX streetnames_gdename
  ON vd.streetnames
  USING btree
  (gdename);

CREATE INDEX streetnames_gdenr
  ON vd.streetnames
  USING btree
  (gdenr);

CREATE INDEX streetnames_type
  ON vd.streetnames
  USING btree
  (type);

-- gin index for  like, ilike
CREATE INDEX trgm_idx_streetnames_label
  ON vd.streetnames
  USING gin
  (label gin_trgm_ops);

CREATE INDEX trgm_idx_streetnames_plzo
  ON vd.streetnames
  USING gin
  (plzo gin_trgm_ops);

CREATE INDEX trgm_idx_streetnames_gdename
  ON vd.streetnames
  USING gin
  (gdename gin_trgm_ops);

CREATE INDEX trgm_idx_streetnames_gdenr
  ON vd.streetnames
  USING gin
  (gdenr gin_trgm_ops);

CREATE INDEX trgm_idx_streetnames_type
  ON vd.streetnames
  USING gin
  (type gin_trgm_ops);

[ltclm]

@tobias since you told me that you have free ressources:
please create an sql per database with begin; ...; rollback; and add the missing indices.
you could also ask @Simon1193 for help.

once the sql files are ready (in this pr) we will run them on dev int prod.
thank you
marcel

[ltclm]

https://github.com/geoadmin/tool-wms/pull/157