如何在 PostgreSQL 中有效率地選擇隨機行？

PostgreSQL高效隨機行選擇方法

在PostgreSQL中選擇隨機行，最佳方法取決於表格的大小、可用索引以及所需的隨機性等級。

對於擁有5億行且包含數值ID欄位（例如，id）的超大型表：

• 最快方法：

  • 使用CTE和random()函數在ID空間內產生隨機ID。
  • 使用id列將產生的ID與表格連接。
  • 過濾掉重複項並移除多餘的ID。

<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>

• 改良方法：

  • 使用遞歸CTE (random_pick) 消除ID空間中的任何間隙。
  • 合併遞迴結果以消除重複項。
  • 應用外部LIMIT以滿足限制條件。

<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>

• 通用函數：

  • 將上述查詢包裝到一個函數中，以便可以對任何具有唯一整數列的表重複使用它們。

<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>

對於不需要精確隨機性或重複呼叫的場景：

• 物化視圖：

  • 建立一個物化視圖來儲存近似隨機選擇的行。
  • 定期刷新物化視圖。

• TABLESAMPLE SYSTEM (n)：

  • 在PostgreSQL 9.5中引入，TABLESAMPLE SYSTEM (n)提供了一種快速且非精確的隨機抽樣方法。
  • n參數表示要抽樣的表格百分比。

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

其他注意事項：

• 為獲得最佳效能，請在ID欄位上使用索引。
• PostgreSQL中的random()函數不是密碼學安全的。
• 建議的方法為大多數實際用例提供了高度的隨機性。

以上是如何在 PostgreSQL 中有效率地選擇隨機行？的詳細內容。更多資訊請關注PHP中文網其他相關文章！