Ich möchte einige grundlegende Geokodierung von Adressen mit Postgres durchführen. Ich habe eine Adresstabelle, die ungefähr 1 Million rohe Adressenstrings hat:
%Vor%Ich habe auch eine Tabelle mit Standortdaten:
%Vor%Die meisten Adress-Strings enthalten Postleitzahlen, also war mein erster Versuch, einen Like und einen lateralen Join zu machen:
%Vor%Das ergab das erwartete Ergebnis, aber es war langsam. Hier ist der Abfrageplan:
%Vor%Ich habe versucht, einen GIST-Trigramm-Index zur Adressspalte hinzuzufügen, wie in Ссылка erwähnt, aber der Abfrageplan für die obige Abfrage verwendet es nicht, und der Abfrageplan unverändert.
%Vor% Ich muss die Reihenfolge entfernen und in der Querverknüpfungsabfrage für den Index, der verwendet werden soll, begrenzen, was mir nicht die Ergebnisse bringt, die ich möchte. Hier ist der Abfrageplan für die Abfrage ohne ORDER oder LIMIT :
Gibt es etwas, was ich tun kann, um die Abfrage den Index zu verwenden, oder gibt es eine bessere Möglichkeit, diese Abfrage neu zu schreiben?
Die Abfrage kann den Index für Principal nicht verwenden. Sie würden einen Index für die Tabelle locations benötigen, aber den, den Sie haben, befindet sich in der Tabelle addresses .
Sie können meinen Anspruch überprüfen, indem Sie Folgendes festlegen:
%Vor%(Nur in Ihrer Sitzung und nur zum Debuggen. Verwenden Sie das Programm nie in der Produktion.) Es ist nicht so, dass der Index teurer wäre als ein sequenzieller Scan. Postgres kann sie einfach nicht für Ihre Abfrage verwenden überhaupt .
Beiseite: [INNER] JOIN ... ON true ist nur eine unbeholfene Art, CROSS JOIN ...
ORDER und LIMIT ? Weil Postgres dieses einfache Formular in folgendes umschreiben kann:
%Vor%Sie sehen genau den gleichen Abfrageplan. (Zumindest mache ich bei meinen Tests auf Postgres 9.5.)
Sie benötigen einen Index für locations.postalcode . Und bei der Verwendung von LIKE oder ILIKE müssten Sie auch den indizierten Ausdruck ( postalcode ) auf die linke Seite des Operators bringen. ILIKE wird mit dem Operator ~~* implementiert und dieser Operator hat keine COMMUTATOR (eine logische Notwendigkeit), so dass es nicht möglich ist, Operanden umzukehren. Detaillierte Erklärung in diesen verwandten Antworten:
Eine Lösung ist die Verwendung des Trigrammähnlichkeitsoperators % oder umgekehrt, der Entfernungsoperator <-> in einer nearest neighbor -Abfrage (jeder ist Kommutator für sich selbst, so dass Operanden die Plätze frei wechseln können):
Finde die ähnlichste postalcode für jede address und überprüfe dann, ob diese postalcode tatsächlich vollständig übereinstimmt.
Auf diese Weise wird ein längerer postalcode automatisch bevorzugt, da er ähnlicher ist (kleinerer Abstand) als ein kürzerer postalcode , der ebenfalls übereinstimmt.
Ein bisschen Unsicherheit bleibt. Abhängig von möglichen Postleitzahlen könnte es falsche Übereinstimmungen aufgrund übereinstimmender Trigramme in anderen Teilen der Kette geben. Es gibt nicht genug Informationen in der Frage, um mehr zu sagen.
Hier ist [INNER] JOIN anstelle von CROSS JOIN sinnvoll, da wir eine tatsächliche Join-Bedingung hinzufügen.
Dies kann sehr effizient durch GiST-Indizes, aber nicht durch GIN-Indizes implementiert werden.
Also:
%Vor%Es ist weit hergeholt, aber wie funktioniert die folgende Alternative?
%Vor%Es kann funktionieren, wenn Sie die seitliche Verbindung von innen nach außen drehen. Aber selbst dann könnte es immer noch sehr langsam sein
%Vor%Der Nachteil ist, dass Sie Paging nur für Postleitzahlen und nicht für Adressen implementieren können.
Tags und Links postgresql indexing nearest-neighbor postgresql-9.4 query-optimization