我想在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万行的非常大的表格,我希望它运行速度很快。
哪种方法更好?它们之间有什么差异?选择随机行的最佳方法是什么?
根据您的规格(在评论中提供附加信息),
以下查询不需要对大表进行顺序扫描,只需要进行索引扫描。
首先,获取主查询的估计值:
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(*)
(对于大表格而言)。 根据上述规范,您不需要它。 用于替换完整计数的估计足以胜任,几乎不需要费用:
唯一可能昂贵的部分是count(*)
(对于大表格而言)。 根据上述规范,您不需要它。 用于替换完整计数的估计足以胜任,几乎不需要费用:
SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint AS ct
FROM pg_class
WHERE oid = 'big'::regclass; -- your table name
详细说明:
只要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
。每行都有完全相同的机会被选择。
你可以简化此查询。上面的 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;
尤其是当你对空缺和估算不太确定时。
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
在基本查询中,我们可以使用较小的剩余部分。如果有太多的间隙,导致第一次迭代中找不到足够的行,则递归CTE将继续进行迭代。我们仍然需要相对较少的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列的名称,并使用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);
与静态版本性能相当。
相关信息:
这是安全的,不容易受到SQL注入攻击。请参见:
如果您的要求允许重复调用时相同的集合(我们在谈论重复调用),可以考虑使用MATERIALIZED VIEW
。执行上面的查询一次并将结果写入表中。用户以闪电般的速度获取准随机选择。在您选择的间隔或事件刷新随机选择。
TABLESAMPLE SYSTEM (n)
其中n
是一个百分比。手册:
伯努利和系统抽样方法各接受一个参数,即要抽样的表的分数,表示为0到100之间的百分比。该参数可以是任何实值表达式。
加粗强调是我的。这很快,但结果不完全随机。手册再次阐述:
当指定较小的抽样百分比时,
SYSTEM
方法比BERNOULLI
方法快得多,但由于聚类效应,它可能返回表的较不随机的样本。
返回的行数可能差别很大。对于我们的示例,要获取大约1000行:
SELECT * FROM big TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);
相关:
或者安装附加模块tsm_system_rows以确切地获取所请求的行数(如果有足够的行)并允许使用更方便的语法:
SELECT * FROM big TABLESAMPLE SYSTEM_ROWS(1000);
详细信息请参见Evan的答案。
但这仍然不是完全随机的。
JOIN bigtbl t
,它是 JOIN bigtbl AS t
的简写。t
是 bigtbl
的 表别名,其目的是缩短语法,但在这种特定情况下不需要。我在我的答案中简化了查询并添加了一个简单版本。 - Erwin Brandstetter这些方法都很慢,因为它们需要进行表扫描,以确保每一行被选择的机会完全相等:
select your_columns from your_table ORDER BY random()
select * from
(select distinct your_columns from your_table) table_alias
ORDER BY random()
select your_columns from your_table ORDER BY random() limit 1
N
:SELECT myid FROM mytable OFFSET floor(random() * N) LIMIT 1;
如果您的表非常庞大,则上述表扫描会成为一个绊脚石,需要长达5分钟才能完成。
为了更快地进行操作,您可以安排在幕后每晚进行表扫描重建,这将保证以O(1)
恒定时间速度进行完美随机选择,但在每晚重建表扫描期间,必须等待维护完成才能接收到另一行随机数据。
--Create a demo table with lots of random nonuniform data, big_data
--is your huge table you want to get random rows from in constant time.
drop table if exists big_data;
CREATE TABLE big_data (id serial unique, some_data text );
CREATE INDEX ON big_data (id);
--Fill it with a million rows which simulates your beautiful data:
INSERT INTO big_data (some_data) SELECT md5(random()::text) AS some_data
FROM generate_series(1,10000000);
--This delete statement puts holes in your index
--making it NONuniformly distributed
DELETE FROM big_data WHERE id IN (2, 4, 6, 7, 8);
--Do the nightly maintenance task on a schedule at 1AM.
drop table if exists big_data_mapper;
CREATE TABLE big_data_mapper (id serial, big_data_id int);
CREATE INDEX ON big_data_mapper (id);
CREATE INDEX ON big_data_mapper (big_data_id);
INSERT INTO big_data_mapper(big_data_id) SELECT id FROM big_data ORDER BY id;
--We have to use a function because the big_data_mapper might be out-of-date
--in between nightly tasks, so to solve the problem of a missing row,
--you try again until you succeed. In the event the big_data_mapper
--is broken, it tries 25 times then gives up and returns -1.
CREATE or replace FUNCTION get_random_big_data_id()
RETURNS int language plpgsql AS $$
declare
response int;
BEGIN
--Loop is required because big_data_mapper could be old
--Keep rolling the dice until you find one that hits.
for counter in 1..25 loop
SELECT big_data_id
FROM big_data_mapper OFFSET floor(random() * (
select max(id) biggest_value from big_data_mapper
)
) LIMIT 1 into response;
if response is not null then
return response;
end if;
end loop;
return -1;
END;
$$;
--get a random big_data id in constant time:
select get_random_big_data_id();
--Get 1 random row from big_data table in constant time:
select * from big_data where id in (
select get_random_big_data_id() from big_data limit 1
);
┌─────────┬──────────────────────────────────┐
│ id │ some_data │
├─────────┼──────────────────────────────────┤
│ 8732674 │ f8d75be30eff0a973923c413eaf57ac0 │
└─────────┴──────────────────────────────────┘
--Get 4 random rows from big_data in constant time:
select * from big_data where id in (
select get_random_big_data_id() from big_data limit 3
);
┌─────────┬──────────────────────────────────┐
│ id │ some_data │
├─────────┼──────────────────────────────────┤
│ 2722848 │ fab6a7d76d9637af89b155f2e614fc96 │
│ 8732674 │ f8d75be30eff0a973923c413eaf57ac0 │
│ 9475611 │ 36ac3eeb6b3e171cacd475e7f9dade56 │
└─────────┴──────────────────────────────────┘
--Test what happens when big_data_mapper stops receiving
--nightly reindexing.
delete from big_data_mapper where 1=1;
select get_random_big_data_id(); --It tries 25 times, and returns -1
--which means wait N minutes and try again.
改编自:https://www.gab.lc/articles/bigdata_postgresql_order_by_random
一个更简单的好用解决方案是在你的大表上创建一个名为big_data
.mapper_int
的新列,使其非空并具有唯一索引。每晚将该列重置为介于1和max(n)之间的唯一整数。要获取随机行,可以"选择一个介于0
和max(id)
之间的随机整数",然后返回 mapper_int 为该整数的行。如果不存在该 ID 的行,因为重新索引后行已发生变化,则选择另一行来获取随机行。如果向 big_data.mapper_int 添加一行,则将其填充为 max(id) + 1
如果您使用的是 PostgreSQL 版本> 9.5
,那么 TABLESAMPLE 可以在没有繁重的表扫描的情况下进行常数时间的随机抽样。
https://wiki.postgresql.org/wiki/TABLESAMPLE_Implementation
--Select 1 percent of rows from yourtable,
--display the first 100 rows, order by column a_column
select * from yourtable TABLESAMPLE SYSTEM (1)
order by a_column
limit 100;
drop table if exists random_nextpick_bigtable;
CREATE TABLE IF NOT EXISTS random_nextpick_bigtable as (
select your_columns from your_bigtable ORDER BY random()
)
在预定任务期间,获取选择需要5分钟,但是一旦完成后,可以立即获得一个完全随机的行:
select * from random_nextpick_bigtable limit 1;
delete from random_nextpick_bigtable where id = your_used_id;
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;
[postgresql] random
会返回很多结果。1 "large" 意味着 "完整的表格无法全部存入内存"。
random() < 0.02
然后洗牌那个列表,然后 limit 1000
!在几千行上进行排序会更加经济实惠(哈哈)。 - Donald Miner从PostgreSQL 9.5开始,有一种新的语法专门用于从表中获取随机元素:
SELECT * FROM mytable TABLESAMPLE SYSTEM (5);
该示例将从mytable
中随机选择5%的元素。
有关详细说明,请参阅文档:http://www.postgresql.org/docs/current/static/sql-select.html
TABLESAMPLE SYSTEM_ROWS(400)
获取 400 行随机样本。您需要启用内置的 tsm_system_rows
扩展程序来使用此语句。 - Mickaël Le Baillif带有ORDER BY语句的查询将会更慢。
select * from table where random() < 0.01;
按记录一个一个地进行筛选,并决定是否随机过滤。这将是O(N)
,因为它只需要检查每个记录一次。
select * from table order by random() limit 1000;
将对整个表进行排序,然后选择前1000条记录。除了任何幕后神秘操作外,此ORDER BY将是O(N * log N)
。
使用random() < 0.01
的缺点是你将获得可变数量的输出记录。
请注意,有一种比按随机顺序排序更好的数据集洗牌方法:Fisher-Yates随机置乱算法,其复杂度为O(N)
。不过在SQL中实现这种置乱算法似乎是相当具有挑战性的。
select * from table order by random() limit 1000;
如果您知道需要多少行,请查看tsm_system_rows
。
该模块提供了表采样方法SYSTEM_ROWS,可在SELECT命令的TABLESAMPLE子句中使用。
此表采样方法接受一个整数参数,即要读取的最大行数。结果样本将始终包含完全相同数量的行,除非表中不包含足够的行,在这种情况下会选择整个表。像内置的SYSTEM采样方法一样,SYSTEM_ROWS执行块级采样,因此样本并不完全随机,而是可能受到聚类效应的影响,特别是如果只请求少量行。
首先安装扩展程序
CREATE EXTENSION tsm_system_rows;
然后是您的查询,
SELECT *
FROM table
TABLESAMPLE SYSTEM_ROWS(1000);
tsm_system_rows
和 tsm_system_time
扩展进行了相当大量的基准测试。就我所看到的,它们除了绝对最小的随机行选择之外,几乎没有任何用处。如果您能快速查看并评论我的分析的有效性或无效性,我将不胜感激。 - VéraceSELECT * FROM "users" TABLESAMPLE SYSTEM_ROWS(n*10) ORDER BY RANDOM() LIMIT n;
其中 n
是你想要的行数。这样它将使用 tsm_system_rows
来获取比你需要的多10倍的行,仅对这些行进行随机排序,然后从中获取 n
行。相比于
SELECT * FROM "users" ORDER BY RANDOM() LIMIT n;
这种方式,速度仍然快得多,因为它不会对每一行进行排序。 - Starscream512tsm_system_rows
扩展来减轻聚集效应的解决方案有什么看法? - Starscream512这是一个对我有用的决定。我想这很容易理解和执行。
SELECT
field_1,
field_2,
field_2,
random() as ordering
FROM
big_table
WHERE
some_conditions
ORDER BY
ordering
LIMIT 1000;
ORDER BY random()
,虽然能够正常工作,但可能不是很高效。 - Anh Caocount
推导出的计算出来的 offset
。select * from table_name limit 1
offset floor(random() * (select count(*) from table_name));
我的经验告诉我一个教训:
offset floor(random() * N) limit 1
并不比 order by random() limit 1
更快。
我本以为使用 offset
方法会更快,因为它应该可以节省在Postgres中排序的时间。结果证明并非如此。
SELECT * FROM tableName ORDER BY random() LIMIT 1