最佳方式来选择随机行PostgreSQL

概述我想在PostgreSQL中随机选择行，我试过这样： select * from table where random() < 0.01; 但一些其他推荐这： select * from table order by random() limit 1000; 我有一个非常大的表有500万行，我想要快。 哪种方法更好？有什么区别？什么是选择随机行的最佳方法？ 根据您的规格(加上评论中的其他信息)， 我想在Postgresql中随机选择行，我试过这样：

select * from table where random() < 0.01;

但一些其他推荐这：

select * from table order by random() limit 1000;

我有一个非常大的表有500万行，我想要快。

哪种方法更好？有什么区别？什么是选择随机行的最佳方法？

根据您的规格(加上评论中的其他信息)，

>您有一个数字ID列(整数)，只有很少(或适度少)的间隙。
>显然没有或几乎没有写 *** 作。
>您的ID列必须编入索引！主键很好地服务。

下面的查询不需要大表的顺序扫描，只需要索引扫描。

首先，获取主查询的估计值：

SELECT count(*) AS ct              -- optional,min(ID)  AS min_ID,max(ID)  AS max_ID,max(ID) - min(ID) AS ID_spanFROM   big;

唯一可能昂贵的部分是count(*)(对于巨大的表)。给定上面的规格，你不需要它。估计会做得很好，几乎没有成本(detailed explanation here)：

SELECT reltuples AS ct FROM pg_class WHERE oID = 'schema_name.big'::regclass;

只要ct不小于ID_span，查询将优于其他方法。

WITH params AS (    SELECT 1       AS min_ID           -- minimum ID <= current min ID,5100000 AS ID_span          -- rounded up. (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                        -- trim duplicates    ) rJOIN   big USING (ID)liMIT  1000;                           -- trim surplus

>在ID空间中生成随机数。您有“少许差距”，因此要添加10％(足以容易地覆盖空白)到要检索的行数。
>每个ID可以偶然被挑选多次(虽然不大可能与一个大ID空间)，所以分组生成的数字(或使用disTINCT)。
>将IDs添加到大表。这应该是非常快的索引到位。
>最后修整没有被抄袭和缺口吃掉的剩余IDs。每一行都有一个完全平等的机会被挑选。

短版本

您可以简化此查询。上述查询中的CTE仅用于教育目的：

SELECT *FROM  (    SELECT disTINCT 1 + trunc(random() * 5100000)::integer AS ID    FROM   generate_serIEs(1,1100) g    ) rJOIN   big USING (ID)liMIT  1000;

用rCTE精简

特别是如果你不太确定差距和估计。

WITH RECURSIVE random_pick AS (   SELECT *   FROM  (      SELECT 1 + trunc(random() * 5100000)::int AS ID      FROM   generate_serIEs(1,1030)  -- 1000 + few percent - adapt to your needs      liMIT  1030                      -- hint for query planner      ) r   JOIN   big b USING (ID)             -- eliminate miss   UNION                               -- eliminate dupe   SELECT b.*   FROM  (      SELECT 1 + trunc(random() * 5100000)::int AS ID      FROM   random_pick r             -- plus 3 percent - adapt to your needs      liMIT  999                       -- less than 1000,hint for query planner      ) r   JOIN   big b USING (ID)             -- eliminate miss   )SELECT *FROM   random_pickliMIT  1000;  -- actual limit

我们可以在基本查询中使用更小的剩余。如果有太多的间隙，所以我们在第一次迭代中找不到足够的行，rCTE继续使用递归项迭代。我们在ID空间中仍然需要相对较少的间隙，或者递归在达到限制之前可能会运行干涸 – 或者我们必须从足够大的缓冲区开始，这样就不能优化性能。

重复项由rCTE中的UNION消除。

外部liMIT使得CTE在我们有足够的行时立即停止。

仔细草拟此查询以使用可用索引，实际生成随机行，并且不停止，直到我们达到限制(除非递归运行干)。这里有一些陷阱，如果你要重写它。

换行函数

重复使用不同参数：

CREATE OR REPLACE FUNCTION f_random_sample(_limit int = 1000,_gaps real = 1.03)  RETURNS SetoF big AS$func$DECLARE   _surplus  int := _limit * _gaps;   _estimate int := (           -- get current estimate from system      SELECT c.reltuples * _gaps      FROM   pg_class c      WHERE  c.oID = 'big'::regclass);BEGIN   RETURN query   WITH RECURSIVE random_pick AS (      SELECT *      FROM  (         SELECT 1 + trunc(random() * _estimate)::int         FROM   generate_serIEs(1,_surplus) g         liMIT  _surplus           -- hint for query planner         ) r (ID)      JOIN   big USING (ID)        -- eliminate misses      UNION                        -- eliminate dupes      SELECT *      FROM  (         SELECT 1 + trunc(random() * _estimate)::int         FROM   random_pick        -- just to make it recursive         liMIT  _limit             -- hint for query planner         ) r (ID)      JOIN   big USING (ID)        -- eliminate misses   )   SELECT *   FROM   random_pick   liMIT  _limit;END$func$  LANGUAGE plpgsql VolATILE ROWS 1000;

呼叫：

SELECT * FROM f_random_sample();SELECT * FROM f_random_sample(500,1.05);

你甚至可以使这个泛型工作于任何表：取PK列和表的名称作为多态类型，并使用EXECUTE …但这超出了这个问题的范围。看到：

> Refactor a PL/pgSQL function to return the output of various SELECT queries

可能的选择

如果你的要求允许相同的集合为重复的电话(我们正在谈论重复的电话)我会考虑一个物化视图。执行上面的查询一次，并将结果写入表。用户以减轻速度得到准随机选择。每隔一段时间或事件刷新您的随机选择。

Postgres 9.5引入了TABLESAMPLE SYSTEM (n)

它很快，但结果不是完全随机的。 The manual:

The SYstem method is significantly faster than the BERNOulli method
when small sampling percentages are specifIEd,but it may return a
less-random sample of the table as a result of clustering effects.

返回的行数可能会大不相同。对于我们的示例，要获得大约1000行，请尝试：

SELECT * FROM big tableSAMPLE SYstem ((1000 * 100) / 5100000.0);

其中n是百分比。手册：

The BERNOulli and SYstem sampling methods each accept a single
argument which is the fraction of the table to sample,ex@R_404_4848@ as a
percentage between 0 and 100. This argument can be any real-valued Expression.

大胆强调我。

有关：

> Fast way to discover the row count of a table in PostgreSQL

或者安装附加模块tsm_system_rows以获得所请求的行数(如果有足够的话)，并允许更方便的语法：

SELECT * FROM big tableSAMPLE SYstem_ROWS(1000);

详情请参见Evan’s answer。

但这还不是完全随机的。

总结

以上是内存溢出为你收集整理的最佳方式来选择随机行PostgreSQL全部内容，希望文章能够帮你解决最佳方式来选择随机行PostgreSQL所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。