tabla - usar indices en consulta sql

Consulta SQL para índice/clave primaria ordinal

Question

En nuestro sistema de concurso en línea, hay una tabla de standings frecuentemente cambiantes con columnas enteras (user_id, score) . Ambos están indexados con una restricción única. Se requieren dos tipos de consultas:

1. Dado un score no está en la tabla, devuelva la posición basada en 1 que ocuparía el puntaje si se insertara.
2. Dado un user_id en la tabla, devuelve la posición de la puntuación correspondiente.

En ambos casos, la posición es con respecto al puntaje ascendente: un nuevo puntaje más pequeño que todos los que están actualmente en la tabla tendrá la posición 1.

Aquí está la parte difícil: probablemente no podemos permitirnos un escaneo de tabla. La tabla puede tener hasta 10 millones de registros, y necesitamos manejar al menos 40 consultas por segundo.

¿Cómo hacer esto en PostgreSQL?

Tengo una solución que no es SQL en Berkeley DB que usa sus B-trees habilitados para números de registros lógicos. Fácilmente tiene un rendimiento lo suficientemente bueno. Pero nos gustaría deshacernos de BDB volviendo a implementar con una consulta de PostgreSQL. He intentado lo obvio

select 1+count(*) from standings where score < ? limit 1;

Esto causa un escaneo de tabla.

Espero que la respuesta sea "de ninguna manera" porque la facilidad de número de registro lógico de BDB requiere el bloqueo de todo el árbol B para cada edición. Para obtener el rendimiento O (log N), se basa en conteos de hojas en cada nodo. Todos estos recuentos en el camino hacia la raíz deben cambiar con cada edición; por lo tanto, el bloqueo. Tal bloqueo va en contra de los principios de diseño de PostgreSQL y probablemente de cualquier base de datos multiusuario.

Entonces, si el problema no puede resolverse con PostgreSQL, la confirmación de esto es el siguiente mejor resultado de esta pregunta.

Answer 1

Con una tabla normal, no hay mucho que pueda hacer en PostgreSQL 9.1. count() da como resultado una exploración de tabla , porque los índices no tienen información de visibilidad. Para verificar que las filas no se borren mientras tanto, PostgreSQL debe visitar la tabla.

Si la tabla es de solo lectura (o rara vez se actualiza), puede agregar un número de fila a la tabla. Luego una consulta como:

SELECT rownumber+1
FROM standings
WHERE score < ?
ORDER BY score DESC
LIMIT 1;

Con un índice:

CREATE INDEX standings_score_idx ON standings (score DESC);

Obtendría el resultado casi al instante. Sin embargo, esa no es una opción para una tabla con carga de escritura por razones obvias. Entonces no para ti.

Las buenas noticias: una de las principales características nuevas de la próxima versión de PostgreSQL 9.2 es la adecuada para usted: " índice de cobertura " o " exploración de solo índice ". Cito las notas de la versión 9.2 aquí :

Permitir consultas para recuperar datos solo de índices, evitando el acceso a heap (Robert Haas, Ibrar Ahmed, Heikki Linnakangas, Tom Lane)

Esto a menudo se denomina "escaneos de solo índice" o "índices de cobertura". Esto es posible para las páginas de almacenamiento dinámico con tuplas exclusivamente visibles, según lo informado por el mapa de visibilidad. El mapa de visibilidad se hizo seguro contra fallas como parte necesaria de la implementación de esta función.

Esta publicación del blog de Robert Haas contiene más detalles sobre cómo esto afecta el rendimiento del recuento . Ayuda al rendimiento incluso con una cláusula WHERE , como en su caso.