diff options
Diffstat (limited to 'src/postprocess/postgres.txt')
-rw-r--r-- | src/postprocess/postgres.txt | 76 |
1 files changed, 0 insertions, 76 deletions
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 @@ | |||
1 | CREATE TABLE telefonbuch ( | ||
2 | id SERIAL PRIMARY KEY, | ||
3 | presence_flag bigint, | ||
4 | reverse_flag bigint, | ||
5 | biz_flag bigint, | ||
6 | zip character varying(64)[] DEFAULT '{}', | ||
7 | nachname character varying(256)[] DEFAULT '{}', | ||
8 | vorname character varying(192)[] DEFAULT '{}', | ||
9 | zusaetze character varying(512)[] DEFAULT '{}', | ||
10 | strasse character varying(64)[] DEFAULT '{}', | ||
11 | hausnummer character varying(32)[] DEFAULT '{}', | ||
12 | verweise text[] DEFAULT '{}', | ||
13 | ort character varying(64)[] DEFAULT '{}', | ||
14 | vorwahl character varying(10)[] DEFAULT '{}', | ||
15 | rufnummer character varying(32)[] DEFAULT '{}', | ||
16 | web character varying(256)[] DEFAULT '{}', | ||
17 | email character varying(256)[] DEFAULT '{}', | ||
18 | coords character varying(32)[] DEFAULT '{}' | ||
19 | ); | ||
20 | |||
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 ''; | ||
22 | |||
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; | ||
35 | |||
36 | # equality indexes | ||
37 | CREATE INDEX idx_nachname ON indexed_view_nachname USING btree (value); | ||
38 | CREATE INDEX idx_vorname ON indexed_view_vorname USING btree (value); | ||
39 | CREATE INDEX idx_vorwahl ON indexed_view_vorwahl USING btree (value); | ||
40 | CREATE INDEX idx_rufnummer ON indexed_view_rufnummer USING btree (value); | ||
41 | CREATE INDEX idx_strasse ON indexed_view_strasse USING btree (value); | ||
42 | CREATE INDEX idx_hausnummer ON indexed_view_hausnummer USING btree (value); | ||
43 | CREATE INDEX idx_zusaetze ON indexed_view_zusaetze USING btree (value); | ||
44 | CREATE INDEX idx_verweise ON indexed_view_verweise USING btree (value); | ||
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); | ||
49 | |||
50 | # full text indexes | ||
51 | CREATE INDEX trgm_idx_nachname ON indexed_view_nachname USING gin (value gin_trgm_ops); | ||
52 | CREATE INDEX trgm_idx_vorname ON indexed_view_vorname USING gin (value gin_trgm_ops); | ||
53 | CREATE INDEX trgm_idx_vorwahl ON indexed_view_vorwahl USING gin (value 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 = 'CCC'; | ||
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 | ||
76 | |||