Was sind optimale Indexe für GIS?

Hallo zusammen,

ich habe unsere Struktur für Geometien in der Datenbanktabelle mit einem GIST-Index versehen. Was die Performance von Verschneidungen und Prüfung der Geometrien verbessert.
Nun sind in der Tabelle die Datensätze Historisch aufgebaut. z.B.

jahr id geom
2015 1 polygon(…)
2016 1 polygon(…)
u.s.w

Einige Tabellen haben ca. 500000-800000 Datensätze mit Geometrien, und das Anzeigen z.B. im QGIS mit Filter auf Jahr=2016 ergibt ca. 100000 Datensätze und ist manchmal ziemlich träge.
Kann man da die Leistung für QGIS vielleicht mit einem anderen INDEX optimieren?

LG
Thomas

nun ja, wenn da 100.000 von 800.000 Datensätze anzuzeigen sind, wird ein Index wenig selektiv sein und das eher in Richtung seq-scan kippen. Falls die Records auch physisch nach Jahr geordnet in der Tabelle sind (weil sie so angelegt wurden und es nie Updates und/oder Deletes gab), könnte aber ein BRIN helfen (auch dem Jahr). Alternativ könntest Du auch Partitioning versuchen (nach Jahr).

Hallo,

ja meistens (zu 90%) werden die Datensätze Jahrweise importiert oder angelegt. Also die Wahrscheinlichkeit, dass Datensatz 1-100000 z.B. das Jahr 2015 und die Datensätze 100001-200000 das Jahr 2016 darstellen ist ziemlich hoch. Ist jetzt nur als Beispiel, wir haben auch Bereiche wo pro Jahr nur 20000 Datensätze anfallen, aber auch Bereiche wo 150K nicht selten sind. Aber es läuft immer nach dem gleichen Schema ab. Wir bekommen die Daten für einen bestimmten Zeitraum und importieren Sie dann in unsere DB-Struktur um sie auszuwerten.

Wie funktioniert die angesprochene Partitionierung?

Thomas

versuche erst einmal mit einem BRIN-Index auf der Jahresspalte. BRIN-Indexe sind SEHR, SEHR klein und wenn das Jahr mit in der Abfrage ist, sollte das schon was bringen. Partitionierung - das wäre jetzt deutlich mehr Aufwand im Nachgang. Welche Version habt ihr?

Okay dann versuchen wir das erstmal mit dem BRIN-Index.
Wir benutzen noch 13.9