如何在 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中文網其他相關文章！