elastic search für Arme

Hallo,

vielleicht nicht das passende Unterforum, aber das könnte ja notfalls umgemodelt werden.

Ich habe für einen Kollegen heute einen GIN Index über ein paar Spalten (5 oder 6) gelegt, alles varchar zwischen 10 und >1000 Zeichen. Er macht brute force Suche aus einem Webformular, ein Eingabefeld, dessen Wert dann in mehreren Tabellenspalten gesucht wird, die Where Kriterien sind OR verknüpft. Da es mittlerweile ein paar Millionen Logdateneinträge sind, machte seine Lösung keinen Spaß mehr.

Der GIN Index über mehre Spalten mit ILIKE Abfrage hat sehr gut geholfen. Besonders weil es ein 2 Zeiler war, der nicht mal in die Logik eingegriffen hat, sondern eben nur einen speziellen Index aufgebaut hat:
Extension erzeugen plus
CREATE INDEX xy_idx ON mytable USING gin (colA gin_trgm_ops, colB gin_trgm_ops, …);

Winziger Schönheitsfehler vielleicht die Anwendbarkeit ab 3 Zeichen. Aber nur, weil es sonst keine Probleme gibt.

Nun 2 Fragen:
Gibt es andere Ansätze, die noch geeigneter sind. (Kleiner Aufwand, noch größere Wirkung)
Was empfiehlt sich, wenn man eine vorhandene Timestamp Spalte ebenfalls nutzbringend einbezihen möchte (weitere Beschleunigung).

Wäre für die Timespalte Partitionierung empfehlenswert? Ein Index “in Konkurrenz” zum GIN Index macht ja vermutlich keinen Sinn.
Es geht nur um eine interne Verwendung während der Entwicklung.

Partitionierung (insbesondere ab 11) ist geil - wenn man a) über viele Rows verfügt, b) diese in Abfragen gemäß der Partitionierung splitten kann und/oder man nach gewisser Zeit viele Rows löschen will/muß.
tl;dr
kommt drauf an.

Ok, wann kommt 11?! Ach ist schon da!
Nein, es ist nicht dringend, sozusagen Luxusprobleme.

Die zeitliche Eingrenzung von Logdaten ist natürlich sinnvoll bzw. eine Anforderung aus dem Tagesgeschäft (des Entwicklers). Es geht darum, wie es den Geräten geht (Logdaten) nach den letzten Änderungen, nicht wie es der DB dabei geht.

Aber im Grunde ergibt sich doch hier auch wieder die Frage, wie dann die reine Partitionierung auf Zeitebene mit weiterer Indizierung (GIN oder andere) zusammenspielt.

Habt ihr mal versucht statt einem großen Index über alle Spalten, einen Index pro Spalte zu verwenden?

Postgres ist recht gut darin mehrere Indizes in einer Abfrage zu kombinieren. Und typischerweise werden von einem Mehrspaltenindex nur die “vorderen” effizient genutzt (bin mir aber nicht ganz sicher wie das bei GIN Indizes aussieht)

Also statt:

CREATE INDEX xy_idx ON mytable USING gin (colA gin_trgm_ops, colB gin_trgm_ops, ..);

sowas

CREATE INDEX ON mytable USING gin (colA gin_trgm_ops);
CREATE INDEX ON mytable USING gin (colB gin_trgm_ops);
CREATE INDEX ON mytable USING gin (colC gin_trgm_ops);
....

Nachdem Du Elastic Search angesprochen hast:
Postgres hat eine recht gute Volltextsuche eingebaut (nicht ganz so ausgefeilt wie ElasticSearch, aber für viele Dinge ausreichend)

https://www.postgresql.org/docs/current/textsearch.html

Um das dann schnell zu machen, legt man einen GIN Index über eine sog. “tsvector” an. Der tsvector enthält dabei alle Spalten die durchsucht werden sollen:

create index on mytable using gin (to_tsvector('pg_catalog.german', col1||' '||col2||' '||col3));

und dann

select *
from mytable
where to_tsvector('pg_catalog.german', col1||' '||col2||' '||col3) @@ to_tsquery('Foobar');

Die Suche in einer Tabelle mit ca 1.5 Million Datensätzen (und 5 Spalten im Index) auf meinem Laptop (SSD Platte, Postgres 11) dauert dann ca. 25 Millisekunden.

Um die Abfragen zu vereinfachen kann man auch eine zusätzliche Spalten “search” erstellen die das Ergebnis des ts_vector() speichert. Diese kann man entweder manuell nach dem Laden der Logdateien via UPDATE befüllen oder mit einem Trigger. Der Index wird dann über die Spalte gelegt, und man muss im WHERE nicht immer den kompletten to_tsvector() Ausdruck wiederholen.

Oder man verpackt das Erstellen des tsvector in eine Funktion:

create or replace function create_search_vector(p_row mytable)
  returns tsvector
  immutable
as  
$$
  select to_tsvector(concat_ws(' ', p_row.col1, p_row.col2, p_row.col2, p_row.col3));
$$
language sql;

create index on mytable using gin (create_search_vector(mytable));

Dann wird auch die Abfrage einfacher:

select *
from mytable
where create_search_vector(mytable) @@ to_tsquery('foobar')

