Búsqueda "al estilo Google" con PostgreSQL (Full Text Search)

[deavid]

Me gustaría que le dierais un vistazo a esta funcionalidad (que está integrada a partir de Postgresql 8.3) , ya que pude ser muy muy interesante.

Os pongo en situación: muchos clientes piden poder buscar por todos los campos a la vez en una tabla, por lo que se les pone por defecto el campo de búsqueda en Eneboo a asterisco '*'.
El problema es que esto es funcional para tablas con pocos campos y pocos registros. Además, no busca por fechas, ni precios, ni otros números. Cuando busca por demasiados campos y registros, cada búsqueda puede demorarse 3 o 4 segundos (hablando de 30.000 registros x 20 campos)
Hemos hecho unas pruebas en el motor para permitir especificar en las MTD qué campos queríamos incluir en estas búsquedas y cuales no. Aunque la solución está bastante bien, porque permite limitar a los 5 o 6 campos que el cliente va a querer buscar normalmente, sigue siendo igual de lento cuando le pones que busque por 20 campos.

A raíz de este problema nos hemos puesto a investigar mecanismos para optimizar este tipo de consultas. El resultado es bastante bueno, aunque el montaje es un poco complejo. Se trata de implementar Full Text Search [1] para acelerar la búsqueda, ya que se parece mucho a lo que queremos hacer. Reduce el tiempo de búsqueda de 4 segundos a 0.05s (50ms)

Os pongo aquí el ejemplo que yo tengo hecho, que es con pedidos de cliente:

Primero que nada vamos a necesitar indexar los documentos de la tabla pedidoscli. En mi caso hice una tabla nueva, llamada "pedidoscli_fts". Lo que hacemos es concatenarle todos los campos como texto y convertirlos a ts_vector:

CREATE TABLE pedidoscli_fts AS
SELECT idpedido,
to_tsvector('spanish',coalesce(codigo,'')) ||
to_tsvector('spanish',coalesce(servido,'')) ||
to_tsvector('spanish',coalesce(replace(fecha::text,'-','/'),'')) ||
to_tsvector('spanish',coalesce(nombrecliente,'')) ||
to_tsvector('spanish',coalesce(cifnif,'')) ||
to_tsvector('spanish',coalesce(total::text,'')) ||
to_tsvector('spanish',coalesce(codcliente,'')) ||
to_tsvector('spanish',coalesce(observaciones,'')) ||
to_tsvector('spanish',coalesce(coddivisa,'')) ||
to_tsvector('spanish',coalesce(codserie,'')) ||
to_tsvector('spanish',coalesce(neto::text,'')) ||
to_tsvector('spanish',coalesce(totaleuros::text,'')) ||
to_tsvector('spanish',coalesce(codpago,'')) ||
to_tsvector('spanish',coalesce(codagente,'')) ||
to_tsvector('spanish',coalesce(codalmacen,'')) ||
to_tsvector('spanish',coalesce(direccion,'')) ||
to_tsvector('spanish',coalesce(codpostal,'')) ||
to_tsvector('spanish',coalesce(ciudad,'')) ||
to_tsvector('spanish',coalesce(provincia,'')) ||
to_tsvector('spanish',coalesce(apartado,'')) ||
to_tsvector('spanish',coalesce(codpais,'')) ||
to_tsvector('spanish',coalesce(codejercicio,''))
as concatenacion_tokens
FROM pedidoscli

Luego, tenemos que crear un índice en esta columna llamada "concatenacion_tokens":

CREATE INDEX pedidoscli_fts_idx
  ON pedidoscli_fts
  USING gin
  (concatenacion_tokens);

El tipo de índice puede ser GIN o GIST. Por lo que tengo entendido GIN tiene mejor velocidad de búsqueda, pero GIST es más rápido actualizando el índice. En mi caso elegí GIN, tal y como está en la SQL.

