From a448659fb888440de4fd447f07e9629dd5150624 Mon Sep 17 00:00:00 2001
From: Dirk Engling <erdgeist@erdgeist.org>
Date: Thu, 7 Mar 2019 15:25:08 +0100
Subject: Turn postgres description document into proper sql batch

---
 src/postprocess/postgres.sql | 110 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 110 insertions(+)
 create mode 100644 src/postprocess/postgres.sql

(limited to 'src/postprocess/postgres.sql')

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;
+
+*/
-- 
cgit v1.2.3

