From 13773527601bebdbe224ec18ccfdc3cb75a0aa90 Mon Sep 17 00:00:00 2001 From: Dirk Engling Date: Sun, 24 Feb 2019 22:29:14 +0100 Subject: Add more details --- src/postprocess/postgres.txt | 71 +++++++++++++++++++++++++++++++++----------- 1 file 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'; -- cgit v1.2.3