Con esto sería suficiente para empezar a buscar, pero el problema es que solo permite buscar por palabras completas, es decir, no permite preguntar por cosas similares, o con un LIKE. Para mejorar esto yo voy a hacer uso del módulo "pg_trgm" que sirve para indexar y buscar palabras por similitud. Por tanto, instalaremos la extensión:

CREATE EXTENSION pg_trgm;

Ahora lo que necesito es un diccionario de palabras conocidas, para ello uso la tabla anterior y creo a partir de ella un resumen en una tabla nueva pedidoscli_fts_words:

CREATE pedidoscli_fts_words AS
SELECT word from ts_stat('SELECT concatenacion_tokens FROM pedidoscli_fts');

Y crearemos un índice también sobre la columna words. Este índce será de trigramas (con el módulo pg_trgm). Esto acelerará los LIKE y permite también búsquedas por similitud.

CREATE INDEX pedidoscli_fts_words_idx
  ON pedidoscli_fts_words
  USING gin
  (word COLLATE pg_catalog."default" gin_trgm_ops);

Ahora con esto ya se pueden hacer consultas, pero como es un poco coñazo el construir la consulta a mano, me hice yo una función de postgresql que lo automatiza. Básicamente convierte la entrada a un array de palabras, localiza en el diccionario las palabras indexadas que se parecen y luego consulta pedidoscli_fts para obtener un idpedido y un ranking. El ranking es útil si quieres poner arriba los mejores resultados. De todos modos creo que no es nuestro caso.

Esta sería la función:

CREATE OR REPLACE FUNCTION search_fts_pedidoscli(IN filtros text, OUT idpedido integer, OUT rank real)
  RETURNS SETOF record AS
$BODY$
DECLARE
    qry tsquery;
    a_filtros text[];
    txtqry text;
    txtnew text;
    filtro text;
BEGIN
    -- DIVIDIR filtro en un array separado por espacios (palabras)
    a_filtros := ('{' || replace(lower(filtros), ' ', ',') || '}')::text[];
    txtqry := '';
    FOREACH filtro IN ARRAY a_filtros
    LOOP       
        txtnew :=  '(' || string_agg(word, ' | ') || ')' FROM pedidoscli_fts_words WHERE word LIKE filtro;
        IF txtnew IS NULL THEN
            filtro := replace(filtro,'%','');
            txtnew :=  '(' || string_agg(word, ' | ') || ')' FROM pedidoscli_fts_words WHERE word % filtro;
            IF txtnew IS NULL THEN
                RAISE WARNING 'filtro(SIMILAR %) -> 0 results -> NULL', filtro;
                txtnew := '(' || filtro || ')';
            ELSE
                RAISE WARNING 'filtro SIMILAR % -> %', filtro, txtnew ;

            END IF;
        END IF;
        IF txtqry != '' THEN
            txtqry := txtqry || '&';
        END IF;
        txtqry := txtqry || txtnew;
    END LOOP;
    RAISE WARNING 'Query (%)', txtqry;
    qry := to_tsquery(txtqry);
    RETURN QUERY SELECT fts.idpedido, ts_rank_cd(concatenacion_tokens, qry) AS rank
    FROM pedidoscli_fts fts
    WHERE concatenacion_tokens @@ qry;
    --RETURN QUERY SELECT 1 as idpedido, 0 as rank;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

Si os fijais un poco, al final lo que hace es un simple select, donde busca con el operador @@, que es el que hace el trabajo.

Y con esto, ya podemos hacer búsquedas como esta:

SELECT codigo, nombrecliente, direccion, ciudad, fecha
FROM pedidoscli WHERE idpedido IN (SELECT idpedido FROM search_fts_pedidoscli('valdemoro 2012/10%') )

Parte de la gracia es que puedo buscar simultáneamente en varias columnas: valdemoro es ciudad y 2012 es fecha. Separando con espacio las palabras de búsqueda puedo incluir el porcentaje (%) para expandir las palabras que quiera, no solamente la última. Si no ponemos porcentaje, entonces busca palabras enteras en lugar de buscar el campo entero como hacíamos con LIKE.

