Re: Como trabaja Postgre con Transacciones

Hola lista, soy nuevo en Posgre, siempre he usado MS-SQL, pero tengo la
oportunidad de crear un nuevo proyecto de desarrollo de aplicación, voy a
usar como fron-desk Visual Fox Pro, los usuarios aqui solo usan Windows, y
ademas es el lenguaje que conozco, tengo varias preguntas:

1.- Se que para bloquear un registro solo comienzo mi actualización de datos
con BEGIN, y postgre toma la decición (creo),
¿Cómo se que se encuentra bloqueado un registro?
¿Cómo saber si se bloqueo la tabla compleata o solo algunos registros?
¿Puedo controlar manualmente cual registro / tabla quiero bloquear ?

Por favor le pido un pequeño ejemplo de esto, y no me manden a la
documentación que ya me la he leido.

Saludos
Jaime S. Gattorno
Gte. Sistemas Corporación Zaffiro

Jaime Sierra Gattorno escribió:
> Hola lista, soy nuevo en Posgre, siempre he usado MS-SQL, pero tengo la
> oportunidad de crear un nuevo proyecto de desarrollo de aplicación, voy a
> usar como fron-desk Visual Fox Pro, los usuarios aqui solo usan Windows, y
> ademas es el lenguaje que conozco, tengo varias preguntas:
>
> 1.- Se que para bloquear un registro solo comienzo mi actualización de datos
> con BEGIN, y postgre toma la decición (creo),

Pequeña aclaracion, no se llama Postgre sino Postgres.

> ¿Cómo se que se encuentra bloqueado un registro?
> ¿Cómo saber si se bloqueo la tabla compleata o solo algunos registros?
> ¿Puedo controlar manualmente cual registro / tabla quiero bloquear ?

Postgres no bloquea registros, sino que usa MVCC. Puedes bloquear
registros explicitamente, usando SELECT FOR UPDATE (lock exclusivo) o
SELECT FOR SHARE (lock compartido), en cuyo caso otros SELECT FOR
UPDATE/SHARE quedaran bloqueados si intentan afectar el mismo registro;
tambien bloquean UPDATE y DELETE, pero no bloquean SELECT ni INSERT.

(Obviamente dos SELECT FOR SHARE no se bloquean mutuamente)

Tambien puedes bloquear tablas explicitamente usando la sentencia LOCK.
Tambien se hace implicitamente en algunas ordenes como VACUUM, ALTER
TABLE, etc. Hay varios niveles de bloqueo, y pueden haber varios
bloqueadores simultaneamente. Por ej. si haces ALTER TABLE o VACUUM
FULL entonces no puedes hacer casi ninguna otra cosa, pero si haces
VACUUM o ANALYZE entonces otros pueden acceder a la tabla sin problemas.

Con respecto a los candados a nivel de registro, lo que se hace al
modificar (UPDATE) un registro es escribir una version nueva, y dejar la
antigua sin tocar. Cuando otra sesion quiere acceder (leer) al
registro, simplemente examina la version antigua y deja la nueva sin
tocar. De esta manera el escritor no bloquea al lector.

La unica situacion en que se bloquean, ocurre cuando dos sesiones
quieren modificar el mismo registro. Esto es lo que sucede:

Si una sesion esta modificando un registro y otra sesion quiere
modificarlo simultaneamente, la segunda sesion se bloquea y lo que
sucede despues depende del nivel de aislamiento de la segunda
transaccion y de lo que haga la primera. Hay varios casos. Si la
segunda transaccion es TRANSACTION ISOLATION LEVEL SERIALIZABLE,
entonces espera a que termine la primera. Si la primera compromete
(COMMIT) entonces la segunda sesion se aborta con un error de
serializacion. Si la primera aborta (ROLLBACK) entonces la segunda
puede continuar.

Si la segunda transaccion es TRANSACTION ISOLATION LEVEL READ COMMITTED
entonces la segunda espera que la primera termine, y si la primera
aborta entonces modifica la version original, de lo contrario busca la
version nueva y la modifica.

Finalmente, hay que aclarar que todos los bloqueos duran desde el
instante en que se adquieren hasta que termina la transaccion en curso.
No hay manera de liberar un lock sin cerrar la transaccion.

--
Alvaro Herrera http://www.PlanetPostgreSQL.org/
"La realidad se compone de muchos sueños, todos ellos diferentes,
pero en cierto aspecto, parecidos..." (Yo, hablando de sueños eróticos)

Alvaro, si no es molestia, me podrias facilitar la vida dandome un ejemplo
de como lo haces tu.

Gracias.

