selección rápida de filas aleatorias en Postgres

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 desea seleccionar solo una fila, porque necesita saber el RECUENTO de la tabla para calcular el porcentaje exacto.

Para evitar un CONTEO lento y usar TABLAS rápidas 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 es más rápido que cualquiera de las otras respuestas.

Para decidir si desea utilizar el SISTEMA BERNULLI oder, lea sobre la diferencia en http://blog.2ndquadrant.com/tablesample-in-postgresql-9-5-2 /

Probé 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 ;

Necesitas usar floor:

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

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

Actualizar: (A. Hatchkins)

El resumen del artículo (muy) largo 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 huecos

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

4) personalizado aleatorio aggregate method método astuto, podría ser lento: random() necesita ser generado N veces

Y sugiere mejorar el método #2 usando

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

Se me ocurrió una solución muy rápida sin TABLESAMPLE. Mucho más rápido que OFFSET random()*N LIMIT 1. Ni siquiera requiere recuento de 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 filas de 1 M:

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

Esta consulta puede a veces (con aproximadamente 1/probabilidad Number_of_rows) devolver 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 otros.

Para comparación:

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)

La forma más fácil y rápida de obtener una fila aleatoria es usar la extensión tsm_system_rows:

CREATE EXTENSION IF NOT EXISTS tsm_system_rows;

Luego puede seleccionar el número exacto de filas que desea:

SELECT myid  FROM mytable TABLESAMPLE SYSTEM_ROWS(1);

Esto está disponible con PostgreSQL 9.5 y versiones posteriores.

Véase: https://www.postgresql.org/docs/current/static/tsm-system-rows.html