Por cierto, este ejemplo lo hice con un desarrollo propio. He quitado varias columnas que creo que eran propias, pero es posible me haya dejado algo. Si hay alguna columna de más, la borrais y ya está.

Obviamente, si cambia algo en pedidoscli, tenéis que rehacer la parte de los índices que toquen. Se puede hacer a mano, con triggers, etc. No me he metido aún en este tema.

En fin, si podéis, lo probáis. A ver que os parece.

(Nota: creo que es mejor poner una columna en pedidoscli, en vez de crear una tabla nueva pedidoscli_fts)

[1] : http://bosqueviejo.net/2010/12/16/fts-i-busqueda-de-texto-completo-en-postgresql/

[José Antonio Fernández Fernández]

yo  cada vez me siento más insignificante . XD Esta genial . +1!

--
Has recibido este mensaje porque estás suscrito al grupo "Eneboo" de Grupos de Google.
Para anular la suscripción a este grupo y dejar de recibir sus correos electrónicos, envía un correo electrónico a eneboo+un...@googlegroups.com.
Para obtener más opciones, visita https://groups.google.com/groups/opt_out.
 
 

--
Cuenta de correo propiedad de FERNÁNDEZ FERNÁNDEZ, JOSÉ ANTONIO y de uso estrictamente profesional. Este mensaje electrónico está dirigido únicamente a la(s) direcciones indicadas anteriormente: el carácter confidencial, personal e intransferible del mismo está protegido legalmente.
Cualquier revelación, uso o reenvío no autorizado, completo o en parte está prohibido. Si ha recibido este mensaje por equivocación notifíquelo inmediatamente a la persona que lo remite y borre el mensaje original junto con sus ficheros anexos sin leerlo ni grabarlo total o parcialmente. Si usted no desea recibir correos de nuestra empresa, por favor, envíenos un correo a AULLAS...@GMAIL.COM manifestando tal deseo

[кириллица или смерть]

Si José Antonio es insignificante los demás somos éter.

Gracias.

Aviso Legal: A información contida nesta mensaxe é confidencial. Esta información é enviada para a lectura exclusiva da(s) persona(s) á quen vai dirixida. O acceso por terceiros aos cales non vai dirixida esta mensaxe non está autorizado. Se vostede non é o destinatario da mensaxe, queda prohibida calqueira divulgación, duplicación ou distribución da información e pode ser ilegal. Calqueira accion tomada ou deixada de tomar basada na información contida na mensaxe queda prohibida e pode ser ilegal. Se a mensaxe vai dirixida a clientes, as opinións ou recomendacións contidas na mensaxe enténdense suxeitas aos termos e condicións do contrato de servicio.

Aviso Legal: La información que contiene este mensaje es confidencial. Ésta información es enviada para lectura exclusiva de la(s) persona(s) a la que va dirigida. El acceso por terceros a los cuales no va dirigido el mensaje no está autorizado. Si Ud. no es el destinatario del mensaje, queda prohibida cualquier divulgación, duplicación o distribución de la información y puede ser ilegal. Cualquier acción tomada o dejada de tomar basada en la información contenida en este mensaje queda prohibida y puede ser ilegal. Si el mensaje va dirigido a clientes, las opiniones o recomendaciones contenidas en el mensaje se entienden sujetas a los términos y condiciones de contrato de servicio.

[Juanguinho]

+1, en dos palabras impre sionante xD

[José Antonio Cuello]

Muy bueno. Un gran trabajo

El 30 de mayo de 2013 12:30, deavid <deavid...@gmail.com> escribió:

--