El día 6/11/07, Alvaro Herrera <alvherre(at)commandprompt(dot)com> escribió:
>
> Jaime Sierra Gattorno escribió:
> > Hola lista, soy nuevo en Posgre, siempre he usado MS-SQL, pero tengo la
> > oportunidad de crear un nuevo proyecto de desarrollo de aplicación, voy
> a
> > usar como fron-desk Visual Fox Pro, los usuarios aqui solo usan Windows,
> y
> > ademas es el lenguaje que conozco, tengo varias preguntas:
> >
> > 1.- Se que para bloquear un registro solo comienzo mi actualización de
> datos
> > con BEGIN, y postgre toma la decición (creo),
>
> Pequeña aclaracion, no se llama Postgre sino Postgres.
>
> > ¿Cómo se que se encuentra bloqueado un registro?
> > ¿Cómo saber si se bloqueo la tabla compleata o solo algunos registros?
> > ¿Puedo controlar manualmente cual registro / tabla quiero bloquear ?
>
> Postgres no bloquea registros, sino que usa MVCC. Puedes bloquear
> registros explicitamente, usando SELECT FOR UPDATE (lock exclusivo) o
> SELECT FOR SHARE (lock compartido), en cuyo caso otros SELECT FOR
> UPDATE/SHARE quedaran bloqueados si intentan afectar el mismo registro;
> tambien bloquean UPDATE y DELETE, pero no bloquean SELECT ni INSERT.
>
> (Obviamente dos SELECT FOR SHARE no se bloquean mutuamente)
>
> Tambien puedes bloquear tablas explicitamente usando la sentencia LOCK.
> Tambien se hace implicitamente en algunas ordenes como VACUUM, ALTER
> TABLE, etc. Hay varios niveles de bloqueo, y pueden haber varios
> bloqueadores simultaneamente. Por ej. si haces ALTER TABLE o VACUUM
> FULL entonces no puedes hacer casi ninguna otra cosa, pero si haces
> VACUUM o ANALYZE entonces otros pueden acceder a la tabla sin problemas.
>
> Con respecto a los candados a nivel de registro, lo que se hace al
> modificar (UPDATE) un registro es escribir una version nueva, y dejar la
> antigua sin tocar. Cuando otra sesion quiere acceder (leer) al
> registro, simplemente examina la version antigua y deja la nueva sin
> tocar. De esta manera el escritor no bloquea al lector.
>
> La unica situacion en que se bloquean, ocurre cuando dos sesiones
> quieren modificar el mismo registro. Esto es lo que sucede:
>
> Si una sesion esta modificando un registro y otra sesion quiere
> modificarlo simultaneamente, la segunda sesion se bloquea y lo que
> sucede despues depende del nivel de aislamiento de la segunda
> transaccion y de lo que haga la primera. Hay varios casos. Si la
> segunda transaccion es TRANSACTION ISOLATION LEVEL SERIALIZABLE,
> entonces espera a que termine la primera. Si la primera compromete
> (COMMIT) entonces la segunda sesion se aborta con un error de
> serializacion. Si la primera aborta (ROLLBACK) entonces la segunda
> puede continuar.
>
> Si la segunda transaccion es TRANSACTION ISOLATION LEVEL READ COMMITTED
> entonces la segunda espera que la primera termine, y si la primera
> aborta entonces modifica la version original, de lo contrario busca la
> version nueva y la modifica.
>
>
> Finalmente, hay que aclarar que todos los bloqueos duran desde el
> instante en que se adquieren hasta que termina la transaccion en curso.
> No hay manera de liberar un lock sin cerrar la transaccion.
>
> --
> Alvaro Herrera
> http://www.PlanetPostgreSQL.org/
> "La realidad se compone de muchos sueños, todos ellos diferentes,
> pero en cierto aspecto, parecidos..." (Yo, hablando de sueños eróticos)
>

Jaime Sierra Gattorno escribió:
> Alvaro, si no es molestia, me podrias facilitar la vida dandome un ejemplo
> de como lo haces tu.

No, porque yo no lo hago. ¿Que es lo que quieres hacer?

--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"Siempre hay que alimentar a los dioses, aunque la tierra esté seca" (Orual)

Mira, entiendo esto

BEGIN TRANSACITION
SELECT * FROM Inventario WHERE Codigo = 'xyz'

UPDATE Inventario
SET Salidas = xValor, Existencia_Actual = xValor

COMMIT

¿Pero como se si el bloqueo fue realizado durante la transaccion?
¿Como saber si el registro se encuentra bloqueado antes de la transaccion?

El día 8/11/07, Alvaro Herrera <alvherre(at)commandprompt(dot)com> escribió:
>
> Jaime Sierra Gattorno escribió:
> > Alvaro, si no es molestia, me podrias facilitar la vida dandome un
> ejemplo
> > de como lo haces tu.
>
> No, porque yo no lo hago. ¿Que es lo que quieres hacer?
>
> --
> Alvaro Herrera
> http://www.flickr.com/photos/alvherre/
> "Siempre hay que alimentar a los dioses, aunque la tierra estÃ(c) seca"
> (Orual)
>

