elastic search für Arme

Fragen und Probleme rund um die Abfragespache SQL.
pogomips
Benutzer
Beiträge: 73
Registriert: Fr 18. Nov 2016, 09:55

elastic search für Arme

Beitrag von pogomips » Mi 28. Nov 2018, 21:14

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.

akretschmer
Erfahrener Benutzer
Beiträge: 7532
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: elastic search für Arme

Beitrag von akretschmer » Mi 28. Nov 2018, 21:57

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.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

pogomips
Benutzer
Beiträge: 73
Registriert: Fr 18. Nov 2016, 09:55

Re: elastic search für Arme

Beitrag von pogomips » Do 29. Nov 2018, 08:54

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.

castorp
Erfahrener Benutzer
Beiträge: 1086
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: elastic search für Arme

Beitrag von castorp » Do 29. Nov 2018, 09:00

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:

Code: Alles auswählen

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

Code: Alles auswählen

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);
....
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

castorp
Erfahrener Benutzer
Beiträge: 1086
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: elastic search für Arme

Beitrag von castorp » Do 29. Nov 2018, 09:54

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:

Code: Alles auswählen

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

Code: Alles auswählen

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:

Code: Alles auswählen

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:

Code: Alles auswählen

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:
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

pogomips
Benutzer
Beiträge: 73
Registriert: Fr 18. Nov 2016, 09:55

Re: elastic search für Arme

Beitrag von pogomips » Do 29. Nov 2018, 11:42

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!

castorp
Erfahrener Benutzer
Beiträge: 1086
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: elastic search für Arme

Beitrag von castorp » Do 29. Nov 2018, 11:59

Das mit den Einzelindizes ist tatsächlich überflüssig. GIN kann ja alle Spalten effizient verwenden, nicht nur die am Anfang.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

akretschmer
Erfahrener Benutzer
Beiträge: 7532
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: elastic search für Arme

Beitrag von akretschmer » Do 29. Nov 2018, 12:03

falls die Spalten NULL enthälten können, sollte man noch coalesce() mit einbauen...
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

pogomips
Benutzer
Beiträge: 73
Registriert: Fr 18. Nov 2016, 09:55

Re: elastic search für Arme

Beitrag von pogomips » Do 29. Nov 2018, 17:40

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:

Code: Alles auswählen

  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.

castorp
Erfahrener Benutzer
Beiträge: 1086
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: elastic search für Arme

Beitrag von castorp » Do 29. Nov 2018, 17:58

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 ... aries.html
https://www.postgresql.org/docs/current ... ation.html

Zum "reinschauen" gibt es diverse "Debug" Funktionen:

https://www.postgresql.org/docs/current ... EBUG-TABLE
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

Antworten