CREATE TABLE telefonbuch (
    id SERIAL PRIMARY KEY,
    presence_flag bigint,
    reverse_flag bigint,
    biz_flag bigint,
    zip character varying(64)[] DEFAULT '{}',
    nachname character varying(256)[] DEFAULT '{}',
    vorname character varying(192)[] DEFAULT '{}',
    zusaetze character varying(512)[] DEFAULT '{}',
    strasse character varying(64)[] DEFAULT '{}',
    hausnummer character varying(32)[] DEFAULT '{}',
    verweise text[] DEFAULT '{}',
    ort character varying(64)[] DEFAULT '{}',
    vorwahl character varying(10)[] DEFAULT '{}',
    rufnummer character varying(32)[] DEFAULT '{}',
    web character varying(256)[] DEFAULT '{}',
    email character varying(256)[] DEFAULT '{}',
    coords character varying(32)[] DEFAULT '{}'
);

COPY Telefonbuch ( presence_flag, reverse_flag, biz_flag, zip, nachname, vorname, zusaetze, strasse, hausnummer, verweise, ort, vorwahl, rufnummer, web, email, coords ) FROM 'telefonbuch.txt' WITH NULL AS '';

CREATE MATERIALIZED VIEW indexed_view_nachname   AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(nachname)   WITH ORDINALITY u(value, row) WITH data;
CREATE MATERIALIZED VIEW indexed_view_vorname    AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(vorname)    WITH ORDINALITY u(value, row) WITH data;
CREATE MATERIALIZED VIEW indexed_view_vorwahl    AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(vorwahl)    WITH ORDINALITY u(value, row) WITH data;
CREATE MATERIALIZED VIEW indexed_view_rufnummer  AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(rufnummer)  WITH ORDINALITY u(value, row) WITH data;
CREATE MATERIALIZED VIEW indexed_view_strasse    AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(strasse)    WITH ORDINALITY u(value, row) WITH data;
CREATE MATERIALIZED VIEW indexed_view_hausnummer AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(hausnummer) WITH ORDINALITY u(value, row) WITH data;
CREATE MATERIALIZED VIEW indexed_view_zusaetze   AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(zusaetze)   WITH ORDINALITY u(value, row) WITH data;
CREATE MATERIALIZED VIEW indexed_view_verweise   AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(verweise)   WITH ORDINALITY u(value, row) WITH data;
CREATE MATERIALIZED VIEW indexed_view_zip        AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(zip)        WITH ORDINALITY u(value, row) WITH data;
CREATE MATERIALIZED VIEW indexed_view_ort        AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(ort)        WITH ORDINALITY u(value, row) WITH data;
CREATE MATERIALIZED VIEW indexed_view_web        AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(web)        WITH ORDINALITY u(value, row) WITH data;
CREATE MATERIALIZED VIEW indexed_view_email      AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(email)      WITH ORDINALITY u(value, row) WITH data;

# equality indexes
CREATE INDEX idx_nachname   ON  indexed_view_nachname   USING btree (value);
CREATE INDEX idx_vorname    ON  indexed_view_vorname    USING btree (value);
CREATE INDEX idx_vorwahl    ON  indexed_view_vorwahl    USING btree (value);
CREATE INDEX idx_rufnummer  ON  indexed_view_rufnummer  USING btree (value);
CREATE INDEX idx_strasse    ON  indexed_view_strasse    USING btree (value);
CREATE INDEX idx_hausnummer ON  indexed_view_hausnummer USING btree (value);
CREATE INDEX idx_zusaetze   ON  indexed_view_zusaetze   USING btree (value);
CREATE INDEX idx_verweise   ON  indexed_view_verweise   USING btree (value);
CREATE INDEX idx_zip        ON  indexed_view_zip        USING btree (value);
CREATE INDEX idx_ort        ON  indexed_view_ort        USING btree (value);
CREATE INDEX idx_web        ON  indexed_view_web        USING btree (value);
CREATE INDEX idx_email      ON  indexed_view_email      USING btree (value);

# full text indexes
CREATE INDEX trgm_idx_nachname   ON  indexed_view_nachname   USING gin (value gin_trgm_ops);
CREATE INDEX trgm_idx_vorname    ON  indexed_view_vorname    USING gin (value gin_trgm_ops);
CREATE INDEX trgm_idx_vorwahl    ON  indexed_view_vorwahl    USING gin (value gin_trgm_ops);
CREATE INDEX trgm_idx_rufnummer  ON  indexed_view_rufnummer  USING gin (value gin_trgm_ops);
CREATE INDEX trgm_idx_strasse    ON  indexed_view_strasse    USING gin (value gin_trgm_ops);
CREATE INDEX trgm_idx_hausnummer ON  indexed_view_hausnummer USING gin (value gin_trgm_ops);
CREATE INDEX trgm_idx_zusaetze   ON  indexed_view_zusaetze   USING gin (value gin_trgm_ops);
CREATE INDEX trgm_idx_verweise   ON  indexed_view_verweise   USING gin (value gin_trgm_ops);
CREATE INDEX trgm_idx_zip        ON  indexed_view_zip        USING gin (value gin_trgm_ops);
CREATE INDEX trgm_idx_ort        ON  indexed_view_ort        USING gin (value gin_trgm_ops);
CREATE INDEX trgm_idx_web        ON  indexed_view_web        USING gin (value gin_trgm_ops);
CREATE INDEX trgm_idx_email      ON  indexed_view_email      USING gin (value gin_trgm_ops);

# slow way:
SELECT * FROM Telefonbuch WHERE nachname = 'CCC';

# using the index:
SELECT * FROM Telefonbuch
  INNER JOIN indexed_view_nachname ON indexed_view_nachname.id = Telefonbuch.id
  INNER JOIN indexed_view_zip      ON indexed_view_zip.id = Telefonbuch.id
WHERE indexed_view_nachname.value = 'CCC'
AND   indexed_view_zip.value = '10117';


# Source: https://stackoverflow.com/questions/39480580/how-to-index-a-string-array-column-for-pg-trgm-term-any-array-column-que/39584236