Jaime Sierra Gattorno escribió:
> Mira, entiendo esto
>
> BEGIN TRANSACITION
> SELECT * FROM Inventario WHERE Codigo = 'xyz'
>
> UPDATE Inventario
> SET Salidas = xValor, Existencia_Actual = xValor
>
> COMMIT
>
> ¿Pero como se si el bloqueo fue realizado durante la transaccion?
> ¿Como saber si el registro se encuentra bloqueado antes de la transaccion?

No entiendo para que quieres _saber_ si el bloqueo fue realizado. ¿De
que te sirve saberlo?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

yo supongo metiendome a lo que leeo es que el quiere ver si se realizo o no
ya que dos seciones uno lo hace y el otro no o si se espera el segundo hasta
acer la actualizacion

On Nov 8, 2007 1:38 PM, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:

> Jaime Sierra Gattorno escribió:
> > Mira, entiendo esto
> >
> > BEGIN TRANSACITION
> > SELECT * FROM Inventario WHERE Codigo = 'xyz'
> >
> > UPDATE Inventario
> > SET Salidas = xValor, Existencia_Actual = xValor
> >
> > COMMIT
> >
> > ¿Pero como se si el bloqueo fue realizado durante la transaccion?
> > ¿Como saber si el registro se encuentra bloqueado antes de la
> transaccion?
>
> No entiendo para que quieres _saber_ si el bloqueo fue realizado. ¿De
> que te sirve saberlo?
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/ <http://www.commandprompt.com/>
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> --
> TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net
>

--
atte

juan antonio jaimes valle
toluca, mexico
juanjava(at)gmail(dot)com
juanjava(at)yahoo(dot)com

Hola a todos,

Tengo una BBDD en PostgreSQL que va a contener varios millones de
registros, por lo que necesito optimizar las consultas lo máximo posible...

Mi problema es que, a la hora de crear los indices, no puedo crear un
indice (tipo BTREE) en una de las columnas, porque algunos de los
registros que hay en ella tienen una longitud mayor a la que permite el
indice (creo recordar que 2000 y pico... no demasiado...)

He buscado documentación acerca de los tipos de indices existentes, pero
no me queda nada claro sus diferencias y caracteristicas. Hasta ahora
siempre había usado indices BTREE, pero nunca me habia parado a pensar
en las diferencias que tendrian estos indices con RTREE o HASH, y ahora
que me veo obligado a usar otro tipo distinto a BTREE, no se si al crear
el indice HASH (por ejemplo) las consultas serán mas lentas, o habrá
alguna penalización....

Podrías indicarme las caracteristicas y/o diferencias entre estos tipos
de índices de PostgreSQL, o decirme donde encontrar información CLARA al
respecto? (ya leí en la documentacion oficial, y no consigo sacar
conclusiones)

Muchas gracias por adelantado,

Saludos,

BhEaN escribió:
> Hola a todos,
>
> Tengo una BBDD en PostgreSQL que va a contener varios millones de
> registros, por lo que necesito optimizar las consultas lo máximo
> posible...
>
> Mi problema es que, a la hora de crear los indices, no puedo crear un
> indice (tipo BTREE) en una de las columnas, porque algunos de los
> registros que hay en ella tienen una longitud mayor a la que permite el
> indice (creo recordar que 2000 y pico... no demasiado...)
>
> He buscado documentación acerca de los tipos de indices existentes, pero
> no me queda nada claro sus diferencias y caracteristicas. Hasta ahora
> siempre había usado indices BTREE, pero nunca me habia parado a pensar
> en las diferencias que tendrian estos indices con RTREE o HASH, y ahora
> que me veo obligado a usar otro tipo distinto a BTREE, no se si al crear
> el indice HASH (por ejemplo) las consultas serán mas lentas, o habrá
> alguna penalización....
>
> Podrías indicarme las caracteristicas y/o diferencias entre estos tipos
> de índices de PostgreSQL, o decirme donde encontrar información CLARA al
> respecto? (ya leí en la documentacion oficial, y no consigo sacar
> conclusiones)

