Problem mit passender Abfrage-Syntax

Hallo,

ich habe gerade erst mit PostgreSQL begonnen, und nutze PG mit PostGIS um Geodaten zu archivieren. Momentan bereitet mir eine Suchanfrage Probleme:

Ich habe eine Datenbank mit mehreren Millionen Einträgen für Ortsnamen. Ich möchte bei Eingabe des Suchbegriffs “everest” folgende Ergebnisse bekommen:

everest = true
Everest = true
Mt. Everest 8848M = true
Everestii= false
Eeverest= false


Momentan nutze ich

SELECT * FROM tabelle WHERE spalte ILIKE ‘%everest%’

Das hat 2 Nachteile:

  1. Es wird auch Everestii Eeverest unerwünschterweise ausgegeben
  2. Die Anfrage dauert sehr lange, da ja nach einem Textstring im Text gesucht wird und kein Index benutzt wird. (Index für ILIKE ‘%string%’ überhaupt möglich ??)

Wie müsste also die korrekte Abfrage lauten und wie kann ich für diese einen funktionellen Index erstellen, damit die Suchgeschwindigkeit in Grenzen bleibt.

Vielen, vielen Dank !


Mel

Du suchst also nach dem Wort, es muß in seinen Grenzen so wie gesucht vorkommen.

test=*# select * from w;
     t
------------
 foo
 Foo
  Foo
  Foo
  Fooii
  Fooii bar
  Foo bar
  xxFoo bar
(8 rows)

Time: 0.765 ms
test=*# select * from w where lower(t) ~ '[[:<:]]foo[[:>:]]';
    t
----------
 foo
 Foo
  Foo
  Foo
  Foo bar
(5 rows)

PostgreSQL: Documentation: Manuals: PostgreSQL 8.2: Pattern Matching

  1. Die Anfrage dauert sehr lange, da ja nach einem Textstring im Text gesucht wird und kein Index benutzt wird. (Index für ILIKE ‘%string%’ überhaupt möglich ??)

Nein. Wenn das nötig ist, wirst Du Dir tsearch2 anschauen wollen, ist ein Extramodul.



Andreas

test=*# select * from w where lower(t) ~ ‘[[:<:]]foo[[:>:]]’;

Vielen Dank für die Hilfe !! Das war genau das was ich gesucht habe. Allerdings haben sich noch 2 weitere Fragen ergeben:

  1. Kann ich für die obere Abfrage einen funktionellen Index erstellen ?

  2. Ich hab versucht, die obere Abfrage in ein PHP-script einzubauen:

$searchstring = “(LOWER($szSearchfield) ~ ‘[[:<:]]$psearch[[:>:]]’)”;

