如何在 PostgreSQL 中有效地选择随机行？

PostgreSQL随机行高效选择方法

PostgreSQL提供了多种高效选择随机行的方法。

方法一：使用Random()和Limit子句

此方法使用random()函数和LIMIT子句：

<code class="language-sql">SELECT *
FROM table
ORDER BY random()
LIMIT 1000;</code>

然而，对于大型表，此方法可能速度较慢，因为它需要进行全表扫描。

方法二：基于索引的方法

此方法利用主键索引优化查询：

<code class="language-sql">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;                          -- 去除多余项</code>

此方法比方法一更快，因为它使用索引扫描而不是全表扫描。

方法三：使用递归CTE

此方法使用递归公用表表达式(CTE)来处理ID列中的缺失值：

<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   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;  -- 实际限制</code>

方法四：使用TABLESAMPLE SYSTEM (n)

PostgreSQL 9.5引入了TABLESAMPLE SYSTEM (n)语法，其中n是0到100之间的百分比：

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

此方法速度很快，但由于集群效应，可能无法返回真正的随机样本。

比较和建议

如果表中ID列的缺失值很少并且主键索引已就位，则方法二(基于索引的方法)是最佳选择，因为它提供了最佳的速度和准确性。

对于缺失值较多的表，请考虑方法三(递归CTE)，它可以有效地处理缺失值。

方法一(random()和limit)性能较低，应与较小的表一起使用。

方法四(TABLESAMPLE SYSTEM)速度很快，但不如其他方法准确。它可用于对大型表进行快速估算。

以上是如何在 PostgreSQL 中有效地选择随机行？的详细内容。更多信息请关注PHP中文网其他相关文章！