La meilleure façon de sélectionner des lignes aléatoires PostgreSQL
je veux une sélection aléatoire de lignes dans PostgreSQL, j'ai essayé ceci:
select * from table where random() < 0.01;
mais d'autres recommandent ceci:
select * from table order by random() limit 1000;
j'ai une très grande table avec 500 millions de rangées, je veux qu'elle soit rapide.
quelle approche est la meilleure? Quelles sont les différences? Quelle est la meilleure façon de sélectionner des lignes aléatoires?
11 réponses
étant donné vos spécifications (plus des informations supplémentaires dans les commentaires),
- vous avez une colonne D'identification numérique (nombres entiers) avec seulement peu (ou modérément peu) d'écarts.
- , Évidemment, pas ou peu d'opérations d'écriture.
- votre colonne ID doit être indexée! Une clé primaire sert bien.
la requête ci-dessous n'a pas besoin d'un scan séquentiel de la grande table, seulement un scan d'index.
tout d'abord, obtenir des estimations pour la requête principale:
SELECT count(*) AS ct -- optional
, min(id) AS min_id
, max(id) AS max_id
, max(id) - min(id) AS id_span
FROM big;
la seule pièce peut-être chère est le count(*)
(pour les grandes tables). Étant donné les spécifications ci-dessus, vous n'en avez pas besoin. Une estimation fera très bien, disponible à presque aucun coût ( explication détaillée ici ):
SELECT reltuples AS ct FROM pg_class WHERE oid = 'schema_name.big'::regclass;
aussi longtemps que ct
n'est pas beaucoup plus petit que id_span
, la question va surpasser d'autres approches.
WITH params AS (
SELECT 1 AS min_id -- minimum id <= current min id
, 5100000 AS id_span -- rounded up. (max_id - min_id + buffer)
)
SELECT *
FROM (
SELECT p.min_id + trunc(random() * p.id_span)::integer AS id
FROM params p
,generate_series(1, 1100) g -- 1000 + buffer
GROUP BY 1 -- trim duplicates
) r
JOIN big USING (id)
LIMIT 1000; -- trim surplus
-
" générer des nombres aléatoires dans l'espace
id
. Vous avez "peu de lacunes", donc ajoutez 10% (assez pour couvrir facilement les blancs) au nombre de lignes à récupérer. -
chaque
id
peut être choisi plusieurs fois par hasard (bien que très improbable avec un grand espace d'identification), ainsi grouper les nombres générés (ou utiliserDISTINCT
). -
Rejoignez le
id
s à la grande table. Cela devrait être très rapide avec l'index en place. -
Enfin garniture surplus
id
s qui n'ont pas été mangés par les dupes et les lacunes. Chaque rang a un chance tout à fait égale à cueillir.
version Courte
vous pouvez simplifier cette requête. Le CTE dans la question ci-dessus est juste à des fins éducatives:
SELECT *
FROM (
SELECT DISTINCT 1 + trunc(random() * 5100000)::integer AS id
FROM generate_series(1, 1100) g
) r
JOIN big USING (id)
LIMIT 1000;
affiner avec rCTE
surtout si vous n'êtes pas si sûr des écarts et des estimations.
WITH RECURSIVE random_pick AS (
SELECT *
FROM (
SELECT 1 + trunc(random() * 5100000)::int AS id
FROM generate_series(1, 1030) -- 1000 + few percent - adapt to your needs
LIMIT 1030 -- hint for query planner
) r
JOIN big b USING (id) -- eliminate miss
UNION -- eliminate dupe
SELECT b.*
FROM (
SELECT 1 + trunc(random() * 5100000)::int AS id
FROM random_pick r -- plus 3 percent - adapt to your needs
LIMIT 999 -- less than 1000, hint for query planner
) r
JOIN big b USING (id) -- eliminate miss
)
SELECT *
FROM random_pick
LIMIT 1000; -- actual limit
nous pouvons travailler avec un petit surplus dans la requête de base. S'il y a trop d'écarts pour que nous ne trouvions pas assez de lignes dans la première itération, le rCTE continue à itérer avec le récursive terme. Nous avons encore besoin de relativement quelques trous dans l'espace ID ou la récursion peut s'épuiser avant que la limite soit atteinte - ou nous devons commencer avec un tampon assez grand qui défie l'objectif de l'optimisation des performances.
Les doublons sont éliminés par le UNION
dans le rCTE.
L'extérieur LIMIT
rend la CTE arrêter dès que nous aurons assez de lignes.
cette requête est soigneusement rédigé pour utiliser l'index disponible, générer en fait des lignes aléatoires et ne pas s'arrêter jusqu'à ce que nous remplissions la limite (à moins que la récursion s'épuise). Il y a un certain nombre d'écueils ici si vous allez le réécrire.
Envelopper dans la fonction
pour utilisation répétée avec des paramètres variables:
CREATE OR REPLACE FUNCTION f_random_sample(_limit int = 1000, _gaps real = 1.03)
RETURNS SETOF big AS
$func$
DECLARE
_surplus int := _limit * _gaps;
_estimate int := ( -- get current estimate from system
SELECT c.reltuples * _gaps
FROM pg_class c
WHERE c.oid = 'big'::regclass);
BEGIN
RETURN QUERY
WITH RECURSIVE random_pick AS (
SELECT *
FROM (
SELECT 1 + trunc(random() * _estimate)::int
FROM generate_series(1, _surplus) g
LIMIT _surplus -- hint for query planner
) r (id)
JOIN big USING (id) -- eliminate misses
UNION -- eliminate dupes
SELECT *
FROM (
SELECT 1 + trunc(random() * _estimate)::int
FROM random_pick -- just to make it recursive
LIMIT _limit -- hint for query planner
) r (id)
JOIN big USING (id) -- eliminate misses
)
SELECT *
FROM random_pick
LIMIT _limit;
END
$func$ LANGUAGE plpgsql VOLATILE ROWS 1000;
appel:
SELECT * FROM f_random_sample();
SELECT * FROM f_random_sample(500, 1.05);
vous pourriez même faire ce générique pour travailler pour n'importe quelle table: prendre le nom de la colonne PK et du tableau comme type polymorphe et utiliser EXECUTE
... Mais c'est au-delà de la portée de cette question. Voir:
alternative Possible
si vos exigences permettent jeux identiques pour les appels répétés (et nous parlons de répétés calls) je considérerais une vue matérialisée . Exécuter la requête ci-dessus une fois et écrire le résultat dans un tableau. Les utilisateurs obtiennent une sélection quasi aléatoire à la vitesse de foudre. Rafraîchissez votre choix aléatoire à intervalles ou événements de votre choix.
Postgres 9.5 introduit TABLESAMPLE SYSTEM (n)
c'est très rapide , mais le résultat est pas exactement au hasard . Le manuel:
la méthode
SYSTEM
est beaucoup plus rapide que la méthodeBERNOULLI
lorsque de petits pourcentages d'échantillonnage sont spécifiés, mais il peut retourner un échantillon moins aléatoire du tableau en raison des effets de regroupement.
Et le nombre de lignes retournées peuvent varier énormément. Pour notre exemple, pour obtenir approximativement 1000 lignes, essayez:
SELECT * FROM big TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);
où n est un pourcentage. Le manuel:
les méthodes d'échantillonnage
BERNOULLI
etSYSTEM
acceptent chacune une seule argument qui est la fraction du tableau à l'échantillon, exprimé en pourcentage entre 0 et 100 . Cet argument peut être n'importe quelle expression de valeurreal
.
le Gras c'est moi qui souligne.
Related:
Ou installer le module supplémentaire tsm_system_rows pour obtenir le nombre de lignes demandées exactement (si il y en a assez) et de permettre la plus commode, la syntaxe:
SELECT * FROM big TABLESAMPLE SYSTEM_ROWS(1000);
voir réponse D'Evan pour plus de détails.
mais ce n'est pas exactement aléatoire.
vous pouvez examiner et comparer le plan d'exécution des deux en utilisant
EXPLAIN select * from table where random() < 0.01;
EXPLAIN select * from table order by random() limit 1000;
un test rapide sur une grande table 1 montre, que le ORDER BY
trie d'abord la table complète et choisit ensuite les 1000 premiers articles. Le tri d'une grande table ne se limite pas à lire cette table, mais implique également la lecture et l'écriture de fichiers temporaires. Le where random() < 0.1
ne scanne la table complète qu'une seule fois.
pour les grandes tables ce n'est peut-être pas ce que vous voulez que même un scan complet de table pourrait prendre trop de temps.
une troisième proposition serait
select * from table where random() < 0.01 limit 1000;
celui-ci arrête le scan de table dès que 1000 lignes ont été trouvées et retourne donc plus tôt. Bien sûr, cela réduit un peu le caractère aléatoire, mais c'est peut-être suffisant dans votre cas.
Edit: en plus de ces considérations, vous pouvez vérifier les questions déjà posées pour cela. En utilisant la requête [postgresql] random
renvoie pas mal de résultats.
- rapide aléatoire de la sélection d'une ligne dans Postgres
- comment extraire des lignes de données aléatoires d'une table postgreSQL?
- postgres: obtenir des entrées aléatoires de la table - trop lent
et un article de depez décrivant plusieurs autres approches:
1 "large" comme dans "la table complète ne rentrera pas dans la mémoire".
postgresql ordre aléatoire(), sélectionner des lignes dans un ordre aléatoire:
select your_columns from your_table ORDER BY random()
postgresql ordre aléatoire() avec une nette:
select * from
(select distinct your_columns from your_table) table_alias
ORDER BY random()
postgresql ordre par hasard, à la limite une seule ligne:
select your_columns from your_table ORDER BY random() limit 1
à partir de PostgreSQL 9.5, il y a une nouvelle syntaxe dédiée à l'obtention d'éléments aléatoires à partir d'une table:
SELECT * FROM mytable TABLESAMPLE SYSTEM (5);
cet exemple vous donnera 5% des éléments de mytable
.
voir plus d'explications sur ce billet de blog: http://www.postgresql.org/docs/current/static/sql-select.html
celui qui commande va être le plus lent.
select * from table where random() < 0.01;
passe enregistrement par enregistrement, et décide de le filtrer au hasard ou non. Cela va être O(N)
parce qu'il a seulement besoin de vérifier chaque enregistrement une fois.
select * from table order by random() limit 1000;
va trier la table entière, puis choisir le premier 1000. En dehors de toute magie vaudou dans les coulisses, l'ordre est O(N * log N)
.
L'inconvénient de la random() < 0.01
un est que vous obtiendrez un nombre variable d'enregistrements de sortie.
Note, il y a une meilleure façon de mélanger un ensemble de données que de les trier au hasard: The Fisher-Yates Shuffle , qui s'exécute en O(N)
. Mettre en œuvre le shuffle en SQL semble être tout à fait le défi, cependant.
Voici une décision qui marche pour moi. Je suppose que c'est très simple à comprendre et à exécuter.
SELECT
field_1,
field_2,
field_2,
random() as ordering
FROM
big_table
WHERE
some_conditions
ORDER BY
ordering
LIMIT 1000;
select * from table order by random() limit 1000;
si vous savez combien de lignes vous voulez, Vérifiez tsm_system_rows
.
tsm_system_rows
module fournit la table method sampling method SYSTEM_ROWS, qui peut être utilisé dans la clause TABLESAMPLE d'une commande SELECT.
cette méthode d'échantillonnage de tableau accepte un seul argument entier qui est le nombre maximum de lignes à lire. Le l'échantillon résultant contiendra toujours exactement autant de lignes, à moins que la table ne contienne pas assez de lignes, auquel cas la table entière est sélectionnée. comme la méthode d'échantillonnage intégrée au système, SYSTEM_ROWS effectue un échantillonnage au niveau du bloc, de sorte que l'échantillon n'est pas complètement aléatoire, mais peut être sujet à des effets de regroupement, surtout si seulement un petit nombre de lignes sont demandées.
première installation de l'extension
CREATE EXTENSION tsm_system_rows;
puis votre requête,
SELECT *
FROM table
TABLESAMPLE SYSTEM_ROWS(1000);
si vous voulez une seule rangée, vous pouvez utiliser un calcul offset
dérivé de count
.
select * from table_name limit 1
offset floor(random() * (select count(*) from table_name));
une variante de la vue matérialisée" alternative Possible " esquissée par Erwin Brandstetter est possible.
Dire, par exemple, que vous ne voulez pas de doublons dans les randomisés valeurs retournées. Donc, vous devez définir une valeur booléenne sur la table primaire contenant votre (non randomisée) ensemble de valeurs.
en supposant que c'est la table d'entrée:
id_values id | used
----+--------
1 | FALSE
2 | FALSE
3 | FALSE
4 | FALSE
5 | FALSE
...
peupler le Tableau ID_VALUES
selon les besoins. Ensuite, comme décrit par Erwin, créer une vue matérialisée qui randomise la ID_VALUES
table once:
CREATE MATERIALIZED VIEW id_values_randomized AS
SELECT id
FROM id_values
ORDER BY random();
notez que la vue matérialisée ne contient pas la colonne utilisée, car celle-ci deviendra rapidement périmée. Il n'est pas non plus nécessaire que la vue contienne d'autres colonnes pouvant figurer dans le tableau id_values
.
pour obtenir (et" consommer") des valeurs aléatoires, utilisez une mise à jour-retour sur id_values
, sélectionner id_values
de id_values_randomized
avec une jointure, et appliquer les critères souhaités pour obtenir seulement les possibilités pertinentes. Par exemple:
UPDATE id_values
SET used = TRUE
WHERE id_values.id IN
(SELECT i.id
FROM id_values_randomized r INNER JOIN id_values i ON i.id = r.id
WHERE (NOT i.used)
LIMIT 5)
RETURNING id;
modifier LIMIT
au besoin -- si vous n'avez besoin que d'une valeur aléatoire à la fois, remplacer LIMIT
par 1
.
avec les index appropriés sur id_values
, je crois que la mise à jour-retour devrait s'exécuter très rapidement avec peu de charge. Il retourne randomisés valeurs base de données de l'aller-retour. Les critères pour les rangées "admissibles" peuvent être aussi complexes que requis. Les nouvelles lignes peuvent être ajoutées à la id_values
table à tout moment, et ils auront accès à l'application dès que la vue matérialisée est actualisé (qui peut être exécuté à une période creuse). La création et le rafraîchissement de la vue matérialisée seront lents, mais ils ne doivent être exécutés que lorsque de nouvelles id sont ajoutées à la table id_values
.
ajouter une colonne intitulée r
avec le type serial
. L'indice r
.
supposons que nous avons 200.000 lignes, nous allons générer un nombre aléatoire n
, où 0 < n
<= 200, 000.
sélectionnez les lignes avec r > n
, triez-les ASC
et sélectionnez la plus petite.
Code:
select * from YOUR_TABLE
where r > (
select (
select reltuples::bigint AS estimate
from pg_class
where oid = 'public.YOUR_TABLE'::regclass) * random()
)
order by r asc limit(1);
le code est explicite. Le subquery au milieu est utilisé pour estimer rapidement le nombre de lignes de la table à partir de https://stackoverflow.com/a/7945274/1271094 .
au niveau de l'application, vous devez exécuter l'instruction à nouveau si n
> le nombre de lignes ou besoin de sélectionner plusieurs lignes.
je sais que je suis un peu en retard à la fête, mais je viens de trouver cet outil génial appelé pg_sample :
pg_sample
- extraire un petit échantillon de données d'une base de données PostgreSQL plus grande tout en maintenant l'intégrité référentielle.
j'ai essayé avec une base de données de 350 m et c'était vraiment rapide, Je ne sais pas à propos du randomness .
./pg_sample --limit="small_table = *" --limit="large_table = 100000" -U postgres source_db | psql -U postgres target_db