[Fusió d'Arts]

Simplemente genial, cada día nos sorprendes más.

Saludos.

[deavid]

Bueno, y ahora viene la gran pregunta:
¿Es lo suficientemente interesante como para integrarlo en el motor de Eneboo?

Hay que tener en cuenta que la integración no es para nada trivial y que es específica de PostgreSQL (lo cual quiere decir que los demás motores de base de datos no se beneficiarían de esto)
(MySQL también tiene esta característica, pero la implementación es completamente distinta y la desconozco)

Si lo integrásemos, habría que decidir sobre cómo almacenar ts_vector (como columna adicional, tabla aparte, etc), cómo mantenerlo al día (actualizarlo) y cómo crear y mantener la lista de palabras usadas.

Como punto extra: A la hora de indexar el documento, además de la cabecera también podemos considerar sus líneas. De este modo haría posible buscar documentos por descripción de una referencia en una línea y otras opciones raras. Básicamente es como buscar a través de una gestión documental, pero donde los documentos son elementos de la base de datos.

[Manuel Calomarde Gomez]

Je... ¿Qué pregunta? A mi entender, no hace falta preguntarlo :-)

[David Martínez Martí]

El lunes, 3 de junio de 2013 18:36:17 UTC+2, Manuel Calomarde Gomez escribió:

Je... ¿Qué pregunta? A mi entender, no hace falta preguntarlo :-)

He subido una variante a mi rama 2.4.2-dev que puede empezar a funcionar. Es una chapuza grande, que imagino que al final llegaremos a otra forma más limpia, pero de momento funciona para empezar a probar.

Para que funcione, debemos hacer por cada tabla que deseemos FTS lo sigiuente:

1) Crear la función de indexación de la tabla: (con los campos que deseemos indexar)

CREATE OR REPLACE FUNCTION pedidoscli_fts(r pedidoscli)
  RETURNS tsvector AS
$BODY$
DECLARE
    ret tsvector;
BEGIN
    ret :=
        to_tsvector('spanish',coalesce(substring(replace(r.neto::text,'-','/') for 250),'')) ||
        to_tsvector('spanish',coalesce(substring(replace(r.codpago::text,'-','/') for 250),'')) ||
        to_tsvector('spanish',coalesce(substring(replace(r.codalmacen::text,'-','/') for 250),'')) ||
        to_tsvector('spanish',coalesce(substring(replace(r.ciudad::text,'-','/') for 250),'')) ||
        to_tsvector('spanish',coalesce(substring(replace(r.apartado::text,'-','/') for 250),'')) ||
        to_tsvector('spanish',coalesce(substring(replace(r.provincia::text,'-','/') for 250),'')) ||
        to_tsvector('spanish',coalesce(substring(replace(r.fecha::text,'-','/') for 250),'')) ||
        to_tsvector('spanish',coalesce(substring(replace(r.codcliente::text,'-','/') for 250),'')) ||
        to_tsvector('spanish',coalesce(substring(replace(r.direccion::text,'-','/') for 250),'')) ||
        to_tsvector('spanish',coalesce(substring(replace(r.fechasalida::text,'-','/') for 250),'')) ||
        to_tsvector('spanish',coalesce(substring(replace(r.observaciones::text,'-','/') for 250),'')) ||
        to_tsvector('spanish',coalesce(substring(replace(r.cifnif::text,'-','/') for 250),'')) ||
        to_tsvector('spanish',coalesce(substring(replace(r.nombrecliente::text,'-','/') for 250),'')) ||
        to_tsvector('spanish',coalesce(substring(replace(r.codagente::text,'-','/') for 250),'')) ||
        to_tsvector('spanish',coalesce(substring(replace(r.codigo::text,'-','/') for 250),'')) ||
        to_tsvector('spanish',coalesce(substring(replace(r.codejercicio::text,'-','/') for 250),''));

    RETURN ret;
END
   $BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;
   
2) Crear el índice GIN. (he probado GIST y para estos casos no acelera lo suficiente las búsquedas) (tarda 6 minutos en indexar 1 millón de registros)

