diff options
| -rw-r--r-- | src/postprocess/postgres.txt | 71 |
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 @@ | |||
| 1 | CREATE TABLE telefonbuch ( | 1 | CREATE TABLE telefonbuch ( |
| 2 | id SERIAL PRIMARY KEY, | ||
| 2 | presence_flag bigint, | 3 | presence_flag bigint, |
| 3 | reverse_flag bigint, | 4 | reverse_flag bigint, |
| 4 | biz_flag bigint, | 5 | biz_flag bigint, |
| @@ -17,25 +18,59 @@ CREATE TABLE telefonbuch ( | |||
| 17 | coords character varying(32)[] DEFAULT '{}' | 18 | coords character varying(32)[] DEFAULT '{}' |
| 18 | ); | 19 | ); |
| 19 | 20 | ||
| 20 | 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'; | 21 | 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 ''; |
| 21 | 22 | ||
| 22 | CREATE MATERIALZED VIEW nachname_view AS SELECT id, nachname FROM telefonbuch CROSS JOIN UNNEST(nachname) WITH ORDINALITY u(name, name_id) WITH data; | 23 | CREATE MATERIALIZED VIEW indexed_view_nachname AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(nachname) WITH ORDINALITY u(value, row) WITH data; |
| 24 | CREATE MATERIALIZED VIEW indexed_view_vorname AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(vorname) WITH ORDINALITY u(value, row) WITH data; | ||
| 25 | CREATE MATERIALIZED VIEW indexed_view_vorwahl AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(vorwahl) WITH ORDINALITY u(value, row) WITH data; | ||
| 26 | CREATE MATERIALIZED VIEW indexed_view_rufnummer AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(rufnummer) WITH ORDINALITY u(value, row) WITH data; | ||
| 27 | CREATE MATERIALIZED VIEW indexed_view_strasse AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(strasse) WITH ORDINALITY u(value, row) WITH data; | ||
| 28 | CREATE MATERIALIZED VIEW indexed_view_hausnummer AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(hausnummer) WITH ORDINALITY u(value, row) WITH data; | ||
| 29 | CREATE MATERIALIZED VIEW indexed_view_zusaetze AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(zusaetze) WITH ORDINALITY u(value, row) WITH data; | ||
| 30 | CREATE MATERIALIZED VIEW indexed_view_verweise AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(verweise) WITH ORDINALITY u(value, row) WITH data; | ||
| 31 | CREATE MATERIALIZED VIEW indexed_view_zip AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(zip) WITH ORDINALITY u(value, row) WITH data; | ||
| 32 | CREATE MATERIALIZED VIEW indexed_view_ort AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(ort) WITH ORDINALITY u(value, row) WITH data; | ||
| 33 | CREATE MATERIALIZED VIEW indexed_view_web AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(web) WITH ORDINALITY u(value, row) WITH data; | ||
| 34 | CREATE MATERIALIZED VIEW indexed_view_email AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(email) WITH ORDINALITY u(value, row) WITH data; | ||
| 23 | 35 | ||
| 24 | CREATE INDEX idx_hausnummer ON public.telefonbuch USING btree (hausnummer); | 36 | # equality indexes |
| 25 | CREATE INDEX idx_multilinegroup ON public.telefonbuch USING btree (multilinegroup); | 37 | CREATE INDEX idx_nachname ON indexed_view_nachname USING btree (value); |
| 26 | CREATE INDEX idx_nachname ON public.telefonbuch USING btree (nachname); | 38 | CREATE INDEX idx_vorname ON indexed_view_vorname USING btree (value); |
| 27 | CREATE INDEX idx_ort ON public.telefonbuch USING btree (ort); | 39 | CREATE INDEX idx_vorwahl ON indexed_view_vorwahl USING btree (value); |
| 28 | CREATE INDEX idx_rufnummer ON public.telefonbuch USING btree (rufnummer); | 40 | CREATE INDEX idx_rufnummer ON indexed_view_rufnummer USING btree (value); |
| 29 | CREATE INDEX idx_strasse ON public.telefonbuch USING btree (strasse); | 41 | CREATE INDEX idx_strasse ON indexed_view_strasse USING btree (value); |
| 30 | CREATE INDEX idx_vorname ON public.telefonbuch USING btree (vorname); | 42 | CREATE INDEX idx_hausnummer ON indexed_view_hausnummer USING btree (value); |
| 31 | CREATE INDEX idx_vorwahl ON public.telefonbuch USING btree (vorwahl); | 43 | CREATE INDEX idx_zusaetze ON indexed_view_zusaetze USING btree (value); |
| 32 | CREATE INDEX idx_zip ON public.telefonbuch USING btree (zip); | 44 | CREATE INDEX idx_verweise ON indexed_view_verweise USING btree (value); |
| 33 | CREATE INDEX idx_zusaetze ON public.telefonbuch USING btree (zusaetze); | 45 | CREATE INDEX idx_zip ON indexed_view_zip USING btree (value); |
| 46 | CREATE INDEX idx_ort ON indexed_view_ort USING btree (value); | ||
| 47 | CREATE INDEX idx_web ON indexed_view_web USING btree (value); | ||
| 48 | CREATE INDEX idx_email ON indexed_view_email USING btree (value); | ||
| 34 | 49 | ||
| 35 | CREATE INDEX trgm_idx_nachname ON public.telefonbuch USING gin (nachname gin_trgm_ops); | 50 | # full text indexes |
| 36 | CREATE INDEX trgm_idx_rufnummer ON public.telefonbuch USING gin (rufnummer gin_trgm_ops); | 51 | CREATE INDEX trgm_idx_nachname ON indexed_view_nachname USING gin (value gin_trgm_ops); |
| 37 | CREATE INDEX trgm_idx_verweise ON public.telefonbuch USING gin (verweise gin_trgm_ops); | 52 | CREATE INDEX trgm_idx_vorname ON indexed_view_vorname USING gin (value gin_trgm_ops); |
| 38 | CREATE INDEX trgm_idx_vorname ON public.telefonbuch USING gin (vorname gin_trgm_ops); | 53 | CREATE INDEX trgm_idx_vorwahl ON indexed_view_vorwahl USING gin (value gin_trgm_ops); |
| 39 | CREATE INDEX trgm_idx_zusaetze ON public.telefonbuch USING gin (zusaetze gin_trgm_ops); | 54 | CREATE INDEX trgm_idx_rufnummer ON indexed_view_rufnummer USING gin (value gin_trgm_ops); |
| 55 | CREATE INDEX trgm_idx_strasse ON indexed_view_strasse USING gin (value gin_trgm_ops); | ||
| 56 | CREATE INDEX trgm_idx_hausnummer ON indexed_view_hausnummer USING gin (value gin_trgm_ops); | ||
| 57 | CREATE INDEX trgm_idx_zusaetze ON indexed_view_zusaetze USING gin (value gin_trgm_ops); | ||
| 58 | CREATE INDEX trgm_idx_verweise ON indexed_view_verweise USING gin (value gin_trgm_ops); | ||
| 59 | CREATE INDEX trgm_idx_zip ON indexed_view_zip USING gin (value gin_trgm_ops); | ||
| 60 | CREATE INDEX trgm_idx_ort ON indexed_view_ort USING gin (value gin_trgm_ops); | ||
| 61 | CREATE INDEX trgm_idx_web ON indexed_view_web USING gin (value gin_trgm_ops); | ||
| 62 | CREATE INDEX trgm_idx_email ON indexed_view_email USING gin (value gin_trgm_ops); | ||
| 63 | |||
| 64 | # slow way: | ||
| 65 | SELECT * FROM Telefonbuch WHERE nachname = 'Engling'; | ||
| 66 | |||
| 67 | # using the index: | ||
| 68 | SELECT * FROM Telefonbuch | ||
| 69 | INNER JOIN indexed_view_nachname ON indexed_view_nachname.id = Telefonbuch.id | ||
| 70 | INNER JOIN indexed_view_zip ON indexed_view_zip.id = Telefonbuch.id | ||
| 71 | WHERE indexed_view_nachname.value = 'CCC' | ||
| 72 | AND indexed_view_zip.value = '10117'; | ||
| 73 | |||
| 74 | |||
| 75 | # Source: https://stackoverflow.com/questions/39480580/how-to-index-a-string-array-column-for-pg-trgm-term-any-array-column-que/39584236 | ||
| 40 | 76 | ||
| 41 | SELECT *, ST_AsText(coords) FROM Telefonbuch WHERE nachname = 'Engling'; | ||