Erhalte daraufhin aber folgenden Error:
Parse error: syntax error, unexpected ‘[’, expecting T_STRING or T_VARIABLE or T_NUM_STRING

Was hab ich falsch gemacht ?

Vielen Dank,
Mel

Du musst die [ und ] escapen, also mit [ und ] schreiben.
Bei einigen anderen Zeichen kann dies auch notwendig werden.

Du musst die [ und ] escapen, also mit [ und ] schreiben.
Bei einigen anderen Zeichen kann dies auch notwendig werden.

hm…wenn ich

$searchstring = “(LOWER($szSearchfield) ~ ‘[[:<:]]$psearch[[:>:]]’)”;

schreibe, kommt zwar keine Fehlermeldung mehr, aber auch keine Ergebnisse.
Hab auch einige andere Varianten probiert, es aber nicht hinbekommen.

Kann mir vielleicht jemand die komplette Zeile formulieren ?

Danke !!!

Achja, und kann man einen funktionalen Index für

select * from w where lower(t) ~ ‘[[:<:]]foo[[:>:]]’;

erstellen ?

Mel

Du kannst auf lower() einen Index machen:

test=# \d w
     Table "public.w"
 Column | Type | Modifiers
--------+------+-----------
 t      | text |

test=# create index idx_w on w(lower(t));
CREATE INDEX
Time: 36.848 ms
test=*# \d w
     Table "public.w"
 Column | Type | Modifiers
--------+------+-----------
 t      | text |
Indexes:
    "idx_w" btree (lower(t))

Für komplettes Fulltext-searching brauchst Du aber tsearch2.



Andreas

Du kannst auf lower() einen Index machen:

Das hatte ich schon gemacht. Während das bei einer normalen Suche enorm viel gebracht hat, scheint es bei

select * from w where lower(t) ~ ‘[[:<:]]foo[[:>:]]’;

recht wenig zu helfen.

Danke für den Tip mit tsearch2.

Mel

Kannst du Dir (und uns) mal ausgeben lassen, was hinterher in $searchstring steht?

Kannst du Dir (und uns) mal ausgeben lassen, was hinterher in $searchstring steht?

echo $searchstring;

ergibt:

(LOWER(name) ~ ‘[[:<:]] Everest [[:>:]]’)

Mel

Hmm, ist natürlich nicht das, was ich erwartet habe. Da sollten jetzt eigentlich nur [ und ] auftauchen, nicht die Backslashes noch davor.

Der Fehler, wo PHP früher ausgestiegen ist, muss an irgendetwas anderem liegen. Versuch doch mal den String so wie du ihn brauchst, einzeln Schritt für Schritt zusammenzusetzen und schau, wo der Fehler auftritt.

Hab’s geschafft !!! Wenngleich auch etwas umständlich:

$helpstringli = “[[:<:]]”;
$helpstringre = “[[:>:]]”;
$searchstring = “LOWER($szSearchfield) ~ ‘$helpstringli$psearch$helpstringre’”;

Nur dauert eine Abfrage nun 20 Sekunden statt 1 Sekunde, als ich noch

$searchstring = “(LOWER($szSearchfield) = (’$psearch’))”;

hatte. :frowning:

Also werde ich mich jetzt mal tsearch2 zuwenden, in der Hoffnung wieder an 1 Sekunde ranzukommen.

Nochmal vielen Dank an euch beide !!

Mel

Langsam …

Nun lässt du dir den fertigen Query mal ausgeben, setzt ein EXPLAIN davor und gibst das auf der Datenbankconsole ein. Die Ausgabe zeigst du uns hier (es gibt in der Menüleiste über dem Eingabefeld ein CODE Tag dafür).

Dann schauen wir weiter.

wenn Du nach Muster innerhalb einer Wortgrenze suchst, dürfte kein Index anschlagen, da Wortgrenze ja sowohl der Begin der Spalte sein kann als auch ein Space davor oder etwas ganz anderes.

Falls Mel damit leben kann, daß nur Wörter gefunden werden, die als erstes stehen, könnte man die erstere Wortgrenze gegen ^ tauschen und hätte dann wieder einen Index-Scan. Vermutlich…

Andreas

geonamesallppl4=# explain select * from name where lower(name) ~ '[[:<:]]everest[[:>:]]';
                          QUERY PLAN                          
--------------------------------------------------------------
 Seq Scan on name  (cost=0.00..67732.84 rows=46 width=74)
   Filter: (lower((name)::text) ~ '[[:<:]]everest[[:>:]]'::text)
(2 Zeilen)

Die Datenbank hat etwa 3 Millionen Einträge. Obige Suche dauert ca. 10-15 Sekunden.

Falls Mel damit leben kann, daß nur Wörter gefunden werden, die als erstes stehen

Das geht leider nicht, da z.B. bei Eingabe von “everest” das Textfeld “Mt. Everest 8848M” gefunden werden soll oder bei Eingabe von “amsterdam” das Textfeld “Amsterdam” aber auch “New-Amsterdam”.

Da ich ja nur nach ganzen Wörtern suchen will, muß ja theoretisch ein Index möglich sein. Wortgrenze wäre ein Space oder ein Bindestrich.

Mel

Ein Volltextindex, ja. Du solltest dich also mit tsearch2 beschäftigen.
Das könnte deine Probleme lösen.