Meilleure façon de sélectionner des lignes aléatoires PostgreSQL

Question

Je veux une sélection aléatoire de lignes dans PostgreSQL, j’ai essayé ceci :

Mais un autre recommande ceci :

J’ai une très grande table avec 500 millions de lignes, je veux qu’il soit rapide.

Quelle approche est préférable ? Quelles sont les différences ? Quelle est la meilleure façon de sélectionner des lignes aléatoires ?

Answer 1

Compte tenu de votre cahier des charges (plus d'infos dans les commentaires),

• Vous avez un numérique, id colonne avec seulement quelques insuffisances.
• Évidemment, pas ou peu d'opérations d'écriture.
• Votre id colonne indexée! Une clé primaire sert bien.

La requête ci-dessous est beaucoup plus rapide. Il n'a pas besoin d'un balayage séquentiel de la grande table, seule une analyse d'index.

Tout d'abord, obtenir des estimations de 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   bigtbl;

La seule modérément coûteux partie est l' count(*). Compte tenu de spécifications ci-dessus, vous n'en avez pas besoin. Une estimation fera l'amende juste, disponible à un coût quasi nul (explication détaillée ici):

SELECT reltuples AS ct FROM pg_class WHERE oid = 'schema_name.bigtbl'::regclass;

Tant que ct n'est pas plus petit que id_span sur les ordres de grandeur, la requête 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 + floor(random() * p.id_span)::integer AS id
    FROM   params p
          ,generate_series(1, 1100) g  -- 1000 + buffer
    GROUP  BY 1                        -- trim duplicates
    ) r
JOIN   bigtbl USING (id)
LIMIT  1000;                           -- trim surplus

• Générer des nombres aléatoires dans l' id de l'espace. Vous avez "quelques lacunes", il faut donc ajouter 10 % (assez pour couvrir facilement les blancs) pour le nombre de lignes à récupérer.

• Chaque id peut être pris plusieurs fois par hasard (bien que très peu probable avec un grand id de l'espace), de sorte que les groupes les nombres générés (ou utilisez DISTINCT).

• Adhérer à l' ids à la grande table. Cela devrait être très rapide avec l'index en place.

• Enfin garniture surplus ids qui n'ont pas été mangés par les dupes et les lacunes. Chaque ligne a un complètement chance égale d'être sélectionnés.

Version courte

Vous pouvez simplifier cette requête. Le CTE dans la requête ci-dessus est juste à des fins éducatives:

SELECT *
FROM  (
    SELECT DISTINCT 1 + floor(random() * 5100000)::integer AS id
    FROM   generate_series(1, 1100) g
    ) r
JOIN   bigtbl USING (id)
LIMIT  1000;

Alternative Possible

SI vos besoins en matière de permettre ensembles identiques répétés d' appels (et nous parlons d'appels répétés) je considère une vue matérialisée. Exécuter la requête ci-dessus une fois et écrire le résultat dans un tableau. Les utilisateurs bénéficient d'une quasi sélection aléatoire à la vitesse de l'éclair. Actualisez votre au choix aléatoire à des intervalles ou des événements de votre choix.

Answer 2

Vous pouvez examiner et de comparer le plan d'exécution de la fois en utilisant

EXPLAIN select * from table where random() < 0.01;
EXPLAIN select * from table order by random() limit 1000;

Un test rapide sur un grand tableau¹ montre, que l' ORDER BY première trie le tableau complet et prend ensuite la première de 1000 points. Tri d'un tableau de grande taille ne lit pas seulement de la table, mais aussi implique la lecture et l'écriture des fichiers temporaires. L' where random() < 0.1 seulement des scans de la table une fois.

Pour les grandes tables, cela pourrait ne pas être ce que vous voulez comme même un tableau complet de numérisation pourrait prendre pour de long.

Une troisième proposition serait

select * from table where random() < 0.01 limit 1000;

Ce que l'on cesse de l'analyse de la table dès que 1000 lignes ont été trouvés et donc des rendements plus tôt. Bien sûr, ce tourbières dans le caractère aléatoire de la un peu, mais c'est peut-être assez bon dans votre cas.

Edit: d'Ailleurs de cette considérations, vous pouvez consulter la déjà posé des questions pour cette. L'aide de la requête [postgresql] random des rendements tout à fait quelques coups.

• rapide aléatoire de la sélection d'une ligne dans Postgres
• Comment faire pour récupérer des variables aléatoires de lignes d'une table postgreSQL?
• postgres: obtenir aléatoire des entrées de la table - trop lent

Et un article lié de depez décrivant plusieurs approches:

• http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/

---

¹ "les grands", comme dans "la table ne rentre pas dans la mémoire".

Answer 3

<h2>Ordonnance de PostgreSQL par aléatoires, de sélectionner des lignes dans un ordre aléatoire :<pre><code></code></pre><h2>ordre de PostgreSQL par aléatoire avec un distinct :</h2><pre><code></code></pre><h2>ordre de PostgreSQL par random limiter une ligne :</h2><pre><code></code></pre></h2>

Answer 4

L'un avec la COMMANDE PAR est va être la plus lente.

select * from table where random() < 0.01; va de record en record, et décide au hasard de la filtrer ou pas. 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 le tableau en entier, puis choisissez les 1000 premiers. De côté de toute la magie vaudou derrière les coulisses, l'ordre en est - O(N * log N).

L'inconvénient de l' random() < 0.01 la première est que vous obtiendrez un nombre variable de sortie des dossiers.

---

Remarque, il y a une meilleure façon de brouiller un ensemble de données que le tri par hasard: Le Shuffle de Fisher-Yates, qui s'exécute en O(N). La mise en œuvre de l'aléatoire dans SQL ressemble assez le défi, bien qu'.

Answer 5

Une variation de la vue matérialisée "alternative" décrites 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
           ...

Remplir l' ID_VALUES tableau. Puis, comme décrit par Erwin, de créer une vue matérialisée qui rend aléatoire l' ID_VALUES tableau une fois:

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 l'utilisé de la colonne, parce que cela va rapidement devenir obsolètes. Ni la vue nécessité de contenir d'autres colonnes qui peuvent être dans l' id_values table.

Afin d'obtenir (et de "consommer") de valeurs aléatoires, utiliser une mise à JOUR-RETOUR sur id_values, en sélectionnant id_values de id_values_randomized avec une jointure, et en appliquant les critères souhaités pour obtenir uniquement des 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;

Variation LIMIT comme il le faut, si vous avez seulement besoin d'une valeur aléatoire à un moment, le changement LIMIT de 1.

Avec le bon index sur id_values, je crois que la mise à JOUR-le RETOUR doit s'exécuter très rapidement avec peu de charges. Il retourne randomisés valeurs avec une base de données de l'aller-retour. Les critères pour les "admissibles", les lignes peuvent être aussi complexe que nécessaire. Les nouvelles lignes peuvent être ajoutées à l' 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). Création et actualisation de la vue matérialisée sera lent, mais il ne doit être exécutée lorsque de nouvelles id sont ajoutés à l' id_values table.