rtree ya no existe. Fue reemplazado por GiST ("generalized search
tree"), el cual es un sistema de indexamiento para tipos complicados --
por ej. se usa para indexar tipos geométricos y también para los índices
de búsqueda en texto, entre muchas otras cosas.

El otro sistema de indexamiento extravagante es GIN; es un índice
invertido. También se usa para búsqueda en texto (es más rápido en
búsquedas que GiST, pero mucho más lento para agregar nuevos valores).
Se puede usar para otras cosas también obviamente.

Hash es un tipo de índice que en Postgres no es recomendable por el
momento, así que no lo uses porque tiene varios problemas.

Así que tu única alternativa es el btree. Dado que no puedes almacenar
más de 2000 y pico caracteres, vas a tener que buscar mecanismos
alternativos. Si quieres indexar campos grandes de texto, ¿quizás lo que
necesitas en realidad es usar el sistema de búsqueda en texto? Te
podemos dar consejos más específicos si nos dices exactamente de qué
naturaleza son los datos y cómo serán las consultas.

--
Alvaro Herrera http://www.advogato.org/person/alvherre
"Sallah, I said NO camels! That's FIVE camels; can't you count?"
(Indiana Jones)

El día 18 de mayo de 2009 13:09, Alvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> escribió:
> BhEaN escribió:
>> Hola a todos,
>>
>> Mi problema es que, a la hora de crear los indices, no puedo crear un
>> indice (tipo BTREE) en una de las columnas, porque algunos de los
>> registros que hay en ella tienen una longitud mayor a la que permite el
>> indice (creo recordar que 2000 y pico... no demasiado...)
>>
>
> Así que tu única alternativa es el btree.  Dado que no puedes almacenar
> más de 2000 y pico caracteres, vas a tener que buscar mecanismos
> alternativos.  Si quieres indexar campos grandes de texto, ¿quizás lo que
> necesitas en realidad es usar el sistema de búsqueda en texto?  Te
> podemos dar consejos más específicos si nos dices exactamente de qué
> naturaleza son los datos y cómo serán las consultas.
>

En ese caso no le conviene crear indices particionados?
i.e:
parapruebas=# create index ix_datos on datos (texto) where texto ~ 'a%';
CREATE INDEX
(es un ejemplo burdo, pero creo que se entiende :)

Separar los indices en un tablespace alamcenado en un lugar
de más rápido acceso?

Son solo propuestas :)

--
Emanuel Calvo Franco
Sumate al ARPUG !
( www.arpug.com.ar)
ArPUG / AOSUG Member

Emanuel Calvo Franco escribió:

> En ese caso no le conviene crear indices particionados?
> i.e:
> parapruebas=# create index ix_datos on datos (texto) where texto ~ 'a%';
> CREATE INDEX
> (es un ejemplo burdo, pero creo que se entiende :)

No soluciona el problema, porque el problema es que el campo es muy
largo. Lo que podría hacer es lo siguiente

create index ix_substr_datos on datos (substring(1, 2000, texto));
-- o como sea el orden de argumentos de substring

y obviamente modificar las consultas para agregar un substring en el
where también (además de la cláusula original).

> Separar los indices en un tablespace alamcenado en un lugar
> de más rápido acceso?

Yo dudo mucho de la robustez de esta idea, porque si hay una caída
tienes que corregir los catálogos y hacer un reindex.

--
Alvaro Herrera http://planet.postgresql.org/
"La realidad se compone de muchos sueños, todos ellos diferentes,
pero en cierto aspecto, parecidos..." (Yo, hablando de sueños eróticos)

El día 18 de mayo de 2009 14:15, Alvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> escribió:
> Emanuel Calvo Franco escribió:
>
>> En ese caso no le conviene crear indices particionados?
>> i.e:
>> parapruebas=# create index ix_datos on datos (texto) where texto ~ 'a%';
>> CREATE INDEX
>> (es un ejemplo burdo, pero creo que se entiende :)
>
> No soluciona el problema, porque el problema es que el campo es muy
> largo.  Lo que podría hacer es lo siguiente
>

Lei mal (lei cualquiera), habia entendido que el problema era la
cantida de filas, no el tamaño del campo. tenes razón...

> create index ix_substr_datos on datos (substring(1, 2000, texto));
> -- o como sea el orden de argumentos de substring
>
> y obviamente modificar las consultas para agregar un substring en el
> where también (además de la cláusula original).
>

Creo que para este caso sería conveniente que utilizará full text search, creo
que ya lo habias dicho.

>> Separar los indices en un tablespace alamcenado en un lugar
>> de más rápido acceso?
>
> Yo dudo mucho de la robustez de esta idea, porque si hay una caída
> tienes que corregir los catálogos y hacer un reindex.
>

No entiendo cual sería la inconsistencia, no ocurriría lo mismo si
tiene los índices en el lugar por defecto ?... (obviando el particionamiento)

--
Emanuel Calvo Franco
Sumate al ARPUG !
( www.arpug.com.ar)
ArPUG / AOSUG Member

Emanuel Calvo Franco escribió:
> El día 18 de mayo de 2009 14:15, Alvaro Herrera
> <alvherre(at)alvh(dot)no-ip(dot)org> escribió:

> >> Separar los indices en un tablespace alamcenado en un lugar
> >> de más rápido acceso?
> >
> > Yo dudo mucho de la robustez de esta idea, porque si hay una caída
> > tienes que corregir los catálogos y hacer un reindex.
>
> No entiendo cual sería la inconsistencia, no ocurriría lo mismo si
> tiene los índices en el lugar por defecto ?... (obviando el particionamiento)

No, porque tú estás pensando en tablespaces en RAM y cosas por el
estilo, no? Al menos eso es lo que dice tu artículo en la Wiki.

--
Alvaro Herrera http://planet.postgresql.org/
"Hackers share the surgeon's secret pleasure in poking about in gross innards,
the teenager's secret pleasure in popping zits." (Paul Graham)

