我想在PostgreSQL中随机选择行，我尝试了这个:
select * from table where random() < 0.01;
但也有人建议:
select * from table order by random() limit 1000;
我有一个非常大的表，有5亿行，我希望它是快速的。
哪种方法更好?有什么不同?选择随机行最好的方法是什么?
当前回答
select * from table order by random() limit 1000;
如果知道需要多少行，请检查tsm_system_rows。
tsm_system_rows
module provides the table sampling method SYSTEM_ROWS, which can be used in the TABLESAMPLE clause of a SELECT command. This table sampling method accepts a single integer argument that is the maximum number of rows to read. The resulting sample will always contain exactly that many rows, unless the table does not contain enough rows, in which case the whole table is selected. Like the built-in SYSTEM sampling method, SYSTEM_ROWS performs block-level sampling, so that the sample is not completely random but may be subject to clustering effects, especially if only a small number of rows are requested.
首先安装扩展
CREATE EXTENSION tsm_system_rows;
然后你的问题，
SELECT *
FROM table
TABLESAMPLE SYSTEM_ROWS(1000);
其他回答
添加一个名为r的列，类型为serial。指数r。
假设我们有20万行，我们将生成一个随机数n，其中0 < n <= 200000。
选择r > n的行，按ASC排序，选择最小的行。
代码:
select * from YOUR_TABLE
where r > (
select (
select reltuples::bigint AS estimate
from pg_class
where oid = 'public.YOUR_TABLE'::regclass) * random()
)
order by r asc limit(1);
代码是自解释的。中间的子查询用于快速估计来自https://stackoverflow.com/a/7945274/1271094的表行数。
在应用程序级别，如果n >为行数或需要选择多行，则需要再次执行该语句。
您可以通过使用来检查和比较两者的执行计划
EXPLAIN select * from table where random() < 0.01;
EXPLAIN select * from table order by random() limit 1000;
对一个大型表1的快速测试表明，ORDER BY首先对整个表进行排序，然后选择前1000个项。对一个大表进行排序不仅要读取该表，还包括读取和写入临时文件。where random() < 0.1只扫描整个表一次。
对于大型表，这可能不是您想要的，因为即使是一次完整的表扫描也可能需要很长时间。
第三个建议是
select * from table where random() < 0.01 limit 1000;
这个方法在找到1000行后立即停止表扫描，因此返回得更快。当然，这将降低随机性，但也许这对于你来说已经足够好了。
编辑:除了这些考虑因素之外，你可以看看已经问过的问题。使用查询[postgresql]随机返回一些结果。
快速随机行选择在Postgres 如何从postgreSQL表检索随机数据行? Postgres:从表中获取随机条目-太慢
depez的一篇链接文章概述了更多的方法:
http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/
1“大”是指“完整的表将不适合内存”。
我知道我有点晚了，但我刚刚找到了这个叫做pg_sample的很棒的工具:
pg_sample -从较大的PostgreSQL数据库中提取一个小的样本数据集，同时保持引用完整性。
我尝试了一个350M行的数据库，它真的很快，不知道随机性。
./pg_sample --limit="small_table = *" --limit="large_table = 100000" -U postgres source_db | psql -U postgres target_db
快速的方式
根据您的规格(以及评论中的其他信息)，
您有一个数字ID列(整数)，只有很少(或中等数量)的间隙。 显然没有或很少有写操作。 您的ID列必须被索引!主键很有用。
下面的查询不需要对大表进行顺序扫描，只需要进行索引扫描。
首先，获取主查询的估计值:
SELECT count(*) AS ct -- optional
, min(id) AS min_id
, max(id) AS max_id
, max(id) - min(id) AS id_span
FROM big;
唯一可能昂贵的部分是count(*)(对于巨大的表)。考虑到上述规格，您不需要它。用一个估计来代替完整的计数就可以了，几乎不需要任何成本:
SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint AS ct
FROM pg_class
WHERE oid = 'big'::regclass; -- your table name
详细解释:
在PostgreSQL中快速发现表的行数
只要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
) r
JOIN big USING (id)
LIMIT 1000; -- trim surplus
在id空间中生成随机数。您有“很少的空白”，因此将10%(足以轻松覆盖空白)添加到要检索的行数。 每个id都可以随机选择多次(尽管对于大id空格来说不太可能)，因此将生成的数字分组(或使用DISTINCT)。 将id连接到大表中。这应该是非常快的索引到位。 最后，修剪多余的id，没有被dupes和gap吃掉。每一行都有完全相等的机会被选中。
短的版本
您可以简化这个查询。上面查询中的CTE仅用于教育目的:
SELECT *
FROM (
SELECT DISTINCT 1 + trunc(random() * 5100000)::integer AS id
FROM generate_series(1, 1100) g
) r
JOIN 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
)
TABLE random_pick
LIMIT 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
LANGUAGE plpgsql VOLATILE ROWS 1000 AS
$func$
DECLARE
_surplus int := _limit * _gaps;
_estimate int := ( -- get current estimate from system
SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint
FROM pg_class
WHERE 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
)
TABLE random_pick
LIMIT _limit;
END
$func$;
电话:
SELECT * FROM f_random_sample();
SELECT * FROM f_random_sample(500, 1.05);
泛型函数
我们可以让这个泛型适用于任何具有唯一整数列的表(通常是PK):将表作为多态类型传递，并(可选地)传递PK列的名称，并使用EXECUTE:
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
-- safe syntax with schema & quotes where needed
_tbl text := pg_typeof(_tbl_type)::text;
_estimate int := (SELECT (reltuples / relpages
* (pg_relation_size(oid) / 8192))::bigint
FROM pg_class -- get current estimate from system
WHERE oid = _tbl::regclass);
BEGIN
RETURN QUERY EXECUTE format(
$$
WITH RECURSIVE random_pick AS (
SELECT *
FROM (
SELECT 1 + trunc(random() * $1)::int
FROM generate_series(1, $2) g
LIMIT $2 -- hint for query planner
) r(%2$I)
JOIN %1$s USING (%2$I) -- eliminate misses
UNION -- eliminate dupes
SELECT *
FROM (
SELECT 1 + trunc(random() * $1)::int
FROM random_pick -- just to make it recursive
LIMIT $3 -- hint for query planner
) r(%2$I)
JOIN %1$s USING (%2$I) -- eliminate misses
)
TABLE random_pick
LIMIT $3;
$$
, _tbl, _id
)
USING _estimate -- $1
, (_limit * _gaps)::int -- $2 ("surplus")
, _limit -- $3
;
END
$func$;
调用默认值(重要!):
SELECT * FROM f_random_sample(null::big); --!
或者更具体地说:
SELECT * FROM f_random_sample(null::"my_TABLE", 'oDD ID', 666, 1.15);
性能与静态版本基本相同。
相关:
重构一个PL/pgSQL函数以返回各种SELECT查询的输出-章节“各种完整的表类型” 从PostgreSQL函数返回SETOF行 Format()用于EXECUTE? 在触发器函数中插入动态表名
这对于SQL注入是安全的。看到的:
表名作为PostgreSQL函数参数 Postgres函数中的SQL注入vs准备好的查询
可能的替代方法
如果你的需求允许重复调用的相同集(我们说的是重复调用)考虑一个物化视图。执行上述查询一次，并将结果写入一个表。用户以闪电般的速度获得准随机选择。每隔一段时间或你选择的事件刷新你的随机选择。
Postgres 9.5引入了TABLESAMPLE系统(n)
其中n是百分比。手册:
伯努利和系统抽样方法各接受一个单一 参数，它是要采样的表的分数，表示为a 0到100之间的百分比。这个参数可以是任何实值表达式。
大胆强调我的。它非常快，但结果不是完全随机的。再看一下手册:
SYSTEM方法明显比BERNOULLI方法快 当指定小的抽样百分比时，但它可能返回一个 由于聚类效应，表中样本的随机程度较低。
返回的行数变化很大。在我们的例子中，要获取大约1000行:
SELECT * FROM big TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);
相关:
在PostgreSQL中快速发现表的行数
或者安装额外的模块tsm_system_rows，以准确获取所请求的行数(如果有足够的行)，并允许更方便的语法:
SELECT * FROM big TABLESAMPLE SYSTEM_ROWS(1000);
详情见埃文的回答。
但这仍然不是完全随机的。
从PostgreSQL 9.5开始，有一个新的语法专门用于从表中获取随机元素:
SELECT * FROM mytable TABLESAMPLE SYSTEM (5);
这个例子将给出mytable中5%的元素。
有关文档的更多说明:http://www.postgresql.org/docs/current/static/sql-select.html
