Re: [Pgsql-ayuda] Informacion del explain. Indices no funcionan :(

On Fri, Jul 25, 2003 at 02:51:38PM +0000, Edwin Quijada wrote:

Edwin,

> Tengo una tabla en mi base de datos con 513213 registros. Nunca me habia
> importado la velocidad pero oyendo varias discusiones aca he decidido
> probar la velocidad. Resulta que m i tabla tiene un pk int8 y cuando hago
> una busqueda sobre este resulta que viene secuencial uy no indexada como
> suponia. Vi un mensaje de Alvaro donde le contestaba a otra persona que
> debia de hacer un cast a int2 , su problema era con int2, asi que tambien
> lo hice pero con int8 y funciono el explain me dio una busqueda indexada
> pero solo para busqueda exactas cuando use el operaqdor < o > volvio a
> secuencial.

Lo que sucede es que el `planner' decide que es mas conveniente hacer
un recorrido secuencial de la tabla que usar el indice.

El Planner es el sistema que toma una consulta, genera todas las
posibles maneras de resolverla y calcula los costos de cada una de
ellas, usando las estadisticas que tiene acerca de las tablas. Luego
escoge la manera mas barata. Las estadisticas de que el planner dispone
son extraídas por ANALYZE o VACUUM ANALYZE; por lo tanto, para que las
consultas se puedan hacer de la manera más eficiente posible, es
importante ejecutar ANALYZE siempre que hayan cambios significativos en
los datos (por ej. cuando insertas muchas tuplas, o cuando borras gran
parte de la tabla).

La pregunta es: por que decide hacer un recorrido secuencial?

La respuesta es que el recorrido del indice sólo es conveniente cuando
la cantidad de tuplas (tambien conocidas como filas o registros) que se
van a extraer de la tabla es menor que un cierto porcentaje del total de
la tabla. Cuando de una tabla de 500000 registros tienes que extraer
sólo aquellos que coinciden con un valor particular (columna=42), el
porcentaje de tuplas a extraer suele ser muy pequeño (quizás una sola
tupla, es decir 1/500000 = 0.000002 = 0.0002%).

En cambio cuando usas un operador de desigualdad (<, >, >=, <=) el
porcentaje puede ser mucho mayor, dependiendo del rango. Por ejemplo si
los valores están entre 1 y 100, y le pides <100, entonces el porcentaje
será algo como 99% (y por lo tanto conviene un recorrido secuencial).
En cambio si le pidieras <10, el porcentaje sería algo como 10%.

Nota que este ejemplo de porcentajes está muy simplificado; en realidad
el planner dispone de mucha más información estadística que simplemente
"el rango de valores", por lo que sus estimaciones suelen ser bastante
precisas.

En los casos en que las estimaciones no son precisas debido a que la
distribucion de valores no sea representable con unas pocas variables
(i.e. no sigue aproximadamente una distribucion normal, ni una
distribucion uniforme, etc), entonces te puede convenir aumentar la
cantidad de datos que ANALYZE toma para esa columna, usando

ALTER TABLE tabla ALTER COLUMN columna SET STATISTICS numero;

donde "numero" es la cantidad de datos a tomar. Por defecto, 'numero'
es 10 para cada columna de cada tabla. Esto es suficiente en la gran
mayoría de los casos. Cuando EXPLAIN ANALYZE muestra que una estimación
(de numero de tuplas a extraer en un paso de un plan) está muy alejada
de la realidad, conviene ir aumentando `numero' paulatinamente hasta
encontrar una estimacion aceptable (se debe permitir un cierto margen de
error; la idea no es obtener numeros _exactos_... en general es
suficiente con que los numeros esten dentro del mismo orden de
magnitud).

Ojo, no es conveniente aumentarlo demasiado, porque numeros muy grandes
pueden causar demoras significativas en los calculos de los planes.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Major Fambrough: You wish to see the frontier?
John Dunbar: Yes sir, before it's gone.