快速從 PostgreSQL 表中獲取真正隨機的行
我一直這樣做：
SELECT column FROM table ORDER BY random() LIMIT 1;對於大表來說，這是令人難以忍受的、難以置信的緩慢，以至於在實踐中毫無用處。這就是為什麼我開始尋找更有效的方法。人家推薦：
SELECT column FROM table TABLESAMPLE BERNOULLI(1) LIMIT 1;雖然速度很快，但它也提供了毫無價值的隨機性。它似乎總是選擇相同的該死記錄，所以這也毫無價值。
我也試過：
SELECT column FROM table TABLESAMPLE BERNOULLI(100) LIMIT 1;它提供了更糟糕的隨機性。它每次都選擇相同的幾條記錄。這是完全沒有價值的。我需要真正的隨機性。
為什麼只選擇隨機記錄顯然如此困難？為什麼它必須抓取每條記錄然後對它們進行排序（在第一種情況下）？為什麼“TABLESAMPLE”版本總是抓取相同的愚蠢記錄？為什麼它們不是隨機的？當它一遍又一遍地挑選相同的幾條記錄時，誰會想要使用這種“BERNOULLI”的東西？我不敢相信，經過這麼多年，我仍然在詢問是否要獲取隨機記錄……這是最基本的可能查詢之一。
用於從 PG 中的表中獲取隨機記錄的實際命令是什麼，它的速度不會太慢以至於對於一個體面大小的表需要整整幾秒鐘？
有趣的問題 - 有很多可能性/排列（這個答案已經過廣泛修改）。
基本上，這個問題可以分為兩個主要流。
- 單個隨機記錄
- 多個隨機記錄（不在問題中 - 請參閱底部的參考和討論）
對此進行研究後，我相信對單條記錄問題的最快解決方案是通過Evan Carroll 的回答
tsm_system_rows提供的對 PostgreSQL 的擴展。如果您使用的是二進制發行版，我不確定，但我認為這些
contrib模組（其中tsm_system_rows一個）預設可用 - 至少它們適用於我用於測試的EnterpriseDB WindowsWindows版本（見下文） . 我的主要測試是在Linux(make world和make install-world) 上從原始碼編譯的 12.1 上完成的。我覺得它最適合單記錄案例的原因是，關於這個擴展，唯一提到的問題是：
SYSTEM_ROWS 與內置的 SYSTEM 採樣方法一樣，執行塊級採樣，因此樣本不是完全隨機的，而是可能會受到分群效應的影響，尤其是在僅請求少量行的情況下。
但是，由於您只對選擇 1 行感興趣，因此塊級分群效果應該不是問題。來自 2ndQuadrant 的這篇文章說明了為什麼對於一個記錄的樣本來說這不應該是一個問題！對於小子集來說這是一個主要問題（見文章結尾）——或者如果您希望從一個大表中生成大量隨機記錄樣本（同樣，請參見下面的討論
tsm_system_rows）tsm_system_time。然後我創建並填充了一個這樣的表：
CREATE TABLE rand AS SELECT generate_series(1, 100000000) AS seq, MD5(random()::text);所以，我現在有一個包含 100,000,000（1 億）條記錄的表。然後我添加了一個
PRIMARY KEY：ALTER TABLE rand ADD PRIMARY KEY (seq);所以，現在
SELECT隨機記錄：SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);請注意，我使用了一個稍微修改過的命令，以便我可以“看到”隨機性 - 我還設置了
\timing命令，以便我可以獲得經驗測量值。我使用了這個
LENGTH()函式，以便我可以很容易地感知PRIMARY KEY返回的整數的大小。以下是返回的記錄範例：test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1); length | ?column? | md5 --------+-----------+---------------------------------- 6 | 970749.61 | bf18719016ff4f5d16ed54c5f4679e20 (1 row) Time: 30.606 ms test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1); length | ?column? | md5 --------+-----------+---------------------------------- 6 | 512101.21 | d27fbeea30b79d3e4eacdfea7a62b8ac (1 row) Time: 0.556 ms test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1); length | ?column? | md5 --------+-----------+---------------------------------- 6 | 666476.41 | c7c0c34d59229bdc42d91d0d4d9d1403 (1 row) Time: 0.650 ms test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1); length | ?column? | md5 --------+----------+---------------------------------- 5 | 49152.01 | 0a2ff4da00a2b81697e7e465bd67d85c (1 row) Time: 0.593 ms test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1); length | ?column? | md5 --------+----------+---------------------------------- 5 | 18061.21 | ee46adc96a6f8264a5c6614f8463667d (1 row) Time: 0.616 ms test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1); length | ?column? | md5 --------+-----------+---------------------------------- 6 | 691962.01 | 4bac0d051490c47716f860f8afb8b24a (1 row) Time: 0.743 ms因此，如您所見，該
LENGTH()函式大部分時間返回 6 - 這是意料之中的，因為大多數記錄將在 10,000,000 到 100,000,000 之間，但有幾個顯示值為 5（也見過 3 和4 - 數據未顯示）。現在，注意時間。第一個是 30 毫秒 (ms)，但其餘的是亞毫秒（大約 0.6 - 0.7 毫秒）。大多數隨機樣本在這個亞毫秒範圍內返回，但是，在 25 - 30 毫秒內返回結果（平均 3 分之一或 4 個）。
有時，這種多毫秒的結果可能會連續出現兩次甚至三次，但正如我所說，大多數結果（大約 66 - 75%）都是亞毫秒級的。我所看到的解決方案的響應時間都沒有超過 75 毫秒**。**
在我的研究中，我還發現了
tsm_system_time類似於tsm_system_rows. 現在，我還對這個擴展進行瞭如下基準測試：SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_TIME(0.001) LIMIT 1;請注意，時間量是毫秒的 1/1000，即微秒 - 如果輸入任何低於此的數字，則不會返回任何記錄。然而，有趣的是，即使是這個微小的量子也總是返回 120 行。
非常為什麼它是 120 比我的工資等級高一點 - PostgreSQL 頁面大小是 8192（預設值）
test=# SELECT current_setting('block_size'); current_setting ----------------- 8192 (1 row)並且
file system block size是 4096[pol@UNKNOWN inst]$blockdev --getbsz /dev/mapper/fedora_localhost--live-home 4096一條記錄應該是（1
INTEGER（4 字節）+ 1UUID（16 字節））（= 20 字節）+seq欄位上的索引（大小？）。4096/120 = 34.1333… - 我幾乎不認為這個表的每個索引條目需要 14 個字節 - 所以 120 來自哪裡，我不確定。我不太確定該
LIMIT子句是否總是返回頁面或塊的第一個元組——從而在等式中引入了一個非隨機元素。查詢的性能
tsm_system_time與擴展的性能相同（AFAICS - 數據未顯示）tsm_system_rows。關於不確定這些擴展如何選擇它們的第一條記錄是否引入了非隨機元素的同樣警告也適用於tsm_system_rows查詢。請參閱下面對這兩種方法的（所謂）隨機性的討論和基準測試。關於性能，僅供參考，我使用的是 Dell Studio 1557，配備 1TB 硬碟（旋轉鏽蝕）和執行 Fedora 31 的 8GB DDR3 RAM）。這是一台10年的機器！
我也在一台帶有 SSD 的機器（Packard Bell、EasyNote TM - 也有 10 年曆史，執行 Windows 2019 Server 的 8GB DDR3 RAM）上做了同樣的事情（SSD 無論如何都不是頂級產品！）和響應時間通常（奇怪的是）稍高（~ 1.3 ms），但尖峰較少，並且這些值較低（~ 5 - 7 ms）。
2019 Server 很可能會在後台執行很多東西 - 但如果您有一台配備不錯 SSD 的現代筆記型電腦，那麼您當然可以期待亞毫秒級的響應時間！
所有測試均使用 PostgreSQL 12.1 執行。
為了檢查這兩種方法的真正“隨機性”，我創建了下表：
CREATE TABLE rand_samp ( seq INT, md5 TEXT );然後執行（每次 3 次）：
DO $$ DECLARE i RECORD; BEGIN FOR i IN 1..10000 LOOP INSERT INTO rand_samp (seq, md5) SELECT seq, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1); END LOOP; END; $$ ;並且還使用（在上述函式的內部循環中）
SELECT seq, md5 FROM rand TABLESAMPLE SYSTEM_TIME(0.001) LIMIT 1;然後每次執行後，我都會查詢我的
rand_samp表：SELECT seq, COUNT(seq) FROM rand_samp GROUP BY seq HAVING COUNT(seq) > 1;並得到以下計數：
對於
TABLESAMPLE SYSTEM_ROWS，我得到了 258、63、44 個騙子，計數均為2。對於，我得到了TABLESAMPLE SYSTEM_TIME46、54 和 62，同樣計數為 2。現在，我的統計數據有點生疏了，但是從 1 億條記錄的表的隨機樣本中，從 10,000 條的樣本中（表中記錄數的萬分之一
rand），我希望有幾個重複- 也許不時，但與我獲得的數字不同。此外，如果存在真正的隨機性，我也期望（少數）3 和 4。我用 100,000 次執行進行了兩次測試，第一次執行
TABLESAMPLE SYSTEM_ROWS獲得了 5540 個重複（~200 個有 3 個重複，6 個有 4 個重複），第二次執行了 5465 個重複（~200 個有 3，6 個有 4 個）。然而，最有趣的查詢是：SELECT COUNT(s.seq) FROM rand_samp s WHERE s.seq IN (SELECT sb.seq FROM rand_samp_bis sb);我在其中比較了兩次執行中的 100,000 的騙局 - 答案是高達 11,250 (> 10%) 是相同的 - 對於千分之一 (1/1000) 的樣本來說，這是非常低的機會！
結果 100,000 次執行
SYSTEM_TIME- 5467 次重複，215 次 3 次，9 次 4 次在第一組，5472、210 (3) 和 12 (4) 次與第二組。匹配記錄數為 11,328（再次 > 10%）。顯然（很多）非隨機行為正在發生。我將把它留給 OP 來決定速度/隨機權衡是否值得！
其他答案的基準。
我決定對其他提議的解決方案進行基準測試——使用上面的 1 億條記錄表。我執行了所有測試 5 次 - 在任何一系列測試開始時忽略任何異常值以消除記憶體/任何影響。所有異常值均高於下面報告的值。
我正在使用帶有 HDD 的機器 - 稍後將使用 SSD 機器進行測試。報告的
.mmm意思是毫秒 - 除了我自己的答案之外，對任何答案都不重要。丹尼爾真理的回答：
SELECT * FROM (SELECT seq FROM rand TABLESAMPLE BERNOULLI(1)) AS s ORDER BY RANDOM() LIMIT 1;跑了 5 次 - 所有時間都超過一分鐘 - 通常是 01:00.mmm（1 在 01:05.mmm）。
典型執行：
test=# SELECT * FROM (SELECT seq FROM rand TABLESAMPLE BERNOULLI(1)) AS s ORDER BY RANDOM() LIMIT 1; seq --------- 9529212 (1 row) Time: 60789.988 ms (01:00.790)斯瓦夫的回答：
SELECT md5 FROM rand OFFSET ( SELECT floor(random() * (SELECT count(seq) from rand))::int ) limit 1;跑了 5 次 - 所有時間都超過一分鐘 - 從 01:03 到 01:29
典型執行：
test=# SELECT md5 FROM rand OFFSET ( SELECT floor(random() * (SELECT count(seq) from rand))::int ) limit 1; md5 ---------------------------------- 8004dfdfbaa9ac94243c33e9753e1f77 (1 row) Time: 68558.096 ms (01:08.558)Colin ’t Hart的回答：
select * from rand where seq >= ( select random()*(max(seq)-min(seq)) + min(seq) from rand ) order by seq limit 1;跑了 5 次 - 時間在 00:06.mmm 和 00:14.mmm 之間變化（最好的休息！）
典型執行：
test=# select * from rand where seq >= ( select random()*(max(seq)-min(seq)) + min(seq) from rand ) order by seq limit 1; seq | md5 ----------+---------------------------------- 29277339 | 2b27c594f65659c832f8a609c8cf8e78 (1 row) Time: 6944.771 ms (00:06.945)Colin ’t Hart的第二個答案（由我改編）：
WITH min_max AS MATERIALIZED -- or NOT, doesn't appear to make a difference ( SELECT MIN(seq) AS min_s, MAX(seq) AS max_s, (MAX(seq) - MIN(seq)) - MIN(seq) AS diff_s FROM rand ), other AS MATERIALIZED ( SELECT FLOOR(RANDOM() * (SELECT diff_s FROM min_max))::INT AS seq_val ) SELECT seq, md5 FROM rand WHERE seq = (SELECT seq_val FROM other);響應時間在 ~ 30 - 45 毫秒之間，在這些時間的兩邊都有奇數的異常值 - 它甚至可以不時下降到 1.xxx 毫秒。我真正能說的是，它似乎比
SYSTEM_TIME和SYSTEM_ROWS方法中的任何一個都更一致。然而，這種方法存在一個主要問題。如果一個人為隨機性選擇的基礎欄位是稀疏的，那麼這個方法不會一直返回一個值——這可能會或可能不會被 OP 接受？您可以執行以下操作（查詢結束）：
SELECT seq, md5 FROM rand WHERE seq >= (SELECT seq_val FROM other) LIMIT 1;（注
>=和LIMIT 1）。這可能非常有效（1.xxx 毫秒），但似乎變化不僅僅是seq =...公式 - 但是一旦記憶體似乎被預熱，它通常會給出約 1.5 毫秒的響應時間。此解決方案的另一個優點是它不需要任何特殊擴展，這取決於上下文（不允許顧問安裝“特殊”工具、DBA 規則……）可能不可用。
關於上述解決方案的一個非常奇怪
::INT的事情是，如果CAST 被刪除，查詢需要大約 1 分鐘。即使FLOOR函式應該返回一個INTEGER. 我只是通過執行才發現這是一個問題EXPLAIN (ANALYZE BUFFERS)。使用 ::INT
CTE other -> Result (cost=0.02..0.04 rows=1 width=4) (actual time=38.906..38.907 rows=1 loops=1) Buffers: shared hit=1 read=9 InitPlan 4 (returns $3) -> CTE Scan on min_max (cost=0.00..0.02 rows=1 width=4) (actual time=38.900..38.902 rows=1 loops=1) Buffers: shared hit=1 read=9 InitPlan 6 (returns $5) -> CTE Scan on other (cost=0.00..0.02 rows=1 width=4) (actual time=38.909..38.910 rows=1 loops=1) Buffers: shared hit=1 read=9 Planning Time: 0.329 ms Execution Time: 68.449 ms (31 rows) Time: 99.708 ms test=#沒有 ::INT
CTE other -> Result (cost=0.02..0.04 rows=1 width=8) (actual time=0.082..0.082 rows=1 loops=1) Buffers: shared hit=10 InitPlan 4 (returns $3) -> CTE Scan on min_max (cost=0.00..0.02 rows=1 width=4) (actual time=0.076..0.077 rows=1 loops=1) Buffers: shared hit=10 InitPlan 6 (returns $5) -> CTE Scan on other (cost=0.00..0.02 rows=1 width=8) (actual time=0.085..0.085 rows=1 loops=1) Buffers: shared hit=10 -> Parallel Seq Scan on rand (cost=0.00..1458334.00 rows=208333 width=37) (actual time=52644.672..60025.906 rows=0 loops=3) Filter: ((seq)::double precision = $5) Rows Removed by Filter: 33333333 Buffers: shared hit=14469 read=818865 Planning Time: 0.378 ms Execution Time: 60259.401 ms (37 rows) Time: 60289.827 ms (01:00.290) test=#注意（沒有
::INT）-> Parallel Seq Scan on rand (cost=0.00..1458334.00 rows=208333 width=37) (actual time=52644.672..60025.906 rows=0 loops=3) Filter: ((seq)::double precision = $5)並行 Seq 掃描（成本高），過濾 (seq)::double
（為什麼要加倍？？）。
和
Buffers: shared hit=14469 read=818865與（與
::INT）相比Buffers: shared hit=1 read=9最後，我自己的答案再次（同一台機器，時間和記憶體）：
（鑑於上面執行的基準測試，這現在是多餘的）。
再次執行我自己的基準測試 15 次 - 通常時間是亞毫秒，偶爾（大約 3/4 中的 1 次）執行大約需要 15 次。25 毫秒。
典型執行：
test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1); length | ?column? | md5 --------+-----------+---------------------------------- 6 | 401578.81 | 30ff7ecfedea088dab75932f0b1ea872 (1 row) Time: 0.708 ms因此，看起來我的解決方案最糟糕的時間比其他最快的答案（Colin ’t Hart）快約 200 倍。
我的分析是沒有完美的解決方案，但最好的解決方案似乎是對 Colin ’t Hart 解決方案的改編。
最後，下面顯示了與使用此解決方案處理多條記錄相關的問題的圖形展示 - 抽取 25 條記錄的樣本（執行多次 - 顯示了典型執行）。
tsm_system_rows方法將產生 25 條連續記錄。這可能適用於某些目的，其中隨機樣本是許多連續記錄這一事實不成問題，但絕對值得牢記。
test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(25); length | ?column? | md5 --------+-----------+---------------------------------- 6 | 763140.01 | 7e84b36ab30d3d2038ebd832c241b54d 6 | 763140.02 | a976e258f6047df18e8ed0559ff48c36 -- -- SEQUENTIAL values of seq! -- 6 | 763140.23 | ad4f1b4362187d6a300aaa9aaef30170 6 | 763140.24 | 0c7fcc4f07d27fbcece68b2503f28d88 6 | 763140.25 | 64d4507b18b5481a724d8a5bb6ac59c8 (25 rows)時間：29.348 毫秒
在該方法的情況下也存在類似的情況
SYSTEM_TIME。如上所述，即使最短時間為 1μs，它也提供 120 條記錄。與 一樣SYSTEM_ROWS，這些給出 的順序值PRIMARY KEY。test=# SELECT seq, md5 FROM rand TABLESAMPLE SYSTEM_TIME(0.001);返回：
seq | md5 ----------+---------------------------------- 42392881 | e92f15cba600f0c7aa16db98c0183828 42392882 | 93db51ea870e15202144d11810c8f40c 42392883 | 7357bf0cf1fa23ab726e642832bb87b0 42392884 | 1f5ce45fb17c8ba19b391f9b9c835242 42392885 | f9922b502d4fd9ee84a904ac44d4e560 ... ... 115 sequential values snipped for brevity!我們的姊妹站點 StackOverflow 處理了這個問題
here。（再次）Erwin Brandstetterhere和 Evan Carroll提供了很好的答案here。整個執行緒值得詳細閱讀 - 因為random（單調遞增/遞減，Pseudorandom number generators……）和sampling（有或沒有替換……）有不同的定義。
你的錯誤是總是取樣本的第一行。
取一個隨機行代替：
SELECT * FROM (SELECT column FROM table TABLESAMPLE BERNOULLI(1)) AS s ORDER BY RANDOM() LIMIT 1;樣本的內容是隨機的，但樣本中的順序不是隨機的。由於採樣會進行表掃描，因此它傾向於按表的順序生成行。如果您查看一個新創建的、完全有序的表，這很明顯：
create table a as select * from generate_series(1,1000000) as i; select * from a tablesample bernoulli(1) limit 10; i ------ 248 394 463 557 686 918 933 1104 1124 1336 (10 rows)將 LIMIT 直接應用於樣本往往會產生較小的值，從表格的開頭按其在磁碟上的順序。LIMIT 1 的情況更糟。
現在將此與正確的方法進行比較：
select * from (select * from a tablesample bernoulli(1) ) s order by random() limit 10; i -------- 622931 864123 817263 729949 748422 127263 322338 900781 49371 616774