Mehr

Effizientes Abfragen einer nach Regionen partitionierten PostGIS-Tabelle

Effizientes Abfragen einer nach Regionen partitionierten PostGIS-Tabelle


Ich habe zwei PostGIS-Tabellen: eine vonStraßen, und einer vonRegionen, die, abgesehen davon, dass sie die gleiche räumliche Ausdehnung umfassen, a priori nichts miteinander zu tun haben. DasStraßenAbfragen, die ich ausführe, können auf eine bestimmte Region beschränkt werden, indem eine bekannte ID angegeben wird. Um dies auszunutzen, habe ich a . hinzugefügtregion_idSpalte zumStraßenTabelle (mit ast_intersects-basierte Update-Abfrage, die ziemlich teuer war, aber nur einmal durchgeführt wurde).

Meine Frage: Wenn dies einmal vorhanden ist, wird die Verwendung eines Indexes für dieStraßen.region_idSpalte ausreichen, um Abfragen dieses Typs maximal effizient zu machen:

Wählen Sie * aus Straßen mit region_id =  und…

Oder gibt es einen alternativen Mechanismus, der besser funktionieren würde?


Die Indizierung einer Spalte, die entweder in Joins oder in where-Klauseln verwendet wird, ist der beste (und einfachste) Weg, um eine Abfrage effizient zu gestalten, daher lautet die Antwort ja.

Ein weiterer Trick, den du ziehen könntest, ist zu CLUSTER die Tabelle gegen den Index (denken Sie daran, zu laufen VAKUUMANALYSE danach, um die Tabellenstatistik zu aktualisieren). CLUSTER erstellt eine Kopie der Tabelle (die Tabelle kann während des Clusterings nicht verwendet werden) und ordnet die Daten neu an, sodass identische Werte des Index zusammenhängend auf der Platte gespeichert werden (wodurch die Platten-Read-Ahead-Effizienz maximiert wird).

Lesen Sie unbedingt die Dokumentation zum Befehl CLUSTER, um zu verstehen, ob Sie ihn benötigen und wie er funktioniert.

Beachten Sie jedoch, dass Postgres den Index nicht verwendet, wenn die Bedingung IS NULL ist.