Add more details

1 files changed, 53 insertions, 18 deletions

diff --git a/src/postprocess/postgres.txt b/src/postprocess/postgres.txt
index d1b4669..943a88a 100644
--- a/src/postprocess/postgres.txt
+++ b/src/postprocess/postgres.txt
@@ -1,4 +1,5 @@
 CREATE TABLE telefonbuch (
+    id SERIAL PRIMARY KEY,
     presence_flag bigint,
     reverse_flag bigint,
     biz_flag bigint,
@@ -17,25 +18,59 @@ CREATE TABLE telefonbuch (
     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 'NULL';
+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 MATERIALZED VIEW nachname_view AS SELECT id, nachname FROM telefonbuch CROSS JOIN UNNEST(nachname) WITH ORDINALITY u(name, name_id) WITH data;
+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;
 
-CREATE INDEX idx_hausnummer ON public.telefonbuch USING btree (hausnummer);
-CREATE INDEX idx_multilinegroup ON public.telefonbuch USING btree (multilinegroup);
-CREATE INDEX idx_nachname ON public.telefonbuch USING btree (nachname);
-CREATE INDEX idx_ort ON public.telefonbuch USING btree (ort);
-CREATE INDEX idx_rufnummer ON public.telefonbuch USING btree (rufnummer);
-CREATE INDEX idx_strasse ON public.telefonbuch USING btree (strasse);
-CREATE INDEX idx_vorname ON public.telefonbuch USING btree (vorname);
-CREATE INDEX idx_vorwahl ON public.telefonbuch USING btree (vorwahl);
-CREATE INDEX idx_zip ON public.telefonbuch USING btree (zip);
-CREATE INDEX idx_zusaetze ON public.telefonbuch USING btree (zusaetze);
+# 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);
 
-CREATE INDEX trgm_idx_nachname ON public.telefonbuch USING gin (nachname gin_trgm_ops);
-CREATE INDEX trgm_idx_rufnummer ON public.telefonbuch USING gin (rufnummer gin_trgm_ops);
-CREATE INDEX trgm_idx_verweise ON public.telefonbuch USING gin (verweise gin_trgm_ops);
-CREATE INDEX trgm_idx_vorname ON public.telefonbuch USING gin (vorname gin_trgm_ops);
-CREATE INDEX trgm_idx_zusaetze ON public.telefonbuch USING gin (zusaetze gin_trgm_ops);
+# 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 = 'Engling';
+
+# 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
 
-SELECT *, ST_AsText(coords) FROM Telefonbuch WHERE nachname = 'Engling';