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?
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
Nee das war ein Beispiel aus einer identischen Tabelle mit nur 6 Zeilen.
Hier das Beispiel mit der kompletten Tabelle:
Die Abfrage lautet so
Zeilen: 13'507'900Code: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;
EXPLAIN ANALYZE SELECT...
Index zu einem Function Scan hab ich keinen. Würde das die Laufzeit drastisch verringern?Code:"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"
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:
AndreasCode: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)
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
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:
Also wenn das Performance Problem in der Funktion tm_match2 liegt, kann es nur an der oben beschriebenen Matching-Verfahren liegen.Code:-- 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;
Nun, das führt sehr sicher zu seine seq-scan der Tabelle. Vielleicht solltest Du analog zum Beispiel in der Doku:
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.Code:CREATE TABLE test_trgm (t text); CREATE INDEX trgm_idx ON test_trgm USING gist (t gist_trgm_ops);
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
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
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?
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
Links: Tobias Bauer - IT- und Funkdienstleistungen - Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen - Funkforum
Lesezeichen