Ich hab eine Tabelle mit ca. 13 Millionen Adressdaten.
In dieser Tabelle befinden sich die Spalten ID, Hnr, Strasse, PLZ, Ort und Land.
Nun möchte ich eine zusätzliche Spalte in dieser Tabelle definieren, die folgenden Eintrag hat:
ID || ’ ’ || HNr || ’ ’ || Strasse || ’ ’ || PLZ || ’ ’ || Ort || ’ ’ || Land
Da der Datensatz so gross ist sehe ich keine Möglichkeit dies in einem Editor oder Excell zu machen.
Gibt es eine Möglichkeit dies automatisch mit einer Funktion durchzuführen?
z.B. in etwa so:
ALTER TABLE testtabelle ADD COLUMN test text;
INSERT INTO testtabelle (test) VALUES (…Funktion…)
Oder gibt es hierfür eine viel einfachere Möglichkeit?
Es gibt keinen Grund die Daten doppelt zu speichern.
Definiere einfach eine VIEW welche die “berechnete” Spalte enthält
CREATE VIEW v_test
AS
SELECT id,
ID || ' ' || HNr || ' ' || Strasse || ' ' || PLZ || ' ' || Ort || ' ' || Land as komplette_adresse
FROM testtabelle
Übrigens: wenn eine der Spalten NULL ist, liefert der ganze Ausdruck NULL zurück. Vermutlich willst Du noch ein coalesce(, ‘’) für jede (nullable) Spalte verwenden
Doch so viel ich weiss, ist es nicht möglich einen Index über eine Sicht zu definieren.
Die neue Tabellenspalte brauch ich, damit ich einen GIST Index für meine Abfrage verwenden kann.
Abfrage:
PERFORM set_limit(sim_minimum);
FOR rec IN SELECT Hnr, Strasse, PLZ, Ort, Land, astext(geom), similarity(Input_norm, Strasse || ’ ’ || Hnr || ’ ’ || PLZ || ’ ’ || Ort || ’ ’ || Land) AS sim FROM testtabelle
WHERE Input_norm % (Strasse || ’ ’ || HNr || ’ ’ || PLZ || ’ ’ || Ort || ’ ’ || Land)
ORDER BY sim, street DESC
Limit 5
LOOP
Ach so es geht um’s Indizieren, gar nicht um eine berechnte Spalte.
Da müsste es doch reichen einen entsprechenden Index auf der Basis-Tabelle zu definieren.
Ich kenne mich mit GIST Indizes überhaupt nicht aus. Aber einen normalen Index kann man über den Ausdruck definieren.
Einen normalen Index könnte man problemlos so definieren:
CREATE INDEX idx_sim ON testtabelle ( (Strasse || ' ' || HNr || ' ' || PLZ || ' ' || Ort || ' ' || Land) )
Geht sowas mit einem GIST Index nicht?
Was genau macht denn der % Operator in Deinem Beispiel?
Wäre die Volltextsuche vielleicht eine Alternative?
Edit: wenn das mit dem Index gar nicht klappt und Du doch eine eigene Spalte machen willst, dann dürfte die schnellste Methode via ADD COLUMN und UPDATE sein:
Du musst dann natürlich über einen Trigger dafür sorgen, dass die Spalte auch immer aktualisiert wird.
Wie gesagt: Volltextsuche ist vielleicht doch die bessere Alternative.
Was soll da drin stehen? Nur die mit || verbundenen Werte anderer Spalten, um dann darüber einen Index zu legen? Reicht nicht auch sowas:
test=# create table tme (a text, b text, c text);
CREATE TABLE
test=*# create index idx_tme on tme((a || b || c));
CREATE INDEX
test=*# \d tme
Table "public.tme"
Column | Type | Modifiers
--------+------+-----------
a | text |
b | text |
c | text |
Indexes:
"idx_tme" btree (((a || b) || c))
Wieviel Zeilen gibt es? Wie sieht das select und das explain analyse komplett aus? Und ja: da ist ein function scan, hast Du einen passenden Index /dafür/?
Nee das war ein Beispiel aus einer identischen Tabelle mit nur 6 Zeilen.
Hier das Beispiel mit der kompletten Tabelle:
Die Abfrage lautet so
SELECT * FROM tm_match2('Dinkelbergstrasse 4 4132 Muttenz Switzerland') AS (housenumber VARCHAR, housename
VARCHAR, street VARCHAR, postcode VARCHAR, city VARCHAR, region VARCHAR, country VARCHAR, lat double precision, long double precision, rating numeric)
ORDER BY rating DESC;
Index zu einem Function Scan hab ich keinen. Würde das die Laufzeit drastisch verringern?
Da ich mit pg_trgm arbeite, sollte ich doch einen GIST Index verwenden, oder versteh ich da was falsch?
Was ist das da für eine Funktion tm_match2? Da drin liegt die Zeit verbuddelt.
Ist die selber geschrieben?
Ansonsten können funktionale Indexe schon was bringen, hier ein Beispiel, was aber für Dich wohl nicht hilft:
test=*# explain analyse select * from words where length(t) = 15;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on words (cost=0.00..1975.53 rows=493 width=9) (actual time=0.405..69.707 rows=837 loops=1)
Filter: (length(t) = 15)
Total runtime: 70.748 ms
(3 rows)
test=*# create index idx_w on words((length(t)));
CREATE INDEX
test=*# explain analyse select * from words where length(t) = 15;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on words (cost=12.08..532.78 rows=493 width=9) (actual time=0.720..2.804 rows=837 loops=1)
Recheck Cond: (length(t) = 15)
-> Bitmap Index Scan on idx_w (cost=0.00..11.96 rows=493 width=0) (actual time=0.606..0.606 rows=837 loops=1)
Index Cond: (length(t) = 15)
Total runtime: 3.853 ms
(5 rows)
Ja die Funktion ist selber geschrieben. Sie führt das Matchingverfahren für den Inputstring durch.
z.B. Matching, wenn Hnr, Strasse, PLZ, Ort und Land gegeben sind:
-- Matchingverfahren HNr, Strasse, PLZ, Ort, Land
PERFORM set_limit(sim_minimum);
FOR rec IN SELECT housenumber, housename, street, postcode, city, region, country, x(geom), y(geom), ((similarity(Input_norm, housenumber || street || postcode || city || country))*100)::numeric(5,2) AS sim FROM address
WHERE Input_norm % (housenumber || street || postcode || city || country)
ORDER BY sim DESC
Limit 5
LOOP
RETURN NEXT rec;
END LOOP;
Also wenn das Performance Problem in der Funktion tm_match2 liegt, kann es nur an der oben beschriebenen Matching-Verfahren liegen.
Nun, das führt sehr sicher zu seine seq-scan der Tabelle. Vielleicht solltest Du analog zum Beispiel in der Doku:
CREATE TABLE test_trgm (t text);
CREATE INDEX trgm_idx ON test_trgm USING gist (t gist_trgm_ops);
einen GIST-Index auf (housenumber || street || postcode || city || country) anlegen. Ob das hilft, probier es, ich habe da keine Erfahung mit. So jedenfalls bekommst Du einen Full-table-seq-scan, unabhängig vom LIMIT.
Und ja: Innerhalb von Funktionen, wo im WHERE ein übergebener Parameter steht, kippen Pläne oft in einen seq-scan, da der Planner zur Planzeit (create function …) ja nicht wissen kann, welchen Wert der Parameter zur Ausführungszeit haben kann. Also, erst mal ‘trocken’ schauen, ob ein Index nützt. Gibt es dann in der Version als Function Probleme: EXECUTE hilft.
Eine Frage hätte ich jedoch noch:
Wenn ich EXPLAIN ANALYZE an der SELECT Anfrage durchführe, wird dann der GIST Index den ich verwende für (housenumber || street || postcode || city || country) im QUERY PLAN aufgeführt?
Oder sollte die Analyse an der Funktion (FOR rec IN SELECT) selber durchgeführt werden, um zu erkennen ob der Index verwendet wird?