DROP INDEX IF EXISTS pedidoscli_fts_idx;

CREATE INDEX pedidoscli_fts_idx
  ON pedidoscli
  USING gin
  (pedidoscli_fts(pedidoscli.*));

3) Modificar la MTD y agregar la opción "FTSFunction" y le ponemos el nombre de función creada:

<!DOCTYPE TMD>
<TMD>
    <name>pedidoscli</name>
<!--Pedido de cliente-->    <alias>QT_TRANSLATE_NOOP("MetaData","Pedidos de Clientes")</alias>
    <FTSFunction>pedidoscli_fts</FTSFunction>
    <field>
        <name>idpedido</name>
<!--Identificador interno de la tabla-->        <alias>QT_TRANSLATE_NOOP("MetaData","Identificador")</alias>
(...)

4) Entrar en el nuevo eneboo (compilado desde mi rama 2.4.2-dev), y intentar buscar por el campo asterisco.
En la consola veremos una salida de depuración como esta:
Using Full Text Search: pedidoscli_fts(pedidoscli) @@ to_tsquery('marta:*')
Using Full Text Search: pedidoscli_fts(pedidoscli) @@ to_tsquery('marta & 2011/04:*')

En esta variante no uso ningún tipo de tabla ni campo adicional para almacenar esta información. La pega es que por cada registro devuelto, se tiene que comparar con el resultado de la función, por lo que internamente ejecuta la función por cada registro que ha cumplido a priori.

Manualmente, he probado a realizar consultas contra tablas de lineasalbaranescli con 1.5 millones de registros y 27 campos indexados, con tiempos del orden de los 80ms en mi ordenador de trabajo.

Hay que tener en cuenta que las búsquedas por FTS no funcionan igual que las normales. En la casilla de búsqueda pondremos "marta 2011" y Eneboo debe traducir eso a "marta & 2011:*" que sería lo mismo que preguntar por registros con la palabra "Marta" en algún campo y que además (en cualquier otro campo) exista una palabra que empiece por "2011". No podemos buscar por '%art%' con este sistema; pues haría falta una tabla de "palabras" que ahora mismo no existe.

[David Martínez Martí]

Os he subido el compilado que yo estaba usando, por si tenéis pereza de compilar:

http://www.eneboo.org/pub/contrib/eneboo-build-linux32-dba-fulltextsearch-1.tar.gz

[José Antonio Fernández Fernández]

Genial trabajo. Los puntos 1 y 2 se realizan todavia ajenos a eneboo .no?

El 04/06/2013 13:47, "David Martínez Martí" <deavid...@gmail.com> escribió:

Os he subido el compilado que yo estaba usando, por si tenéis pereza de compilar:

http://www.eneboo.org/pub/contrib/eneboo-build-linux32-dba-fulltextsearch-1.tar.gz

--

[deavid]

