Volltextsuche / Jokersuche auf Indizierte Spalte

Hallo,

wie im Titel erwähnt würde ich gerne eine Jokersuche auf meine Index-Spalte nutzen.
Dazu habe ich mir einen Index über die unten stehenden Befehle erstellt.

Soweit ich das verstanden habe, wir nun der Inhalt der ursprünglichen Spalte gefiltert und die Stammform der Wörter in die tsvector-Spalte übernommen, auf die man später seine
Suchabfrage abfeuert.
Mögliches select wäre dann:
SELECT * FROM <tbl_name> WHERE <index_col> @@ to_tsquery(‘default_ger’, ‘Suchbegriff1 | Suchbegriff2’);

Ich würde jetzt gerne eine Jokersuche machen und z.B. nach ‘Suchbe%’ suchen.
So funktioniert das nur leider nicht. Ich erhalte keine Treffer mehr.

In der Dokumentation steht leider auch nichts dazu drin.

Ich hoffe Ihr könnt mir weiterhelfen.
Vielen Dank im Voraus.

[PostgreSQL Version 9.0]

Gruß Manuel





Volltext Index erstellen:
CREATE TEXT SEARCH CONFIGURATION default_ger (COPY=german);
ALTER TABLE <tbl_name> ADD COLUMN <index_col> tsvector;
UPDATE <tbl_name> SET <index_col>=to_tsvector(‘default_ger’, <tbl_spalte>);
VACUUM FULL ANALYZE;
CREATE INDEX <index_name> ON memo USING gin(<tbl_name>); --gin bzw. gist
VACUUM FULL ANALYZE;

Nachtrag: Soweit ich das jetzt recherchiert habe, kann man keine Substring-Suche (Jokersuche) ausführen.

Was mit trotzdem stutzig macht ist folgendes:
Eigentlich hatte ich eine Verbesserung der Abfragedauer durch die Indizierung erwartet.

Zum Testen habe ich mir hierfür 3 Datenbanken (20.000 Einträge in meiner Tabelle) erstellt.

  1. Ohne Indize
  2. Mit Hash-Indize
  3. Gist/Gin-Indize

