Re: usando indices => 100 veces mas lento (!)

Hernan Gonzalez wrote:
>
> Un caso un poco raro (pero nada 'patologico', me
> sucedio la semana pasada).
>
> Tenemos dos tablas muy sencillitas A y B :
>
> A
> ---------
> A1 int2
>
> B
> ----------
> B1 int2
> BA oid <--- esto es clave externa, apunta a A.oid
>
> Todos lo campos (tambien los oids) estan indexados (como hash).
> Cada tabla tiene 1000 registros, y para cada registro de
> la tabla A hay exactamente un registro de la tabla B que
> lo apunta.
> De modo que la consulta
>
> [consulta 1]
>
> SELECT COUNT(*) FROM A, B WHERE B.BA = A.oid;
>
> devuelve = 1000.
>
> La consulta es muy sencilla, y es practicamente instantanea
> (digamos, algunas decimas de segundo) (tengo una PII 300Mhz,
> 64 Mb ram, Linux-RedHat 5.1, postgres 6.3.2).
>
> Ahora, hacemos todos los campos A1=0 y B1= 0,
> (si, todos igual ... supongan que es un flag o
> algo asi)....
> despues un 'vacuum' (por las dudas) y consultamos:
>
> [consulta 2]
>
> SELECT COUNT(*) FROM A, B
> WHERE B.BA = A.oid
> AND A.A1 = 0 ;
>
> Lo mismo: 1000 registros encontrados, en menos de un segundo.
> Pero (ahora viene lo interesante, no se impacienten) hagamos
> algo apenas mas complicado:
>
> [consulta 3]
>
> SELECT COUNT(*) FROM A, B
> WHERE
> B.BA = A.oid
> AND A.A1 = 0
> AND B.B1 = 0;
>
> Tambien devuelve 1000 registros.. pero demora 100 veces mas!!!
> (alrededor de un minuto !).
> Y si en lugar de tener 1000 registros tenemos 3000 (como sucedia en
> mi caso real, del cual esto es una simplificacion) la consulta
> demora .... una eternidad (nunca tuve la paciencia de esperar a
> que termine).
>
> Que esta pasando ??
> Perdi un dia entero con este problema, y conclui que
> el problema es que el indice no deberia usarse cuando todos
> los campos valen lo mismo. (!)
> Si hacemos un drop de los indices A1 y/o A2, la consulta es
> instantanea.
> No altera cambiar el indice de hash a btree.
>
> Alguien puede explicarme (o corregirme) ?
> (tengo poca experiencia en bases de datos).
>
> Suena plausible que un indice se comporte mal cuando
> todos (o casi) los campos valen lo mismo... pero no

Claro que es plausible ese comportamiento. Ya que el índice hash
provocará mogollón de colisiones (exactamente 999 colisiones), con lo
que lo único que estás haciendo es leer el índice completo para nada. Si
el índice es btree, pues tres cuartos de lo mismo, ya que todas las
claves del btree tienen el mismo valor, lo que implica que hay que leer
el índice completo para que no resuelva nada.

Aunque no estoy seguro creo que la lectura completa del índice se repite
para cada registro, con lo que se lee muchísimas veces para concluir que
no vale para nada.

Lo correcto para campos en los que los valores se repitan mucho es no
usar índices.

> me deja muy tranquilo... yo no se (supuestamente) a
> priori la distribucion de los valores de los
> campos A1 - B1. No me digan que la moraleja es
> 'no usar indices' ! :-)
> No tiene el Postgres la inteligencia
> necesaria para optimizar la consulta ?

No. De hecho en algún sitio de la documentación se habla de los tipos de
índice que hay que usar para cada consulta. Básicamente si el operador
de comparación es = hay que usar hash y si el operando es >, >=, <, <=
hay que usar btree. Pero siempre sobre campos que tomen valores
diferentes.

>
> La respuesta (y moraleja) provisoria que encontre es esta:
>
> VACUUM ANALYZE !!!!!!!!!!!!!!!!!!!!
>
> Parece que la opcion 'analyze' hace que el vacuum
> revise la distribucion de los valores; despues
> de hacerlo, la consulta anda bien: aunque tiene
> indices definidos decide no usarlos y la consulta
> es instantanea.
>
> Me quedan dudas...
> No se por que la [consulta 2] no tiene el problema
> y la 3 si... ni por que no logre reproducir el
> problema usando una unica tabla ...

Al usar una única tabla se lee esa tabla sólo una vez, al usar dos se
produce un cruce de los valores de una tabla con los de la otra. En
definitiva, con úna única tabla sólo hay implicados 1000 registros, con
dos probablemente sea 1000 * 1000 = 1000000. Aunque por el tiempo de
respuesta que dices que tiene 100 veces más supongo que el 1000 * 1000
se convierte en 1000 * 100, que asumo que es por movidas de cache.

>
> Saludos
>
> Hernan Gonzalez

Saludos.

Carlos.

> Buenos Aires, Argentina
> hgonzal(at)sinectis(dot)com(dot)ar
> --------- Pie de mensaje -------------------------------------------
> Archivo historico: http://tlali.iztacala.unam.mx/maillist/pgsql-ayuda
> Cancelar inscripcion:
> mail to: majordomo(at)tlali(dot)iztacala(dot)unam(dot)mx
> text : cancelacion pgsql-ayuda
--------- Pie de mensaje -------------------------------------------
Archivo historico: http://tlali.iztacala.unam.mx/maillist/pgsql-ayuda
Cancelar inscripcion:
mail to: majordomo(at)tlali(dot)iztacala(dot)unam(dot)mx
text : cancelacion pgsql-ayuda