summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--src/postprocess/postgres.txt71
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 @@
1CREATE TABLE telefonbuch ( 1CREATE 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
20COPY 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'; 21COPY 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
22CREATE MATERIALZED VIEW nachname_view AS SELECT id, nachname FROM telefonbuch CROSS JOIN UNNEST(nachname) WITH ORDINALITY u(name, name_id) WITH data; 23CREATE MATERIALIZED VIEW indexed_view_nachname AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(nachname) WITH ORDINALITY u(value, row) WITH data;
24CREATE MATERIALIZED VIEW indexed_view_vorname AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(vorname) WITH ORDINALITY u(value, row) WITH data;
25CREATE MATERIALIZED VIEW indexed_view_vorwahl AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(vorwahl) WITH ORDINALITY u(value, row) WITH data;
26CREATE MATERIALIZED VIEW indexed_view_rufnummer AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(rufnummer) WITH ORDINALITY u(value, row) WITH data;
27CREATE MATERIALIZED VIEW indexed_view_strasse AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(strasse) WITH ORDINALITY u(value, row) WITH data;
28CREATE MATERIALIZED VIEW indexed_view_hausnummer AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(hausnummer) WITH ORDINALITY u(value, row) WITH data;
29CREATE MATERIALIZED VIEW indexed_view_zusaetze AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(zusaetze) WITH ORDINALITY u(value, row) WITH data;
30CREATE MATERIALIZED VIEW indexed_view_verweise AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(verweise) WITH ORDINALITY u(value, row) WITH data;
31CREATE MATERIALIZED VIEW indexed_view_zip AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(zip) WITH ORDINALITY u(value, row) WITH data;
32CREATE MATERIALIZED VIEW indexed_view_ort AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(ort) WITH ORDINALITY u(value, row) WITH data;
33CREATE MATERIALIZED VIEW indexed_view_web AS SELECT id, value, row FROM Telefonbuch CROSS JOIN unnest(web) WITH ORDINALITY u(value, row) WITH data;
34CREATE 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
24CREATE INDEX idx_hausnummer ON public.telefonbuch USING btree (hausnummer); 36# equality indexes
25CREATE INDEX idx_multilinegroup ON public.telefonbuch USING btree (multilinegroup); 37CREATE INDEX idx_nachname ON indexed_view_nachname USING btree (value);
26CREATE INDEX idx_nachname ON public.telefonbuch USING btree (nachname); 38CREATE INDEX idx_vorname ON indexed_view_vorname USING btree (value);
27CREATE INDEX idx_ort ON public.telefonbuch USING btree (ort); 39CREATE INDEX idx_vorwahl ON indexed_view_vorwahl USING btree (value);
28CREATE INDEX idx_rufnummer ON public.telefonbuch USING btree (rufnummer); 40CREATE INDEX idx_rufnummer ON indexed_view_rufnummer USING btree (value);
29CREATE INDEX idx_strasse ON public.telefonbuch USING btree (strasse); 41CREATE INDEX idx_strasse ON indexed_view_strasse USING btree (value);
30CREATE INDEX idx_vorname ON public.telefonbuch USING btree (vorname); 42CREATE INDEX idx_hausnummer ON indexed_view_hausnummer USING btree (value);
31CREATE INDEX idx_vorwahl ON public.telefonbuch USING btree (vorwahl); 43CREATE INDEX idx_zusaetze ON indexed_view_zusaetze USING btree (value);
32CREATE INDEX idx_zip ON public.telefonbuch USING btree (zip); 44CREATE INDEX idx_verweise ON indexed_view_verweise USING btree (value);
33CREATE INDEX idx_zusaetze ON public.telefonbuch USING btree (zusaetze); 45CREATE INDEX idx_zip ON indexed_view_zip USING btree (value);
46CREATE INDEX idx_ort ON indexed_view_ort USING btree (value);
47CREATE INDEX idx_web ON indexed_view_web USING btree (value);
48CREATE INDEX idx_email ON indexed_view_email USING btree (value);
34 49
35CREATE INDEX trgm_idx_nachname ON public.telefonbuch USING gin (nachname gin_trgm_ops); 50# full text indexes
36CREATE INDEX trgm_idx_rufnummer ON public.telefonbuch USING gin (rufnummer gin_trgm_ops); 51CREATE INDEX trgm_idx_nachname ON indexed_view_nachname USING gin (value gin_trgm_ops);
37CREATE INDEX trgm_idx_verweise ON public.telefonbuch USING gin (verweise gin_trgm_ops); 52CREATE INDEX trgm_idx_vorname ON indexed_view_vorname USING gin (value gin_trgm_ops);
38CREATE INDEX trgm_idx_vorname ON public.telefonbuch USING gin (vorname gin_trgm_ops); 53CREATE INDEX trgm_idx_vorwahl ON indexed_view_vorwahl USING gin (value gin_trgm_ops);
39CREATE INDEX trgm_idx_zusaetze ON public.telefonbuch USING gin (zusaetze gin_trgm_ops); 54CREATE INDEX trgm_idx_rufnummer ON indexed_view_rufnummer USING gin (value gin_trgm_ops);
55CREATE INDEX trgm_idx_strasse ON indexed_view_strasse USING gin (value gin_trgm_ops);
56CREATE INDEX trgm_idx_hausnummer ON indexed_view_hausnummer USING gin (value gin_trgm_ops);
57CREATE INDEX trgm_idx_zusaetze ON indexed_view_zusaetze USING gin (value gin_trgm_ops);
58CREATE INDEX trgm_idx_verweise ON indexed_view_verweise USING gin (value gin_trgm_ops);
59CREATE INDEX trgm_idx_zip ON indexed_view_zip USING gin (value gin_trgm_ops);
60CREATE INDEX trgm_idx_ort ON indexed_view_ort USING gin (value gin_trgm_ops);
61CREATE INDEX trgm_idx_web ON indexed_view_web USING gin (value gin_trgm_ops);
62CREATE INDEX trgm_idx_email ON indexed_view_email USING gin (value gin_trgm_ops);
63
64# slow way:
65SELECT * FROM Telefonbuch WHERE nachname = 'Engling';
66
67# using the index:
68SELECT * 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
71WHERE indexed_view_nachname.value = 'CCC'
72AND 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
41SELECT *, ST_AsText(coords) FROM Telefonbuch WHERE nachname = 'Engling';