Auf 1. und 2. habe ich ein Testselekt mit LIKE ausgeführt.
Auf 3. habe ich das Testselekt mit der Vorgegeben Syntax verwendet. (SELECT * FROM <tbl_name> WHERE <index_col> @@ to_tsquery(‘default_ger’, ‘Suchbegriff1 | Suchbegriff2’) :wink:

Konnte unter 1. und 2. keinen Unterschied zur Abfragedauer feststellen.
Auf 3. war das Testselekt sogar langsamer als bei 1. und 2.


Ich hätte jetzt zumindest zwischen 1. und 2. einen deutlichen unterschied erwartet…

Kann mir da vielleicht jemand sagen wieso ich solche Ergebnisse gekriegt habe?
Ich dachte durch die Indizes wird der Zugriff beschleuningt, was eben nicht mit meinen Ergebnissen übereinstimmt :frowning:

Hoffe Ihr könnt mir vielleicht einen Tipp geben.

Gruß Manuel

Solange wir da kein EXPLAIN ANALYSE zu sehen bekommen können auch wir keinen Unterschied sehen …

Andreas

So, hier mal die SQLs mit EXPLAIN ANALYSE:

Mit HASH Index
“Seq Scan on memo (cost=0.00…32286.15 rows=18369 width=1236) (actual time=0.176…1878.748 rows=20561 loops=1)”
" Filter: ((memo_text ~~ ‘%Server%’::text) OR (memo_text ~~ ‘%Agentur%’::text))"
“Total runtime: 1882.176 ms”

Ohne Index
“Seq Scan on memo (cost=0.00…32286.15 rows=18369 width=1236) (actual time=0.175…1885.420 rows=20561 loops=1)”
" Filter: ((memo_text ~~ ‘%Server%’::text) OR (memo_text ~~ ‘%Agentur%’::text))"
“Total runtime: 1888.860 ms”



GIN Index
“Seq Scan on memo (cost=0.00…37322.14 rows=21666 width=980) (actual time=0.563…2149.345 rows=21202 loops=1)”
" Filter: (memo_fti_gin @@ ‘’‘serv’’ | ‘‘agentur’’‘::tsquery)"
“Total runtime: 2154.516 ms”


GIST Index
“Seq Scan on memo (cost=0.00…37322.14 rows=21666 width=980) (actual time=0.124…1879.307 rows=21202 loops=1)”
" Filter: (memo_fti_gist @@ ‘’‘serv’’ | ‘‘agentur’’'::tsquery)"
“Total runtime: 1884.147 ms”

Wie Sie sehen, sehen Sie nix :wink:

Zumindest nix Index-Scan.


Andreas

Und wieso nicht? Ich hab doch Indizes erstellt?

Nun, die puren Anwesenheit eines Indexes erzwingt nicht dessen Nutzung, und das ist auch gut so.

Wie groß ist Deine Tabelle (wie viele Zeilen), und auf wie viele trifft die Bedingung zu? Offenbar trifft sie ja auf (fast) alle Zeilen zu, oder?

Andreas

Unter folgendem Link steht die Erklärung wann der Index verwendet wird:
FAQ/de - PostgreSQL wiki

Mal sehen was da noch so steht… Mit Antworten wie “Wie Sie sehen, sehen Sie nix” komm ich nämlich leider nicht voran

Tatsächlich wurden alle Datensätze als Treffermenge ausgegeben.
Also soweit man beim Gin/Gist Index nicht die komplette Datenmenge als Ergebnis hat, verwendet er den Index auch. Das ist schonmal gut.

Der HASH-Index wird aber so gut wie nie benutzt.
In den FAQs aus dem PG Wiki, steht z.B. dass LIKE suchen generell nicht mit ‘%’ beginnen dürfen.

Beispiel:
SELECT * FROM memo
WHERE memo_text like ‘%server%’

“Seq Scan on memo (cost=0.00…31804.29 rows=19 width=1236) (actual time=1.954…1387.229 rows=4473 loops=1)”
" Filter: (memo_text ~~ ‘%server%’::text)"
“Total runtime: 1388.376 ms”

UND:
SELECT * FROM memo
WHERE memo_text like ‘server%’

“Seq Scan on memo (cost=0.00…31804.29 rows=19 width=1236) (actual time=1005.211…1005.211 rows=0 loops=1)”
" Filter: (memo_text ~~ ‘server%’::text)"
“Total runtime: 1005.261 ms”


Beide benutzt den Index also nicht… Er scheint Ihn also irgendwie nie zu benutzen. Was muss ich nun machen, damit er den Index verwendet?

Ihn richtig erstellen :wink:

test=*# \d words
   Table "public.words"
 Column | Type | Modifiers
--------+------+-----------
 w      | text |
Indexes:
    "idx_w" btree (w)

test=*# explain select * from words where w like 'foo%';
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on words  (cost=0.00..8460.29 rows=48 width=10)
   Filter: (w ~~ 'foo%'::text)
(2 rows)

test=*# create index idx_w2 on words(w text_pattern_ops);
CREATE INDEX
test=*# explain select * from words where w like 'foo%';
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Only Scan using idx_w2 on words  (cost=0.00..8.34 rows=48 width=10)
   Index Cond: ((w ~>=~ 'foo'::text) AND (w ~<~ 'fop'::text))
   Filter: (w ~~ 'foo%'::text)
(3 rows)

Andreas

Hallo.

Indexes:idx_w" btree (w) <-- Hab ich auch, einfacher BTree Index
create index idx_w2 on words(w text_pattern_ops); <-- Kann ich nicht erstellen: FEHLER: Indexzeile benötigt 9224 Bytes, Maximalgröße ist 8191

Vielleicht kannst du mir nochmal erklären wieso ich überhaupt 2 Indizes erstellen muss/soll ?

Gruß Mamuel

Öhm, nö, Du brauchst nicht 2 Indexe, das war nur als Demo gedacht, daß das text_pattern_ops hilfreich sein kann … näheres dazu siehe Doku.


Andreas

Ok, danke dir.

Gruß Manuel