1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
|
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
|