Sí, por supuesto. Hay que modificar la base de datos a mano :-(

[Chencho]

Una duda... antes de meterme en berengenales con este tipo de búsqueda... ¿como seteáis el "campo" * por defecto en las búsquedas?

[deavid]

Está en pruebas.

La última que hice es usando este commit de eneboo se agrega el código:
https://github.com/gestiweb/eneboo/commit/5f682156c3df3c00a4eae1737113af8fa359df44

Para que te funcione, lo que hacemos es en el QS, agregar una funcion:
flfacturac.tableDB_filterRecords_pedidoscli(

nombreTablaCursor

textoEnCajaBusqueda

fieldNamedeBusqueda // (o asterisco *)

filtroGeneradoEnElMotor ) Y la función debe devolver, o "null" en caso de no querer modificar la búsqueda, o una string con el nuevo filtro para reemplazar filtroGeneradoEnElMotor.

De esta forma puedes tanto modificar la búsqueda de cualquier campo, así como tener eventos para leer cuando el usuario está cambiando el filtrado. Me pareció más portable.

[deavid]

Otra opción es usar lo que hice en el commit anterior a este, que es lo de las opciones de búsqueda, el "FTSFunction". Pero creo que con la existencia de la funcion que te comentaba, lo de las opciones de MTD lo quitaremos.

[Chencho]

Ok, veo que es a nivel de ejecutable.

Si ya me cuesta el tema del código "normal" de eneboo, como para ponerme con el ejecutable!

Muchas gracias, de todas formas igual le puedo dedicar algo de tiempo para ir jugando.

[Miguel J]

Hola,

 

...esto del buscador estilo google funciona en algún lugar? es una extensión?

 

...existe para eneboo con mysql sobre Windows?

 

Gracias

[Luismi PR]

+1 a esta petición. sería genial

Saludos.

[Antonio Rodríguez]

Eres un crack Deavid! Tenerlo en el motor sería perfecto



Nosotros estamos pensando en una búsqueda google "total". Algo así como un buscador global que busque en ciertas tablas por ciertos campos de una sola vez

Por ejemplo, en clientes y proveedores por nombre y en artículos por referencia y descripción.

Los resultados se mostrarían en una tabla manual, no un fltabledb, ya que habría registros de distintas tablas

¿Es posible esto con esta nueva búsqueda? ¿O habría que hacer una query por tabla?

[Miguel J]

.yo sigo necesitando un buscador de clientes por direcciones para mi instalación en local (la tengo en la copia del servidor y por heidi)...

--
Has recibido este mensaje porque estás suscrito a un tema del grupo "Eneboo" de Grupos de Google.
Para anular la suscripción a este tema, visita https://groups.google.com/d/topic/eneboo/qVOsklTcqq4/unsubscribe.
Para anular la suscripción a este grupo y a todos sus temas, envía un correo electrónico a eneboo+un...@googlegroups.com.
Para obtener más opciones, visita https://groups.google.com/d/optout.

[deavid]

Es posible. Repasa el procedimiento que os he puesto al principio; yo incluso te recomendaría que lo probases "tal cual" está explicado para empezar a tomar contacto con las búsquedas FTS.

Si te fijas al final tengo una búsqueda que hace un "SELECT * FROM tabla WHERE id IN (SELECT id FROM tabla_fts WHERE campo @@ busqueda)", lo cual en realidad quiere decir que tengo una tabla externa, y que para mostrarlo en el FLTableDB tengo que cruzarla para sacar qué registros.

Al final,esa tabla puede tener lo que quieras y el tema sería reconocer también (agregar una propiedad) qué tipo de registro estamos obteniendo. No sé si además algo de información relacionada para contextualizar la búsqueda.

Las búsquedas FTS son muy potentes y muy muy rápidas, lo que su tecnología no se parece a nada que yo haya visto en otros sitios. Así que toca leer mucha documentación y practicar mucho para entender cómo funciona.

Diferentes variantes de esto se podrían proponer como extensiones, o se podría integrar en el motor; quién sabe. Pero lo importante es que sepáis cómo funciona y que intentemos consensuar un uso o una forma de hacerlo en común, para que al final cada uno no tenga su implementación propia incompatible entre sí.

Ah, y por supuesto, las búsquedas FTS dependen completamente del motor de base de datos que uses... o de un motor externo si fuese el caso. Eso quiere decir que lo que programes para PostgreSQL dificilmente funcionará en MySQL.

 

[Miguel J]

...pues si se programa distinto entre mysql y postgre tendría q hacerse como extension, no en el motor....digo yo...

--

Has recibido este mensaje porque estás suscrito a un tema del grupo "Eneboo" de Grupos de Google.
Para anular la suscripción a este tema, visita https://groups.google.com/d/topic/eneboo/qVOsklTcqq4/unsubscribe.
Para anular la suscripción a este grupo y a todos sus temas, envía un correo electrónico a eneboo+un...@googlegroups.com.

Para acceder a más opciones, visita https://groups.google.com/d/optout.