Neue Spalte aus vorhanden Spalten

Hallo

Folgendes würd ich gerne machen:

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?

Freundliche Grüsse

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

Die Idee ist gut.

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

RETURN NEXT rec;

END 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?

Hab das mal ausprobiert

Den GIST Index wollte ich mit folgendem Befehl kreieren:

CREATE INDEX idx_kombi
  ON address
  USING gist((housenumber || ' ' || street || ' ' ||  postcode || ' ' || city || ' ' ||  country) gist_trgm_ops);

Leider hat er das nicht verstanden und den Index in dieser Form gemacht:

CREATE INDEX idx_kombi
  ON address
  USING gist
  (((((((((housenumber::text || ' '::text) || street::text) || ' '::text) || postcode::text) || ' '::text) || city::text) || ' '::text) || country::text) gist_trgm_ops);

Schade eigentlich.

Die Bemerkung verstehe ich nicht.

Wird der Index denn verwendet?

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:

ALTER TABLE orginal ADD COLUMN volle_adresse text;
UPDATE orginal 
  SET volle_addresse = Strasse || ' ' || HNr || ' ' || PLZ || ' ' || Ort || ' ' || Land;

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.

Merci für die Antwort

Den GIST Index verwendet er nicht.

Volltextsuche kommt nicht in Frage, weil ich ein Rating brauch.
Dafür verwende ich die pg_trgm similarity Funktion und den % Operator.

So wie ich das sehe, ist die einzige Möglichkeit eine neue Spalte zu definieren. Leider kenn ich mich mit Trigger überhaupt nicht aus.

Hat sonst vielleicht noch jemand eine Idee, wie ich es ohne Trigger machen könnte?
Ist dies überhaupt möglich?

Gruss

Nein, ohne Trigger kannst Du die zusätzliche Spalte nicht akutell halten.

Könntest du mir vielleicht ein Beispiel geben, wie der Trigger aussehen müsste.

Würde mir sehr weiterhelfen.

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))

Andreas

Hatte ich ja schon vorgeschlagen, aber der (GIST) Index wird wohl nicht verwendet (siehe Posting #7)

Das Problem ist, dass bei diesem Verfahren der Index nicht verwendet wird:

EXPLAIN ANALYZE SELECT…

“Sort (cost=309.83…312.33 rows=1000 width=272) (actual time=7.441…7.445 rows=5 loops=1)”
" Sort Key: rating"
" Sort Method: quicksort Memory: 17kB"
" -> Function Scan on tm_match2 (cost=0.00…260.00 rows=1000 width=272) (actual time=7.419…7.423 rows=5 loops=1)"
“Total runtime: 7.471 ms”

5 Datensätze aus 12 Millionen in 7ms.
Das ist doch eigentlich schnell genug?

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/?

Andreas

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;

Zeilen: 13’507’900

EXPLAIN ANALYZE SELECT…

"Sort  (cost=309.83..312.33 rows=1000 width=272) (actual time=133124.934..133124.938 rows=5 loops=1)"
"  Sort Key: rating"
"  Sort Method:  quicksort  Memory: 17kB"
"  ->  Function Scan on tm_match2  (cost=0.00..260.00 rows=1000 width=272) (actual time=133124.919..133124.923 rows=5 loops=1)"
"Total runtime: 133124.964 ms"

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)

Andreas

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.

Andreas

Danke für die Tipps. Ich werd die ausprobieren.

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?

Ob der Index genutzt wird, verrät Dir EXPLAIN ANALYSE.
An der Funktion selber ist das schwer zu erkennen, das ist eher eine Blackbox.

Andreas