El día 18 de mayo de 2009 14:43, Alvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> escribió:
> Emanuel Calvo Franco escribió:
>> El día 18 de mayo de 2009 14:15, Alvaro Herrera
>> <alvherre(at)alvh(dot)no-ip(dot)org> escribió:
>
>> >> Separar los indices en un tablespace alamcenado en un lugar
>> >> de más rápido acceso?
>> >
>> > Yo dudo mucho de la robustez de esta idea, porque si hay una caída
>> > tienes que corregir los catálogos y hacer un reindex.
>>
>> No entiendo cual sería la inconsistencia, no ocurriría lo mismo si
>> tiene los índices en el lugar por defecto ?... (obviando el particionamiento)
>
> No, porque tú estás pensando en tablespaces en RAM y cosas por el
> estilo, no?  Al menos eso es lo que dice tu artículo en la Wiki.
>

:P eso fue un simple articulo, no recomendaria ni mamado eso en producción.
Pero eso significa que leiste el artículo :)

--
Emanuel Calvo Franco
Sumate al ARPUG !
( www.arpug.com.ar)
ArPUG / AOSUG Member

El día 18 de mayo de 2009 11:36, BhEaN <listas(at)bhean(dot)com> escribió:
> Hola a todos,
>
> Tengo una BBDD en PostgreSQL que va a contener varios millones de registros,
> por lo que necesito optimizar las consultas lo máximo posible...
>
> Mi problema es que, a la hora de crear los indices, no puedo crear un indice
> (tipo BTREE) en una de las columnas, porque algunos de los registros que hay
> en ella tienen una longitud mayor a la que permite el indice (creo recordar
> que 2000 y pico... no demasiado...)

En el 100% los casos, un índice de 2000 caracteres es un índice inútil
(para Postgres y para cualquier DBMS)
Dependiendo de la solución que quieras implementar, como en otros
mails se dice, hay que usar índice parcial o una solución "Full text
search". También, si la búsqueda es exacta, podés implementar un
índice HASH programado a manopla, que no es muy difícil.

Saludos!
Silvio

El día 18 de mayo de 2009 14:53, Silvio Quadri <silvioq(at)gmail(dot)com> escribió:
> El día 18 de mayo de 2009 11:36, BhEaN <listas(at)bhean(dot)com> escribió:
>> Hola a todos,
>>
>
> En el 100% los casos, un índice de 2000 caracteres es un índice inútil
> (para Postgres y para cualquier DBMS)
> Dependiendo de la solución que quieras implementar, como en otros
> mails se dice, hay que usar índice parcial o una solución "Full text
> search". También, si la búsqueda es exacta, podés implementar un
> índice HASH programado a manopla, que no es muy difícil.

La solución de un hash manual no es tan dificil de implementar (hay un
articulo que dice como emularlos en la wiki). Sin embargo IMHO no se
si sería una solución viable, ya que haría bastante carga para hashear 2000
posiciones ( a menos que haga un substring para hashear menos, pero
requeriría un hash de 64 bits o superior)...

--
Emanuel Calvo Franco
Sumate al ARPUG !
( www.arpug.com.ar)
ArPUG / AOSUG Member

Antes de nada, muchísimas gracias a todos por vuestras respuestas....

