Skip site navigation (1) Skip section navigation (2)

Re: extraer parte decimal

From: Álvaro Hernández Tortosa <aht(at)Nosys(dot)es>
To: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Cc: Juan <smalltalker(dot)marcelo(at)gmail(dot)com>,"gilberto(dot)castillo" <gilberto(dot)castillo(at)etecsa(dot)cu>,Ayuda <pgsql-es-ayuda(at)postgresql(dot)org>
Subject: Re: extraer parte decimal
Date: 2011-06-23 16:39:39
Message-ID: 20110623163939.GV12026@nosys.net (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
Wed, Jun 22, 2011 at 06:12:16PM -0400, Alvaro Herrera escribió:

>Excerpts from Juan's message of mié jun 22 14:59:12 -0400 2011:
>> Perdon
>> 
>> el lenguaje usado es 'plpgsql'
>> lo que pasa es q estoy haciendo algo usando random para seleccionar
>> un grupo de registros.
>
>me parece que vas por mal camino.  Lee esto
>
>http://blog.rhodiumtoad.org.uk/2009/03/08/selecting-random-rows-from-a-table/

	Efectivamente, hay una solución muy sencilla (como la que
comenté yo) pero que es conocido sólo aplica a tablas pequeñas, como el
link que que envía Álvaro demuestra. No hay soluciones inmediatas para
tablas grandes, como el post (y muchos otros al respecto) comentan, o
que requieren añadir columnas, depender de PKs numéricas o columnas
seriales que no tengan "muchos huecos". Ninguna aparenta, por tanto, una
buena solución.

	Así que, volviendo a pensar sobre el tema, se me ha ocurrido
una solución que podría funcionar bien :) La idea básica es generar N
números aleatorios al principio y luego usar cursores y desplazamientos
para extraer dichos registros. Así que he escrito una pequeña función
para probarlo:

CREATE FUNCTION random_rows(table_name text, n_rnd_rows int, n_rows bigint) RETURNS SETOF RECORD AS $$
	DECLARE
		curs		refcursor;
		ret		record;
		rnd_row_pos	int[];
	BEGIN
		FOR i IN 1..n_rnd_rows LOOP
			rnd_row_pos[i] = floor(random() * n_rows);
		END LOOP;
	
		-- Sort the array
		SELECT INTO rnd_row_pos array_agg(i) FROM (SELECT * FROM unnest(rnd_row_pos) i ORDER BY i) AS i;

		OPEN curs FOR EXECUTE 'SELECT * FROM ' || table_name; -- ORDER is not relevant

		MOVE ABSOLUTE rnd_row_pos[1] - 1 FROM curs;
		FETCH NEXT FROM curs INTO ret;
		RETURN NEXT ret;

		FOR i IN 2..n_rnd_rows LOOP
			MOVE RELATIVE rnd_row_pos[i] - rnd_row_pos[i - 1] - 1 FROM curs;
			FETCH NEXT FROM curs INTO ret;
			RETURN NEXT ret;
		END LOOP;
		
		CLOSE curs;

		RETURN;
	END;
$$ LANGUAGE plpgsql;

	Esta función lee los registros de origen "en el orden que están
en disco". Sin embargo, esto no es un problema porque luego se
seleccionan aleatoriamente los registros a obtener.


	A nivel de demo, probando con una tabla llamada 'random' que
tiene 10 millones de filas generadas con generate_series:

- Solución trivial (sólo tablas pequeñas):

	aht=> EXPLAIN ANALYZE SELECT * FROM random ORDER BY random() LIMIT 20;
	[...]
	Total runtime: 7349.974 ms

- Con la función que comento, pero obteniendo el número de filas con COUNT(*),
que requiere un seqscan, ya supone un ahorro importante de tiempo (y
creciente con el tamaño de la tabla y muy significativamente si work_mem
limitara):

	aht=> EXPLAIN ANALYZE SELECT * FROM random_rows('random', 20, (SELECT COUNT(*) FROM random)) AS (i integer);
	[...]
	Total runtime: 5472.431 ms


- Si en lugar de hacer COUNT(*) sabemos el número de registros o queremos
restringir el número de filas random a los primeros N registros, la
técnica que comento rinde fantásticamente:

	aht=> EXPLAIN ANALYZE SELECT * FROM random_rows('random', 20, 10000000) AS (i integer);
	[...]
	Total runtime: 843.520 ms

- Otra opción sería fiarnos de las estadísticas de postgres y usarlas para
estimar el número de registros:

	aht=> EXPLAIN ANALYZE SELECT * FROM random_rows('random', 20, (SELECT n_live_tup FROM pg_stat_all_tables WHERE relname = 'random')) AS (i integer);
	[...]
	Total runtime: 856.390 ms

- Por razones de eficiencia, los resultados de esta función salen
"ordenados". A su vez se pueden reordenar aleatoriamente si se quiere
sin mucho costo adicional:

	aht=> EXPLAIN ANALYZE SELECT * FROM random_rows('random', 20, 10000000) AS (i integer) ORDER BY random() LIMIT 20;
	[...]
	Total runtime: 863.488 ms

Y parece funcionar:

	aht=> SELECT * FROM random_rows('random', 20, 10000000) AS (i integer) ORDER BY random() LIMIT 20;
	    i    
	---------
	 2078060
	 4894086
	 4437251
	 3700397
	  642530
	 7685856
	 2297124
	 5127436
	 2764292
	  376513
	 6676704
	  791899
	  671257
	 1029904
	 2942200
	 1237436
	 9265684
	 1302897
	 7479438
	 3685012
	(20 filas)


	¿Qué os parece el método? ¿Se os ocurre algún punto flaco que
pueda tener?

	Saludos,

	Álvaro

-- 

Álvaro Hernández Tortosa


-----------
NOSYS
Networked Open SYStems

In response to

Responses

pgsql-es-ayuda by date

Next:From: JuanDate: 2011-06-23 16:53:02
Subject: Re: comop programar con arrays
Previous:From: Lazaro Rubén García MartinezDate: 2011-06-23 16:34:52
Subject: Auditorias en PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group