Den tsvector kann man auch noch optimieren, indem man verschiedenen Spalten verschiedene Prioritäten gibt (ein Treffer in “Computer” ist “wichtiger” als ein Treffer in “Dateiname” oder ähnliches)

Ein paar Artikel dazu:

Einzelindizierung mit Btree fällt ja wegen nicht greifendem Index bei Searchpattern %xy% raus.
Einzelindizierung mittels GIn wäre einen Versuch wert. Ich weiß nicht ob es viel bringt, weil ja nur der Index jeweils (etwas) kleiner wird, also weniger IO bei jeder einzelnen “Indexdurchquerung”, insgesamt aber vielleicht mehr. GIN Indexe sollen ja eh kompakt sein. Das hängt sicher vom indizierten Datenbestand ab, aber gerade Logdaten sind ja typischer Weise mit vielem statischen Kram versehen.

Die Textsuche sieht allerdings nach einem sehr vielversprechenden Tipp aus, werde ich mir anschauen, danke!

Das mit den Einzelindizes ist tatsächlich überflüssig. GIN kann ja alle Spalten effizient verwenden, nicht nur die am Anfang.

falls die Spalten NULL enthälten können, sollte man noch coalesce() mit einbauen…

nebenbei: Grad einmal gepostet, aber nichts angekommen, nochmal:

Einzelne GIN Indexe bringen nichts, sie werden nicht verwendet mit den OR Kriterien.

Volltextsuche gefällt, sie fühlt sich schneller an, in Summe etwas schwer zu sagen, je nach Suchanfrage. Aber ich habe es noch nicht richtig verstanden.
Dass bestimmte Worte nicht gesucht werden, ok, kein at, kein the usw. bei englischer locale.
Aber die Suche nach ‘failed’ zeigt keine Ergebnisse. ‘fail’ funktioniert wieder wie erwartet.
Vergleiche:

  select to_tsquery('failed');
  select to_tsquery('fail');

  select to_tsvector('english','failed');

Schön wäre jetzt, wenn to_tsquery gleich lexiert ausgeben würde oder meinetwegen plainto_tsquery. Aber so bleibt irgendwie im Dunkeln, was wie wann draus wird.

Liegt vermutlich an den Stopwords. FTS ja auch eher für “natürliche Sprache” gedacht.

Aber Du könntest mal mit dictionaries und configuration rumspielen:

https://www.postgresql.org/docs/current/textsearch-dictionaries.html
https://www.postgresql.org/docs/current/textsearch-configuration.html

Zum “reinschauen” gibt es diverse “Debug” Funktionen:

https://www.postgresql.org/docs/current/functions-textsearch.html#TEXTSEARCH-FUNCTIONS-DEBUG-TABLE

Danke, damit steigt der Rechercheaufwand (und vielleicht Implementierung/Configuration) im Vergleich zur GIN dann doch stark an.
Mal sehen, ob der Kollege das braucht …
Ich werde mich melden, wenn ich weiß warum es ‘failed’. Es muss ja sowas wie ein Stopp Word sein, nur leuchtet das bei diesem Wort nicht ein.

Das Beispiel funktioniert bei mir auch (jenachdem welchen Bastelstand ich in der Searchkonfiguration verwende)

Das gilt auch, wenn ich Dein Beispiel oder andere angleiche an die Fehlersituation. Varchar Spalten, mehr Suchspalten, GIN index dazu, …
… also ist es ein projektspezifisches Phänomen.

Ich kann als “Workaround” mit Suchwort:* arbeiten, das reicht erstmal.
P.S.: Und es ist schneller, als GIN mit Trigrams.


Noch was von mir zu meiner Frage:
Ich bin bei der Suche nach Indexalternativen über RUM gestolpert. Ich hätte zwar gedacht, sie nennen den neuen nach GIN dann Wodka, aber vielleicht kommt der Wodka Index später noch.
Die Funktionalität sieht vielversprechend aus und löst wahrscheinlich genau mein Problem.
Man kann damit u.a. weitere Spalten indizieren, z.B. einen Timestamp mit reinnehmen und so auch eine brauchbare zeitliche Einschränkung oder - Sortierung via Index erschlagen.
Ist aber Stand jetzt bei mir noch Theorie. Die Extension gehört zu Postgres Pro Enterprise. (Kannte ich nicht) Allerdings gibt es das auch auf Github als extension. Hab nicht ganz verstanden, warum Enterprise Pro und Github gleichzeitig, dafür nicht in Standard PG, aber bin noch nicht dazu gekommen, das auszuprobieren.

VODKA gibt es auch schon. Auf Oleg und seine Freunde ist da Verlaß :wink:

Abgehangen ist natürlich sicherer, kann ich so gesehen verstehen.

Vodka scheint ja schon älter zu sein, eher ein Experiment?
Hab mal mit Rum angefangen, eine analoge Lösung zu basteln. Mal schauen, was draus wird.

RUM benötigt offenbar native ts_vector feldtypen, jedenfalls ist es mir nicht gelungen, einen Index ähnlich zur GIN Variante zu erstellen, der auf vorhandenen varchar Felder läuft. Teilworte/Wildcard Suche scheint analog zu GIN auch nicht vorgesehen.
Damit ist es erstmal zu viel Änderungsaufwand und zu wenig Effekt gegen die GIN basierte Textvektorsuche.