Comment sélectionner efficacement des lignes aléatoires dans PostgreSQL ?

Méthode de sélection de lignes aléatoires efficace PostgreSQL

Pour sélectionner des lignes aléatoires dans PostgreSQL, la meilleure méthode dépend de la taille de la table, des index disponibles et du niveau de caractère aléatoire requis.

Pour une très grande table avec 500 millions de lignes et une colonne d'identifiant numérique (par exemple id) :

• Méthode la plus rapide :

  • Utilisez les fonctions CTE et random() pour générer des identifiants aléatoires dans l'espace d'identification.
  • Joignez l'ID généré au tableau en utilisant la colonne id.
  • Filtrez les doublons et supprimez les identifiants redondants.

<code class="language-sql">WITH params AS (
   SELECT 1       AS min_id,           -- 最小id
        , 5100000 AS id_span          -- 四舍五入。(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                        -- 去除重复项
) r
JOIN   big USING (id)
LIMIT  1000;                          -- 去除多余项</code>

• Méthode d'amélioration :

  • Utilisez le CTE récursif (random_pick) pour éliminer toute lacune dans l'espace d'identification.
  • Fusionnez les résultats récursifs pour éliminer les doublons.
  • Appliquer externeLIMIT pour satisfaire les contraintes.

<code class="language-sql">WITH RECURSIVE random_pick AS (
   SELECT *
   FROM  (
      SELECT 1 + trunc(random() * 5100000)::int AS id
      FROM   generate_series(1, 1030)  -- 1000 + 百分之几 - 根据需要调整
      LIMIT  1030                      -- 查询规划器提示
      ) r
   JOIN   big b USING (id)             -- 消除缺失

   UNION                               -- 消除重复项
   SELECT b.*
   FROM  (
      SELECT 1 + trunc(random() * 5100000)::int AS id
      FROM   random_pick r             -- 加上百分之三 - 根据需要调整
      LIMIT  999                       -- 小于1000，查询规划器提示
      ) r
   JOIN   big b USING (id)             -- 消除缺失
   )
TABLE  random_pick
LIMIT  1000;  -- 实际限制</code>

• Fonctions générales :

  • Enveloppez les requêtes ci-dessus dans une fonction afin qu'elles puissent être réutilisées pour n'importe quelle table avec des colonnes entières uniques.

<code class="language-sql">CREATE OR REPLACE FUNCTION f_random_sample(_tbl_type anyelement
                                         , _id text = 'id'
                                         , _limit int = 1000
                                         , _gaps real = 1.03)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql VOLATILE ROWS 1000 AS
$func$
DECLARE
   _tbl text := pg_typeof(_tbl_type)::text;
   _estimate int := (...);
BEGIN
   RETURN QUERY EXECUTE format(
   $$
   WITH RECURSIVE random_pick AS (
      SELECT ...
      FROM  ...
     ...
   )
   TABLE  random_pick
   LIMIT  ;
   $$
 , _tbl, _id
   )
   USING (...);
END
$func$;</code>

Pour les scénarios qui ne nécessitent pas de hasard précis ni d'appels répétés :

• Vue matérialisée :

  • Créez une vue matérialisée pour stocker des lignes sélectionnées de manière approximativement aléatoire.
  • Actualisez périodiquement les vues matérialisées.

• TABLESAMPLE SYSTEM (n) :

  • Introduit dans PostgreSQL 9.5, TABLESAMPLE SYSTEM (n) fournit une méthode d'échantillonnage aléatoire rapide et inexacte.
  Le paramètre
  • n représente le pourcentage de tables à échantillonner.

<code class="language-sql">SELECT * FROM big TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);</code>

Autres notes :

• Pour de meilleures performances, utilisez un index sur la colonne ID.
• Les fonctions random() de PostgreSQL ne sont pas sécurisées cryptographiquement.
• L'approche proposée offre un degré élevé de caractère aléatoire pour la plupart des cas d'utilisation pratiques.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!