Os contesto debajo de cada parrafo:
> rtree ya no existe. Fue reemplazado por GiST ("generalized search
> tree"), el cual es un sistema de indexamiento para tipos complicados --
> por ej. se usa para indexar tipos geométricos y también para los índices
> de búsqueda en texto, entre muchas otras cosas.
>
Eso es lo que necesito, un índice de búsqueda en texto...
> El otro sistema de indexamiento extravagante es GIN; es un índice
> invertido. También se usa para búsqueda en texto (es más rápido en
> búsquedas que GiST, pero mucho más lento para agregar nuevos valores).
> Se puede usar para otras cosas también obviamente.
>
Eso suena mejor que GiST, puesto que la relación de inserciones/lecturas
será muy baja... se haran muchísimas más consultas que inserciones, por
lo que quizás me interese más éste tipo de indice...
> Hash es un tipo de índice que en Postgres no es recomendable por el
> momento, así que no lo uses porque tiene varios problemas.
>
> Así que tu única alternativa es el btree. Dado que no puedes almacenar
> más de 2000 y pico caracteres, vas a tener que buscar mecanismos
> alternativos. Si quieres indexar campos grandes de texto, ¿quizás lo que
> necesitas en realidad es usar el sistema de búsqueda en texto? Te
> podemos dar consejos más específicos si nos dices exactamente de qué
> naturaleza son los datos y cómo serán las consultas.
>
>
Ok... siento no haber sido más explícito... lo que tengo exactamente es
una tabla con muchos anuncios clasificados (los típicos anuncios de
"vendo blablabla", o "compro blablablablabla"... hay varios millones de
éstos anuncios.... y lo que necesito es optimizar todo lo posible las
búsquedas en ella, ya que debo permitir búsquedas de palabras en el
texto y título de dichos anuncios... es decir, búsquedas del tipo LIKE
'%blablabla%' (lo cual tiene pinta de que va a ser horrible para la
BBDD, pero es lo que hay, jejejee...). No dispongo aún de los datos
"reales", por lo que no puedo hacer pruebas de rendimiento con un índice
u otro, sino... simplemente "probaría" a hacer búsquedas con un tipo de
índice... luego con otro... y así hasta dar con el más optimo, pero no
los tengo aún, así que tengo que preparar el tema un poco "a ciegas".

Gracias de nuevo,

Saludos

At 19:53 18/05/2009, Silvio Quadri wrote:
>El día 18 de mayo de 2009 11:36, BhEaN <listas(at)bhean(dot)com> escribió:
> > Hola a todos,
> >
> > Tengo una BBDD en PostgreSQL que va a
> contener varios millones de registros,
> > por lo que necesito optimizar las consultas lo máximo posible...
> >
> > Mi problema es que, a la hora de crear los
> indices, no puedo crear un indice
> > (tipo BTREE) en una de las columnas, porque
> algunos de los registros que hay
> > en ella tienen una longitud mayor a la que permite el indice (creo recordar
> > que 2000 y pico... no demasiado...)
>
>
>En el 100% los casos, un índice de 2000 caracteres es un índice inútil
>(para Postgres y para cualquier DBMS)
>Dependiendo de la solución que quieras implementar, como en otros
>mails se dice, hay que usar índice parcial o una solución "Full text
>search". También, si la búsqueda es exacta, podés implementar un
>índice HASH programado a manopla, que no es muy difícil.

Puedes comprimir el texto. En tiempo es similar a
hacer un hash pero tendras una correspondencia
1:1 entre el texto metido y el indice. Ademas
podras hacer comparaciones y aplicar metricas
(una simple resta te puede decir si dos entradas
son similares y cuanto). Puedes usar tanto un
huffman, arith coder o directamente un zip sobre
los 2KB de texto u otro algoritmo de compresion sin perdida.

>Saludos!
>Silvio

---------------------------------
Warning!!! Lemmings inside!!!

El día 19 de mayo de 2009 7:08, BhEaN <listas(at)bhean(dot)com> escribió:
> Antes de nada, muchísimas gracias a todos por vuestras respuestas....
>
> Os contesto debajo de cada parrafo:
>>
>> rtree ya no existe.  Fue reemplazado por GiST ("generalized search
>> tree"), el cual es un sistema de indexamiento para tipos complicados --
>> por ej. se usa para indexar tipos geométricos y también para los índices
>> de búsqueda en texto, entre muchas otras cosas.
>>
>
> Eso es lo que necesito, un índice de búsqueda en texto...
>>
>> El otro sistema de indexamiento extravagante es GIN; es un índice
>> invertido.  También se usa para búsqueda en texto (es más rápido en
>> búsquedas que GiST, pero mucho más lento para agregar nuevos valores).
>> Se puede usar para otras cosas también obviamente.
>>
>
> Eso suena mejor que GiST, puesto que la relación de inserciones/lecturas
> será muy baja... se haran muchísimas más consultas que inserciones, por lo
> que quizás me interese más éste tipo de indice...
>>
>> Hash es un tipo de índice que en Postgres no es recomendable por el
>> momento, así que no lo uses porque tiene varios problemas.
>>
>> Así que tu única alternativa es el btree.  Dado que no puedes almacenar
>> más de 2000 y pico caracteres, vas a tener que buscar mecanismos
>> alternativos.  Si quieres indexar campos grandes de texto, ¿quizás lo que
>> necesitas en realidad es usar el sistema de búsqueda en texto?  Te
>> podemos dar consejos más específicos si nos dices exactamente de qué
>> naturaleza son los datos y cómo serán las consultas.
>>
>>
>
> Ok... siento no haber sido más explícito... lo que tengo exactamente es una
> tabla con muchos anuncios clasificados (los típicos anuncios de "vendo
>  blablabla", o "compro blablablablabla"... hay varios millones de éstos
> anuncios.... y lo que necesito es optimizar todo lo posible las búsquedas en
> ella, ya que debo permitir búsquedas de palabras en el texto y título de
> dichos anuncios... es decir, búsquedas del tipo LIKE '%blablabla%' (lo cual
> tiene pinta de que va a ser horrible para la BBDD, pero es lo que hay,
> jejejee...). No dispongo aún de los datos "reales", por lo que no puedo
> hacer pruebas de rendimiento con un índice u otro, sino... simplemente
> "probaría" a hacer búsquedas con un tipo de índice... luego con otro... y
> así hasta dar con el más optimo, pero no los tengo aún, así que tengo que
> preparar el tema un poco "a ciegas".

Like '%blablabla%' no va a usar un índice tradicional. Revisá la
documentación acerca de "full text search"
(http://www.postgresql.org/docs/8.3/static/textsearch.html) que te va
a servir de ayuda.
Saludos!

Silvio

--
Silvio Quadri

BhEaN escribió:

> Ok... siento no haber sido más explícito... lo que tengo exactamente es
> una tabla con muchos anuncios clasificados (los típicos anuncios de
> "vendo blablabla", o "compro blablablablabla"... hay varios millones de
> éstos anuncios.... y lo que necesito es optimizar todo lo posible las
> búsquedas en ella, ya que debo permitir búsquedas de palabras en el
> texto y título de dichos anuncios... es decir, búsquedas del tipo LIKE
> '%blablabla%' (lo cual tiene pinta de que va a ser horrible para la
> BBDD, pero es lo que hay, jejejee...). No dispongo aún de los datos
> "reales", por lo que no puedo hacer pruebas de rendimiento con un índice
> u otro, sino... simplemente "probaría" a hacer búsquedas con un tipo de
> índice... luego con otro... y así hasta dar con el más optimo, pero no
> los tengo aún, así que tengo que preparar el tema un poco "a ciegas".

Creo que un índice GIN o GiST de búsqueda en texto deberías estar bien.
Asegúrate de usar Postgres 8.3 porque en esa fue integrado el sistema de
búsqueda en texto; en las anteriores, debías instalar un módulo contrib
y el sistema estaba mucho menos depurado.

Si las inserciones van a ser pco frecuentes comparadas con las
búsquedas, creo que es obvio que deberías usar un índice GIN. (Además,
hazte a la idea que las búsquedas no se hacen con LIKE sino con
operadores específicos de búsqueda en texto)

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Coge la flor que hoy nace alegre, ufana. ¿Quién sabe si nacera otra mañana?"

Yo quiero adentrarme en este tema, lo conozco pero no suficiente. Yo les
pido ayuda de donde puedo encontrar documentación o me recomienden algún
libro que aborde el tema de los indices para implementarlos de forma
correcta y optima.

2009/5/19 Silvio Quadri <silvioq(at)gmail(dot)com>

> El día 19 de mayo de 2009 7:08, BhEaN <listas(at)bhean(dot)com> escribió:
> > Antes de nada, muchísimas gracias a todos por vuestras respuestas....
> >
> > Os contesto debajo de cada parrafo:
> >>
> >> rtree ya no existe. Fue reemplazado por GiST ("generalized search
> >> tree"), el cual es un sistema de indexamiento para tipos complicados --
> >> por ej. se usa para indexar tipos geométricos y también para los índices
> >> de búsqueda en texto, entre muchas otras cosas.
> >>
> >
> > Eso es lo que necesito, un índice de búsqueda en texto...
> >>
> >> El otro sistema de indexamiento extravagante es GIN; es un índice
> >> invertido. También se usa para búsqueda en texto (es más rápido en
> >> búsquedas que GiST, pero mucho más lento para agregar nuevos valores).
> >> Se puede usar para otras cosas también obviamente.
> >>
> >
> > Eso suena mejor que GiST, puesto que la relación de inserciones/lecturas
> > será muy baja... se haran muchísimas más consultas que inserciones, por
> lo
> > que quizás me interese más éste tipo de indice...
> >>
> >> Hash es un tipo de índice que en Postgres no es recomendable por el
> >> momento, así que no lo uses porque tiene varios problemas.
> >>
> >> Así que tu única alternativa es el btree. Dado que no puedes almacenar
> >> más de 2000 y pico caracteres, vas a tener que buscar mecanismos
> >> alternativos. Si quieres indexar campos grandes de texto, ¿quizás lo
> que
> >> necesitas en realidad es usar el sistema de búsqueda en texto? Te
> >> podemos dar consejos más específicos si nos dices exactamente de qué
> >> naturaleza son los datos y cómo serán las consultas.
> >>
> >>
> >
> > Ok... siento no haber sido más explícito... lo que tengo exactamente es
> una
> > tabla con muchos anuncios clasificados (los típicos anuncios de "vendo
> > blablabla", o "compro blablablablabla"... hay varios millones de éstos
> > anuncios.... y lo que necesito es optimizar todo lo posible las búsquedas
> en
> > ella, ya que debo permitir búsquedas de palabras en el texto y título de
> > dichos anuncios... es decir, búsquedas del tipo LIKE '%blablabla%' (lo
> cual
> > tiene pinta de que va a ser horrible para la BBDD, pero es lo que hay,
> > jejejee...). No dispongo aún de los datos "reales", por lo que no puedo
> > hacer pruebas de rendimiento con un índice u otro, sino... simplemente
> > "probaría" a hacer búsquedas con un tipo de índice... luego con otro... y
> > así hasta dar con el más optimo, pero no los tengo aún, así que tengo que
> > preparar el tema un poco "a ciegas".
>
>
> Like '%blablabla%' no va a usar un índice tradicional. Revisá la
> documentación acerca de "full text search"
> (http://www.postgresql.org/docs/8.3/static/textsearch.html) que te va
> a servir de ayuda.
> Saludos!
>
>
> Silvio
>
> --
> Silvio Quadri
> --
> TIP 8: explain analyze es tu amigo
>

--
Luis Fernando Curiel Cabrera
- Professional ABACO DE BOLITAS Developer.
- Certified ABACO DE BOLITAS Programmer.

Muchas gracias de nuevo a todos por vuestra ayuda...
Después de todo, parece que la mejor solución será actualizar a Postgres
8.3 y realizar las búsquedas con índices de tipo "full_text"...

Una vez hecho ésto, me aconsejan consultas sobre esos campos con MATCH
[] AGAINST [], o mejor con los LIKE de "toda la vida"? ;)

Un saludo,

Silvio Quadri escribió:
> El día 19 de mayo de 2009 7:08, BhEaN <listas(at)bhean(dot)com> escribió:
>
>> Antes de nada, muchísimas gracias a todos por vuestras respuestas....
>>
>> Os contesto debajo de cada parrafo:
>>
>>> rtree ya no existe. Fue reemplazado por GiST ("generalized search
>>> tree"), el cual es un sistema de indexamiento para tipos complicados --
>>> por ej. se usa para indexar tipos geométricos y también para los índices
>>> de búsqueda en texto, entre muchas otras cosas.
>>>
>>>
>> Eso es lo que necesito, un índice de búsqueda en texto...
>>
>>> El otro sistema de indexamiento extravagante es GIN; es un índice
>>> invertido. También se usa para búsqueda en texto (es más rápido en
>>> búsquedas que GiST, pero mucho más lento para agregar nuevos valores).
>>> Se puede usar para otras cosas también obviamente.
>>>
>>>
>> Eso suena mejor que GiST, puesto que la relación de inserciones/lecturas
>> será muy baja... se haran muchísimas más consultas que inserciones, por lo
>> que quizás me interese más éste tipo de indice...
>>
>>> Hash es un tipo de índice que en Postgres no es recomendable por el
>>> momento, así que no lo uses porque tiene varios problemas.
>>>
>>> Así que tu única alternativa es el btree. Dado que no puedes almacenar
>>> más de 2000 y pico caracteres, vas a tener que buscar mecanismos
>>> alternativos. Si quieres indexar campos grandes de texto, ¿quizás lo que
>>> necesitas en realidad es usar el sistema de búsqueda en texto? Te
>>> podemos dar consejos más específicos si nos dices exactamente de qué
>>> naturaleza son los datos y cómo serán las consultas.
>>>
>>>
>>>
>> Ok... siento no haber sido más explícito... lo que tengo exactamente es una
>> tabla con muchos anuncios clasificados (los típicos anuncios de "vendo
>> blablabla", o "compro blablablablabla"... hay varios millones de éstos
>> anuncios.... y lo que necesito es optimizar todo lo posible las búsquedas en
>> ella, ya que debo permitir búsquedas de palabras en el texto y título de
>> dichos anuncios... es decir, búsquedas del tipo LIKE '%blablabla%' (lo cual
>> tiene pinta de que va a ser horrible para la BBDD, pero es lo que hay,
>> jejejee...). No dispongo aún de los datos "reales", por lo que no puedo
>> hacer pruebas de rendimiento con un índice u otro, sino... simplemente
>> "probaría" a hacer búsquedas con un tipo de índice... luego con otro... y
>> así hasta dar con el más optimo, pero no los tengo aún, así que tengo que
>> preparar el tema un poco "a ciegas".
>>
>
>
> Like '%blablabla%' no va a usar un índice tradicional. Revisá la
> documentación acerca de "full text search"
> (http://www.postgresql.org/docs/8.3/static/textsearch.html) que te va
> a servir de ayuda.
> Saludos!
>
>
> Silvio
>
>

BhEaN escribió:

> Una vez hecho ésto, me aconsejan consultas sobre esos campos con MATCH
> [] AGAINST [], o mejor con los LIKE de "toda la vida"? ;)

Nunca he oído de MATCH [] AGAINST [], ¿supongo que seá un MySQL-ismo?

--
Alvaro Herrera http://www.advogato.org/person/alvherre
"Once again, thank you and all of the developers for your hard work on
PostgreSQL. This is by far the most pleasant management experience of
any database I've worked on." (Dan Harris)
http://archives.postgresql.org/pgsql-performance/2006-04/msg00247.php

Upppps! Tienes razón, jejejee....
Estoy acostumbrado a MySQL... no caí en que esa sintaxis no existe en
PostgreSQL...
;)

Alvaro Herrera escribió:
> BhEaN escribió:
>
>
>> Una vez hecho ésto, me aconsejan consultas sobre esos campos con MATCH
>> [] AGAINST [], o mejor con los LIKE de "toda la vida"? ;)
>>
>
> Nunca he oído de MATCH [] AGAINST [], ¿supongo que seá un MySQL-ismo?
>
>