Turn postgres description document into proper sql batch

2 files changed, 110 insertions, 76 deletions

diff --git a/src/postprocess/postgres.sql b/src/postprocess/postgres.sql
new file mode 100644
index 0000000..2d89d1c
--- /dev/null
+++ b/src/postprocess/postgres.sql
@@ -0,0 +1,110 @@
+\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;
+
+*/
diff --git a/src/postprocess/postgres.txt b/src/postprocess/postgres.txt
deleted file mode 100644
index e47ca00..0000000
--- a/src/postprocess/postgres.txt
+++ /dev/null
@@ -1,76 +0,0 @@
-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
-