From a448659fb888440de4fd447f07e9629dd5150624 Mon Sep 17 00:00:00 2001 From: Dirk Engling Date: Thu, 7 Mar 2019 15:25:08 +0100 Subject: Turn postgres description document into proper sql batch --- src/postprocess/postgres.sql | 110 +++++++++++++++++++++++++++++++++++++++++++ src/postprocess/postgres.txt | 76 ------------------------------ 2 files changed, 110 insertions(+), 76 deletions(-) create mode 100644 src/postprocess/postgres.sql delete mode 100644 src/postprocess/postgres.txt 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 - -- cgit v1.2.3