tengo la siguiente tabla
create table log ( id bigint default nextval('log_id_seq'::regclass) not null constraint log_pkey primary key, level integer, category varchar(255), log_time timestamp, prefix text, message text );Contiene como 3 millones de filas.
Estoy comparando las siguientes consultas:
EXPLAIN SELECT id FROM log WHERE log_time < now() - INTERVAL '3 month' LIMIT 100000lo que da el siguiente plan:
Limit (cost=0.00..19498.87 rows=100000 width=8) -> Seq Scan on log (cost=0.00..422740.48 rows=2168025 width=8) Filter: (log_time < (now() - '3 mons'::interval))Y la misma consulta con la instrucción ORDER BY id agregada:
EXPLAIN SELECT id FROM log WHERE log_time < now() - INTERVAL '3 month' ORDER BY id ASC LIMIT 100000cuyos rendimientos
Limit (cost=0.43..25694.15 rows=100000 width=8) -> Index Scan using log_pkey on log (cost=0.43..557048.28 rows=2168031 width=8) Filter: (log_time < (now() - '3 mons'::interval))Tengo las siguientes preguntas:
La ausencia de la instrucción ORDER BY permite que Postgres no se preocupe por el orden de las filas. También se pueden entregar ordenados. ¿Por qué no usa el índice sin ORDER BY?
WHERE de la consulta contiene una columna no indexada y para obtener esa columna, se requerirá un escaneo secuencial de la base de datos, pero la consulta con ORDER BY no indica eso.La página del manual de Postgres dice:
Para una consulta que requiere escanear una gran fracción de la tabla, es probable que una ordenación explícita sea más rápida que usar un índice porque requiere menos E/S de disco debido a que sigue un patrón de acceso secuencial.
¿Puede aclararme esta afirmación? El índice siempre está ordenado. Y leer una estructura ordenada siempre es más rápido, siempre es un acceso secuencial (al menos en términos de escaneo de páginas) que leer datos no ordenados y luego ordenarlos manualmente.
¿Puede aclararme esta afirmación? El índice siempre está ordenado. Y leer una estructura ordenada siempre es más rápido, siempre es un acceso secuencial (al menos en términos de escaneo de páginas) que leer datos no ordenados y luego ordenarlos manualmente.
El índice se lee secuencialmente, sí, pero postgres necesita hacer un seguimiento con una lectura de las filas de la tabla. Es decir, en la mayoría de los casos, si un índice identifica 100 filas, postgres necesitará realizar hasta 100 lecturas aleatorias en la tabla.
Internamente, el planificador de postgres pesa las lecturas secuenciales y aleatorias de manera diferente, y las lecturas aleatorias generalmente son mucho más costosas. Las configuraciones seq_page_cost y random_page_cost las determinan. Hay otras configuraciones que puede ver y modificar si lo desea, aunque le recomiendo ser muy conservador con las modificaciones.
Volvamos a tus preguntas anteriores:
La ausencia de la instrucción ORDER BY permite que Postgres no se preocupe por el orden de las filas. También se pueden entregar ordenados. ¿Por qué no usa el índice sin ORDER BY?
La razón es el género. Como observará más adelante, el índice no incluye la columna de restricción, por lo que no tiene ningún sentido utilizar el índice. En cambio, el planificador básicamente está diciendo "lea toda la tabla, descubra qué filas se ajustan a la restricción y luego devuelva las primeras 100000 de ellas, en cualquier orden que las encontremos".
El género cambia las cosas. En ese caso, el planificador dice "necesitamos ordenar por este campo, y tenemos un índice que ya está ordenado, así que lea las filas de la tabla en orden de índice, verificando la restricción, hasta que tengamos 100000 de ellos, y devolver ese conjunto".
Notará que las estimaciones de costos (por ejemplo, '0.43..25694.15') son mucho más altas para la segunda consulta: el planificador piensa que hacer tantas lecturas aleatorias del escaneo de índice costará mucho más que solo leer todo tabla a la vez sin clasificación.
Espero que ayude, y hágamelo saber si tiene más preguntas.