Postgresql 9.4: la consulta MÁS RÁPIDA para seleccionar y actualizar en grandes conjuntos de datos (> 30 millones de filas) con escrituras / lecturas pesadas y bloqueos
Quiero seleccionar una fila entre un gran conjunto de datos (> 30 millones de filas) con escrituras / lecturas pesadas AL AZAR.
Mi problema no puedo dejar que la elección arbitraria postgresql (esa hubiera sido la consulta más barata / más rápida, solo usando 'límite 1') ya que se comporta de manera errática y de "formas oscuras": vea mi problema inicial aquí:postgresql 9.4: evite que la aplicación seleccione siempre las últimas filas actualizadas
Aquí está mi consulta actual
UPDATE opportunities s
SET opportunity_available = false
FROM (
SELECT id
FROM opportunities
WHERE deal_id = #{@deal.id}
AND opportunity_available
AND pg_try_advisory_xact_lock(id)
LIMIT 1
FOR UPDATE
) sub
WHERE s.id = sub.id
RETURNING s.prize_id, s.id;
// inspired by https://stackoverflow.com/questions/33128531/put-pg-try-advisory-xact-lock-in-a-nested-subquery
Hice una primera pregunta (postgresql 9.4: evite que la aplicación seleccione siempre las últimas filas actualizadas) pero creo que incluso si no hay una respuesta clara, lo que está sucediendo es que postgresql se deja libre para hacer una selección arbitraria (ya que solo uso 'LIMIT 1' porque quería la consulta más barata / más rápida), que es MUY DIFERENTE de una elección ALEATORIA. Pero como consecuencia, postgresql a menudo genera las últimas filas actualizadas por el administrador (que siempre son oportunidades que tienen todos los premios), en lugar de elegir realmente la fila al azar.
Creo que necesito alejarme de la elección arbitraria para obtener una elección ALEATORIA.
En ese contexto, ¿cuál es la mejor opción, es decir, la más rápida para seleccionar (observe el 'FOR ACTUALIZADO' y 'bloqueos de aviso' ya que necesito bloquear las filas cuando se actualizan para actualizar para evitar llamadas concurrentes ... Usaré pronto en postgresql 9.5 omitir bloqueado tan pronto como 9.5 salga de beta)
Utilizarorden con random () pero es notoriamente (leer muchas publicaciones en stackoverflow y stack exchange dba sobre esto) REALMENTE realmente lento en grandes conjuntos de datos => "ORDER BY RAND () es lento porque el DBMS tiene que leer todas las filas, ordenarlas todas, solo para mantener unas pocas filas. Por lo tanto, el rendimiento de esta consulta depende en gran medida del número de filas de la tabla y disminuye a medida que aumenta el número de filas ", como se explicóaquí oaquí
Utilizarcompensar También se sabe que es lento para grandes conjuntos de datos
UtilizarMuestreo como se explica / aconseja aquí por lo que parecen grandes expertos:https://www.periscopedata.com/blog/how-to-sample-rows-in-sql-273x-faster.html
Utilizarotra tecnica avanzada podrías sugerir