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

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

PostgreSQL fournit une variété de méthodes pour sélectionner efficacement des lignes aléatoires.

Méthode 1 : utiliser Random() et la clause Limit

Cette méthode utilise la fonction random() et la clause LIMIT :

SELECT *
FROM table
ORDER BY random()
LIMIT 1000;

Cependant, pour les grandes tables, cette méthode peut être plus lente car elle nécessite une analyse complète de la table.

Méthode 2 : méthode basée sur un index

Cette méthode utilise l'index de clé primaire pour optimiser la requête :

WITH params AS (
   SELECT 1       AS min_id,          -- 最小ID (大于等于当前最小ID)
        , 5100000 AS id_span          -- 四舍五入 (max_id - min_id + 缓冲)
)
SELECT *
FROM  (
   SELECT p.min_id + trunc(random() * p.id_span)::integer AS id
   FROM   params p
        , generate_series(1, 1100) g  -- 1000 + 缓冲
   GROUP  BY 1                        -- 去除重复项
) r
JOIN   table USING (id)
LIMIT  1000;                          -- 去除多余项

Cette méthode est plus rapide que la première méthode car elle utilise une analyse d'index au lieu d'une analyse de table complète.

Méthode 3 : Utiliser le CTE récursif

Cette méthode utilise une expression de table commune (CTE) récursive pour gérer les valeurs manquantes dans la colonne ID :

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   table b USING (id)             -- 去除缺失值

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

Méthode 4 : Utiliser TABLESAMPLE SYSTEM (n)

PostgreSQL 9.5 a introduit la syntaxe TABLESAMPLE SYSTEM (n), où n est un pourcentage compris entre 0 et 100 :

SELECT *
FROM big
TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);

Cette méthode est rapide, mais peut ne pas renvoyer d'échantillons véritablement aléatoires en raison des effets de clustering.

Comparaison et suggestions

Si la table comporte peu de valeurs manquantes pour la colonne ID et que l'index de clé primaire est en place, La deuxième méthode (méthode basée sur l'index) est le meilleur choix car elle offre la meilleure vitesse et sexe de précision.

Pour les tableaux avec de nombreuses valeurs manquantes, veuillez considérer la Méthode 3 (CTE récursive), qui peut gérer efficacement les valeurs manquantes.

La première méthode (random() et limit) a des performances inférieures et doit être utilisée avec des tables plus petites.

Méthode 4(TABLESAMPLE SYSTEM) est rapide, mais pas aussi précise que les autres méthodes. Il peut être utilisé pour faire des estimations rapides sur de grands tableaux.

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!