postgresql - selección de fila aleatoria rápida en Postgres

random-access

Question

Tengo una tabla en postgres que contiene un par de millones de filas. Lo revisé en Internet y encontré lo siguiente

SELECT myid FROM mytable ORDER BY RANDOM() LIMIT 1;

funciona, pero es realmente lento ... ¿hay otra forma de hacer esa consulta, o una forma directa de seleccionar una fila al azar sin leer toda la tabla? por cierto, ''myid'' es un número entero, pero puede ser un campo vacío.

Gracias

Answer 1

Consulte este enlace para ver algunas opciones diferentes. http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/

Actualización: (A.Hatchkins)

El resumen del artículo (muy) extenso es el siguiente.

El autor enumera cuatro enfoques:

1) ORDER BY random() LIMIT 1; -- lento

2) ORDER BY id where id>=random()*N LIMIT 1 - no uniforme si hay lagunas

3) columna aleatoria - necesita ser actualizada de vez en cuando

4) agregado aleatorio personalizado - método de astucia, podría ser lento: aleatorio () debe generarse N veces

y sugiere mejorar el método n. ° 2 mediante el uso

5) ORDER BY id where id=random()*N LIMIT 1 con consultas posteriores si el resultado está vacío.

Answer 2

Es posible que desee experimentar con OFFSET , como en

SELECT myid FROM mytable OFFSET floor(random()*N) LIMIT 1;

El N es el número de filas en mytable . Es posible que primero deba hacer un SELECT COUNT(*) para calcular el valor de N

Actualización (por Antony Hatchkins)

Debes usar el floor aquí:

SELECT myid FROM mytable OFFSET floor(random()*N) LIMIT 1;

Considere una tabla de 2 filas; random()*N genera 0 <= x < 2 y por ejemplo SELECT myid FROM mytable OFFSET 1.7 LIMIT 1; devuelve 0 filas debido al redondeo implícito al int más cercano.

Answer 3

He encontrado una solución muy rápida sin TABLESAMPLE . Mucho más rápido que OFFSET random()*N LIMIT 1 . Ni siquiera requiere contar la mesa.

La idea es crear un índice de expresión con datos aleatorios pero predecibles, por ejemplo md5(primary key) .

Aquí hay una prueba con datos de muestra de 1M de filas:

create table randtest (id serial primary key, data int not null);

insert into randtest (data) select (random()*1000000)::int from generate_series(1,1000000);

create index randtest_md5_id_idx on randtest (md5(id::text));

explain analyze
select * from randtest where md5(id::text)>md5(random()::text)
order by md5(id::text) limit 1;

Resultado:

 Limit  (cost=0.42..0.68 rows=1 width=8) (actual time=6.219..6.220 rows=1 loops=1)
   ->  Index Scan using randtest_md5_id_idx on randtest  (cost=0.42..84040.42 rows=333333 width=8) (actual time=6.217..6.217 rows=1 loops=1)
         Filter: (md5((id)::text) > md5((random())::text))
         Rows Removed by Filter: 1831
 Total runtime: 6.245 ms

En ocasiones, esta consulta (con alrededor de 1 / Number_of_rows probabilidad) devuelve 0 filas, por lo que debe verificarse y volver a ejecutarse. Además, las probabilidades no son exactamente las mismas: algunas filas son más probables que otras.

Para comparacion:

explain analyze SELECT id FROM randtest OFFSET random()*1000000 LIMIT 1;

Los resultados varían ampliamente, pero pueden ser bastante malos:

 Limit  (cost=1442.50..1442.51 rows=1 width=4) (actual time=179.183..179.184 rows=1 loops=1)
   ->  Seq Scan on randtest  (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.016..134.835 rows=915702 loops=1)
 Total runtime: 179.211 ms
(3 rows)

Answer 4

Intenté esto con una subconsulta y funcionó bien. Offset, al menos en Postgresql v8.4.4 funciona bien.

select * from mytable offset random() * (select count(*) from mytable) limit 1 ;

Answer 5

Necesitas usar floor :

SELECT myid FROM mytable OFFSET floor(random()*N) LIMIT 1;

Answer 6

PostgreSQL 9.5 introdujo un nuevo enfoque para una selección de muestras mucho más rápida: TABLESAMPLE

La sintaxis es

SELECT * FROM my_table TABLESAMPLE BERNOULLI(percentage);
SELECT * FROM my_table TABLESAMPLE SYSTEM(percentage);

Esta no es la solución óptima si solo quiere seleccionar una fila, ya que necesita conocer el COUNT de la tabla para calcular el porcentaje exacto.

Para evitar un COUNT lento y usar TABLESAMPLE rápido para tablas de 1 fila a miles de millones de filas, puede hacer:

 SELECT * FROM my_table TABLESAMPLE SYSTEM(0.000001) LIMIT 1;
 if you got no result:
 SELECT * FROM my_table TABLESAMPLE SYSTEM(0.00001) LIMIT 1;
 if you got no result:
 SELECT * FROM my_table TABLESAMPLE SYSTEM(0.0001) LIMIT 1;
 if you got no result:
 SELECT * FROM my_table TABLESAMPLE SYSTEM(0.001) LIMIT 1;
 ...

Esto podría no parecer tan elegante, pero probablemente sea más rápido que cualquiera de las otras respuestas.

Para decidir si desea utilizar BERNULLI o el SISTEMA, lea sobre la diferencia en TABLESAMPLE