\timing on

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 '/Users/erdgeist/Coding/Telefonbuch/work/telefonbuch.txt' WITH NULL AS '';

-- # Clean up the old ones
DROP TABLE IF EXISTS table_nachname, table_vorname, table_zusaetze, table_strasse, table_hausnummer, table_zip, table_ort, table_verweise, table_vorwahl, table_rufnummer, table_web, table_email;

-- # tables we use for indexing
CREATE TABLE table_nachname   ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_vorname    ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_zusaetze   ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_strasse    ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_hausnummer ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_zip        ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_ort        ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_verweise   ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_vorwahl    ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_rufnummer  ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_web        ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_email      ( telefonbuch_id integer, offs integer, value text);

-- # Take copies of all content rows for indexing
INSERT INTO table_nachname    ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(nachname   ) WITH ORDINALITY u(value, offs);
INSERT INTO table_vorname     ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(vorname    ) WITH ORDINALITY u(value, offs);
INSERT INTO table_zusaetze    ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(zusaetze   ) WITH ORDINALITY u(value, offs);
INSERT INTO table_strasse     ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(strasse    ) WITH ORDINALITY u(value, offs);
INSERT INTO table_hausnummer  ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(hausnummer ) WITH ORDINALITY u(value, offs);
INSERT INTO table_zip         ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(zip        ) WITH ORDINALITY u(value, offs);
INSERT INTO table_ort         ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(ort        ) WITH ORDINALITY u(value, offs);
INSERT INTO table_verweise    ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(verweise   ) WITH ORDINALITY u(value, offs);
INSERT INTO table_vorwahl     ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(vorwahl    ) WITH ORDINALITY u(value, offs);
INSERT INTO table_rufnummer   ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(rufnummer  ) WITH ORDINALITY u(value, offs);
INSERT INTO table_web         ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(web        ) WITH ORDINALITY u(value, offs);
INSERT INTO table_email       ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(email      ) WITH ORDINALITY u(value, offs);

ALTER TABLE table_nachname   ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_vorname    ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_zusaetze   ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_strasse    ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_hausnummer ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_zip        ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_ort        ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_verweise   ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_vorwahl    ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_rufnummer  ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_web        ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_email      ADD PRIMARY KEY (telefonbuch_id, offs);

-- # equality indexes
CREATE INDEX idx_nachname   ON  table_nachname   USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_vorname    ON  table_vorname    USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_zusaetze   ON  table_zusaetze   USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_strasse    ON  table_strasse    USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_hausnummer ON  table_hausnummer USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_zip        ON  table_zip        USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_ort        ON  table_ort        USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_verweise   ON  table_verweise   USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_vorwahl    ON  table_vorwahl    USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_rufnummer  ON  table_rufnummer  USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_web        ON  table_web        USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_email      ON  table_email      USING btree (value, telefonbuch_id, offs);

-- # full text indexes
CREATE INDEX trgm_idx_nachname   ON  table_nachname   USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_vorname    ON  table_vorname    USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_zusaetze   ON  table_zusaetze   USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_strasse    ON  table_strasse    USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_hausnummer ON  table_hausnummer USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_zip        ON  table_zip        USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_ort        ON  table_ort        USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_verweise   ON  table_verweise   USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_vorwahl    ON  table_vorwahl    USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_rufnummer  ON  table_rufnummer  USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_web        ON  table_web        USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_email      ON  table_email      USING gin (value gin_trgm_ops, telefonbuch_id, offs);

-- # 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
# Tricks: SELECT reltuples::bigint AS estimate FROM   pg_class WHERE  oid = 'table_zusaetze'::regclass;

*/