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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
|
\timing on
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 '/Users/erdgeist/Coding/Telefonbuch/work/telefonbuch.txt' WITH NULL AS '';
-- # Clean up the old ones
DROP TABLE IF EXISTS table_nachname, table_vorname, table_zusaetze, table_strasse, table_hausnummer, table_zip, table_ort, table_verweise, table_vorwahl, table_rufnummer, table_web, table_email;
-- # tables we use for indexing
CREATE TABLE table_nachname ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_vorname ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_zusaetze ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_strasse ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_hausnummer ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_zip ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_ort ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_verweise ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_vorwahl ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_rufnummer ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_web ( telefonbuch_id integer, offs integer, value text);
CREATE TABLE table_email ( telefonbuch_id integer, offs integer, value text);
-- # Take copies of all content rows for indexing
INSERT INTO table_nachname ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(nachname ) WITH ORDINALITY u(value, offs);
INSERT INTO table_vorname ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(vorname ) WITH ORDINALITY u(value, offs);
INSERT INTO table_zusaetze ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(zusaetze ) WITH ORDINALITY u(value, offs);
INSERT INTO table_strasse ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(strasse ) WITH ORDINALITY u(value, offs);
INSERT INTO table_hausnummer ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(hausnummer ) WITH ORDINALITY u(value, offs);
INSERT INTO table_zip ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(zip ) WITH ORDINALITY u(value, offs);
INSERT INTO table_ort ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(ort ) WITH ORDINALITY u(value, offs);
INSERT INTO table_verweise ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(verweise ) WITH ORDINALITY u(value, offs);
INSERT INTO table_vorwahl ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(vorwahl ) WITH ORDINALITY u(value, offs);
INSERT INTO table_rufnummer ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(rufnummer ) WITH ORDINALITY u(value, offs);
INSERT INTO table_web ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(web ) WITH ORDINALITY u(value, offs);
INSERT INTO table_email ( telefonbuch_id, offs, value ) SELECT id, offs, value FROM Telefonbuch CROSS JOIN unnest(email ) WITH ORDINALITY u(value, offs);
ALTER TABLE table_nachname ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_vorname ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_zusaetze ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_strasse ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_hausnummer ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_zip ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_ort ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_verweise ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_vorwahl ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_rufnummer ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_web ADD PRIMARY KEY (telefonbuch_id, offs);
ALTER TABLE table_email ADD PRIMARY KEY (telefonbuch_id, offs);
-- # equality indexes
CREATE INDEX idx_nachname ON table_nachname USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_vorname ON table_vorname USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_zusaetze ON table_zusaetze USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_strasse ON table_strasse USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_hausnummer ON table_hausnummer USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_zip ON table_zip USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_ort ON table_ort USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_verweise ON table_verweise USING btree (value, telefonbuch_id, offs) WHERE LENGTH(value) < 256;
CREATE INDEX idx_vorwahl ON table_vorwahl USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_rufnummer ON table_rufnummer USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_web ON table_web USING btree (value, telefonbuch_id, offs);
CREATE INDEX idx_email ON table_email USING btree (value, telefonbuch_id, offs);
-- # full text indexes
CREATE INDEX trgm_idx_nachname ON table_nachname USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_vorname ON table_vorname USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_zusaetze ON table_zusaetze USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_strasse ON table_strasse USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_hausnummer ON table_hausnummer USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_zip ON table_zip USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_ort ON table_ort USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_verweise ON table_verweise USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_vorwahl ON table_vorwahl USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_rufnummer ON table_rufnummer USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_web ON table_web USING gin (value gin_trgm_ops, telefonbuch_id, offs);
CREATE INDEX trgm_idx_email ON table_email USING gin (value gin_trgm_ops, telefonbuch_id, offs);
-- # 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
# Tricks: SELECT reltuples::bigint AS estimate FROM pg_class WHERE oid = 'table_zusaetze'::regclass;
*/
|
