Seite 2 von 2 ErsteErste 12
Ergebnis 11 bis 20 von 20

Thema: Neue Spalte aus vorhanden Spalten

  1. #11
    castorp ist offline Erfahrener Benutzer
    Registriert seit
    27.06.2009
    Beiträge
    493

    Standard

    Zitat Zitat von akretschmer Beitrag anzeigen
    Was soll da drin stehen? Nur die mit || verbundenen Werte anderer Spalten, um dann darüber einen Index zu legen? Reicht nicht auch sowas
    Hatte ich ja schon vorgeschlagen, aber der (GIST) Index wird wohl nicht verwendet (siehe Posting #7)
    A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

  2. #12
    tme2525 ist offline Benutzer
    Registriert seit
    27.06.2010
    Beiträge
    34

    Standard

    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"

  3. #13
    castorp ist offline Erfahrener Benutzer
    Registriert seit
    27.06.2009
    Beiträge
    493

    Standard

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

  4. #14
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Nähe Dresden
    Beiträge
    5.337

    Standard

    Zitat Zitat von tme2525 Beitrag anzeigen
    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"
    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
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  5. #15
    tme2525 ist offline Benutzer
    Registriert seit
    27.06.2010
    Beiträge
    34

    Standard

    Nee das war ein Beispiel aus einer identischen Tabelle mit nur 6 Zeilen.

    Hier das Beispiel mit der kompletten Tabelle:
    Die Abfrage lautet so
    Code:
    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...
    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"
    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?

  6. #16
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Nähe Dresden
    Beiträge
    5.337

    Standard

    Zitat Zitat von tme2525 Beitrag anzeigen
    Nee das war ein Beispiel aus einer identischen Tabelle mit nur 6 Zeilen.

    Hier das Beispiel mit der kompletten Tabelle:
    Die Abfrage lautet so
    Code:
    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...
    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"
    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:

    Code:
    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
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  7. #17
    tme2525 ist offline Benutzer
    Registriert seit
    27.06.2010
    Beiträge
    34

    Standard

    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:
    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;
    Also wenn das Performance Problem in der Funktion tm_match2 liegt, kann es nur an der oben beschriebenen Matching-Verfahren liegen.

  8. #18
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Nähe Dresden
    Beiträge
    5.337

    Standard

    Zitat Zitat von tme2525 Beitrag anzeigen
    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:
    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;
    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:

    Code:
    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
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  9. #19
    tme2525 ist offline Benutzer
    Registriert seit
    27.06.2010
    Beiträge
    34

    Standard

    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?

  10. #20
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Nähe Dresden
    Beiträge
    5.337

    Standard

    Zitat Zitat von tme2525 Beitrag anzeigen
    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
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

Seite 2 von 2 ErsteErste 12

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  

Links: Tobias Bauer - IT- und Funkdienstleistungen   -   Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen   -   Funkforum