blocks|key|211076|text|带有ORDER+BY的将是速度较慢的那个。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|211077|select+*+from+table+where+random()+<+0.01;逐条记录，并决定是否随机过滤它。这将是O(N)，因为它只需要检查每条记录一次。|offset|length|style|CODE|211078|select+*+from+table+order+by+random()+limit+1000;将对整个表进行排序，然后选择前1000个表。除了幕后的巫毒魔法，订单是O(N+*+log+N)。|211079|random()+<+0.01+one的缺点是您将获得数量可变的输出记录。|211080|211081|请注意，与随机排序相比，有一种更好的方法来处理一组数据:在O(N)中运行的The+Fisher-Yates+Shuffle。不过，在SQL中实现shuffle听起来像是一个相当大的挑战。|211082|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Fisher%25E2%2580%2593Yates_shuffle^0|0|0|16|1P|4|0|0|1D|2C|C|0|0|F|0|0|T|4|11|O|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@$D|Y|E|Z|F|G]|$D|10|E|11|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|12|8|@$D|13|E|14|F|G]|$D|15|E|16|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|17|8|@$D|18|E|19|F|G]]|9|@]|A|$]]|$1|L|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1B|8|@$D|1C|E|1D|F|G]]|9|@$D|1E|E|1F|1|1G]]|A|$]]|$1|O|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

The one with the ORDER BY is going to be the slower one.
<code>select * from table where random() &lt; 0.01;</code> goes record by record, and decides to randomly filter it or not. This is going to be <code>O(N)</code> because it only needs to check each record once.
<code>select * from table order by random() limit 1000;</code> is going to sort the entire table, then pick the first 1000. Aside from any voodoo magic behind the scenes, the order by is <code>O(N * log N)</code>.
The downside to the <code>random() &lt; 0.01</code> one is that you'll get a variable number of output records.
<hr />
Note, there is a better way to shuffling a set of data than sorting by random: <a href="http://en.wikipedia.org/wiki/Fisher%E2%80%93Yates_shuffle" rel="noreferrer">The Fisher-Yates Shuffle</a>, which runs in <code>O(N)</code>. Implementing the shuffle in SQL sounds like quite the challenge, though.

blocks|key|4000225|text|根据您的规范(以及评论中的其他信息)，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4000226|你有一个数字ID列(整数)，只有很少的(或相当少的)+gaps.|4000227|Obviously没有或有很少的写operations.|unordered-list-item|4000228|
|4000229|Your+ID列必须被索引！|4000230|！主键可以很好地提供服务。|4000231|4000232|4000233|下面的查询不需要顺序扫描大表，只需要一次索引扫描。|4000234|首先，获取主查询的估计值：|4000235|SELECT+count(*)+AS+ct++++++++++++++--+optional
+++++,+min(id)++AS+min_id
+++++,+max(id)++AS+max_id
+++++,+max(id)+-+min(id)+AS+id_span
FROM+++big;|code-block|syntax|javascript|4000236|唯一可能昂贵的部分是count(*)+(对于大型表)。根据上面的规范，你不需要它。评估将做得很好，几乎是免费的(detailed+explanation+here)：|offset|length|style|CODE|4000237|SELECT+reltuples+AS+ct+FROM+pg_class
WHERE+oid+=+'schema_name.big'::regclass;|4000238|只要ct不比id_span小很多，查询的性能就会优于其他方法。|4000239|WITH+params+AS+(
+++SELECT+1+++++++AS+min_id+++++++++++--+minimum+id+<=+current+min+id
++++++++,+5100000+AS+id_span++++++++++--+rounded+up.+(max_id+-+min_id+%2B+buffer)
++++)
SELECT+*
FROM++(
+++SELECT+p.min_id+%2B+trunc(random()+*+p.id_span)::integer+AS+id
+++FROM+++params+p
+++++++++,generate_series(1,+1100)+g++--+1000+%2B+buffer
+++GROUP++BY+1++++++++++++++++++++++++--+trim+duplicates
)+r
JOIN+++big+USING+(id)
LIMIT++1000;++++++++++++++++++++++++++--+trim+surplus|4000240|4000241|在id空间中生成随机数。您有“很少的间隙”，因此在要检索的行数中添加10%25(足以轻松地覆盖空白)。|4000242|4000243|4000244|每个数字可能会被随机选取多次(尽管对于较大的id空间来说不太可能)，因此对生成的id进行分组(或使用DISTINCT).|4000245|4000246|4000247|将id加入到大桌子中。有了索引，这应该会非常快。|4000248|4000249|4000250|最终修剪掉了没有被愚弄和缝隙吃掉的多余id。每一行都有一个完全相等的被挑选的机会。|4000251|4000252|简短版本|4000253|你可以对这个查询进行简化()。以上查询中的CTE仅用于教育目的：|4000254|SELECT+*
FROM++(
+++SELECT+DISTINCT+1+%2B+trunc(random()+*+5100000)::integer+AS+id
+++FROM+++generate_series(1,+1100)+g
+++)+r
JOIN+++big+USING+(id)
LIMIT++1000;|4000255|使用rCTE进行优化|4000256|特别是如果你对差距和估计不是很确定的话。|4000257|WITH+RECURSIVE+random_pick+AS+(
+++SELECT+*
+++FROM++(
++++++SELECT+1+%2B+trunc(random()+*+5100000)::int+AS+id
++++++FROM+++generate_series(1,+1030)++--+1000+%2B+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+%2B+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|4000258|我们可以在基本查询中使用较小的剩余。如果有太多的间隙，所以我们在第一次迭代中找不到足够的行，rCTE将继续使用递归项进行迭代。我们仍然需要ID空间中的相对较少的间隙，否则递归可能会在达到限制之前变得枯竭-或者我们必须从一个足够大的缓冲区开始，这违背了优化性能的目的。|BOLD|4000259|重复项由rCTE中的UNION消除。|4000260|一旦我们有足够的行数，外部的LIMIT就会使CTE停止。|4000261|这个查询是精心设计的，以使用可用的索引，生成实际的随机行，并且直到我们达到限制时才停止(除非递归枯竭)。如果你打算重写它，这里有很多陷阱。|4000262|Wrap函数|4000263|对于可变参数的重复使用：|4000264|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+c.reltuples+*+_gaps
++++++FROM+++pg_class+c
++++++WHERE++c.oid+=+'big'::regclass);
BEGIN
+++RETURN+QUERY
+++WITH+RECURSIVE+random_pick+AS+(
++++++SELECT+*
++++++FROM++(
+++++++++SELECT+1+%2B+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+%2B+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$;|4000265|呼叫：|4000266|SELECT+*+FROM+f_random_sample();
SELECT+*+FROM+f_random_sample(500,+1.05);|4000267|您甚至可以使此泛型适用于任何表:将PK列的名称和表作为多态类型，并使用EXECUTE+...但这超出了这个问题的范围。请参见：|4000268|4000269|Refactor+a+PL/pgSQL+function+to+return+the+output+of+various+SELECT+queries|4000270|4000271|可能的替代方案|4000272|如果您的需求允许对重复的调用使用相同的集(我们讨论的是重复调用)，我会考虑使用物化视图。执行上述查询一次，并将结果写到一个表中。用户以闪电速度获得准随机选择。在您选择的时间间隔或事件中刷新您的随机选择。|4000273|Postgres9.5引入了TABLESAMPLE+SYSTEM+(n)|4000274|其中n是百分比。The+manual:|4000275|4000276|+BERNOULLI和SYSTEM采样方法都接受单个参数，该参数是要采样的表的分数，表示为0到100之间的百分比。此参数可以是任何real-valued表达式。|blockquote|4000277|4000278|4000279|大胆强调我的观点。它非常快，但结果并不是完全随机的。再看一遍手册：|4000280|4000281|当指定较小的采样百分比时，SYSTEM方法比BERNOULLI方法快得多，但由于群集效应，它可能会返回较少随机的表样本。|4000282|4000283|4000284|返回的行数可以有很大的变化。对于我们的示例，要获取大约1000行：|4000285|SELECT+*+FROM+big+TABLESAMPLE+SYSTEM+((1000+*+100)+/+5100000.0);|4000286|相关信息：|4000287|4000288|Fast+way+to+discover+the+row+count+of+a+table+in+PostgreSQL|4000289|4000290|或安装附加模块以准确获取请求的行数(如果有足够的行数)，并允许使用更方便的语法：|4000291|SELECT+*+FROM+big+TABLESAMPLE+SYSTEM_ROWS(1000);|4000292|详情请参见Evan's+answer。|4000293|但这仍然不是完全随机的。|4000294|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/7943233/fast-way-to-discover-the-row-count-of-a-table/7945274#7945274|1|https://stackoverflow.com/questions/11740256/refactor-a-pl-pgsql-function-to-return-the-output-of-various-select-queries/11751557#11751557|2|https://www.postgresql.org/docs/current/sql-select.html#SQL-FROM|3|4|5|https://stackoverflow.com/a/41337788/939860^0|0|0|0|0|0|0|0|0|0|0|0|A|8|1K|P|0|0|0|2|2|6|7|0|0|0|1|2|0|0|0|14|2|1E|8|0|0|0|1|2|0|0|0|J|2|0|0|0|0|0|0|0|0|C|5|0|A|5|0|E|5|0|0|0|0|0|0|0|Z|7|0|0|0|23|1|0|0|0|J|2A|0|0|10|E|M|E|M|2|0|0|J|2|1|8|B|3|0|0|1|1D|1H|R|1|9|B|6|1T|4|0|0|0|0|X|0|0|0|1O|D|6|M|9|0|0|0|0|X|0|0|0|5|0|0|0|1N|0|1N|4|0|0|0|14|0|0|0|J|5|D|5|0|0|C|0^^$0|@$1|2|3|4|5|6|7|49|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|4A|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|4B|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|4C|8|@]|9|@]|A|$]]|$1|I|3|J|5|F|7|4D|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|4E|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|4F|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|4G|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|4H|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|4I|8|@]|9|@]|A|$]]|$1|S|3|T|5|U|7|4J|8|@]|9|@]|A|$V|W]]|$1|X|3|Y|5|6|7|4K|8|@$Z|4L|10|4M|11|12]]|9|@$Z|4N|10|4O|1|4P]]|A|$]]|$1|13|3|14|5|U|7|4Q|8|@]|9|@]|A|$V|W]]|$1|15|3|16|5|6|7|4R|8|@$Z|4S|10|4T|11|12]|$Z|4U|10|4V|11|12]]|9|@]|A|$]]|$1|17|3|18|5|U|7|4W|8|@]|9|@]|A|$V|W]]|$1|19|3|-4|5|6|7|4X|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|F|7|4Y|8|@$Z|4Z|10|50|11|12]]|9|@]|A|$]]|$1|1C|3|-4|5|6|7|51|8|@]|9|@]|A|$]]|$1|1D|3|-4|5|6|7|52|8|@]|9|@]|A|$]]|$1|1E|3|1F|5|F|7|53|8|@$Z|54|10|55|11|12]|$Z|56|10|57|11|12]]|9|@]|A|$]]|$1|1G|3|-4|5|6|7|58|8|@]|9|@]|A|$]]|$1|1H|3|-4|5|6|7|59|8|@]|9|@]|A|$]]|$1|1I|3|1J|5|F|7|5A|8|@$Z|5B|10|5C|11|12]]|9|@]|A|$]]|$1|1K|3|-4|5|6|7|5D|8|@]|9|@]|A|$]]|$1|1L|3|-4|5|6|7|5E|8|@]|9|@]|A|$]]|$1|1M|3|1N|5|F|7|5F|8|@$Z|5G|10|5H|11|12]]|9|@]|A|$]]|$1|1O|3|-4|5|6|7|5I|8|@]|9|@]|A|$]]|$1|1P|3|1Q|5|6|7|5J|8|@]|9|@]|A|$]]|$1|1R|3|1S|5|6|7|5K|8|@]|9|@]|A|$]]|$1|1T|3|1U|5|U|7|5L|8|@]|9|@]|A|$V|W]]|$1|1V|3|1W|5|6|7|5M|8|@]|9|@]|A|$]]|$1|1X|3|1Y|5|6|7|5N|8|@]|9|@]|A|$]]|$1|1Z|3|20|5|U|7|5O|8|@]|9|@]|A|$V|W]]|$1|21|3|22|5|6|7|5P|8|@$Z|5Q|10|5R|11|23]]|9|@]|A|$]]|$1|24|3|25|5|6|7|5S|8|@$Z|5T|10|5U|11|12]]|9|@]|A|$]]|$1|26|3|27|5|6|7|5V|8|@$Z|5W|10|5X|11|12]]|9|@]|A|$]]|$1|28|3|29|5|6|7|5Y|8|@]|9|@]|A|$]]|$1|2A|3|2B|5|6|7|5Z|8|@]|9|@]|A|$]]|$1|2C|3|2D|5|6|7|60|8|@]|9|@]|A|$]]|$1|2E|3|2F|5|U|7|61|8|@]|9|@]|A|$V|W]]|$1|2G|3|2H|5|6|7|62|8|@]|9|@]|A|$]]|$1|2I|3|2J|5|U|7|63|8|@]|9|@]|A|$V|W]]|$1|2K|3|2L|5|6|7|64|8|@$Z|65|10|66|11|12]]|9|@]|A|$]]|$1|2M|3|-4|5|6|7|67|8|@]|9|@]|A|$]]|$1|2N|3|2O|5|F|7|68|8|@]|9|@$Z|69|10|6A|1|6B]]|A|$]]|$1|2P|3|-4|5|6|7|6C|8|@]|9|@]|A|$]]|$1|2Q|3|2R|5|6|7|6D|8|@]|9|@]|A|$]]|$1|2S|3|2T|5|6|7|6E|8|@$Z|6F|10|6G|11|23]]|9|@]|A|$]]|$1|2U|3|2V|5|6|7|6H|8|@$Z|6I|10|6J|11|23]|$Z|6K|10|6L|11|12]]|9|@$Z|6M|10|6N|1|6O]]|A|$]]|$1|2W|3|2X|5|6|7|6P|8|@$Z|6Q|10|6R|11|23]|$Z|6S|10|6T|11|12]]|9|@$Z|6U|10|6V|1|6W]]|A|$]]|$1|2Y|3|-4|5|6|7|6X|8|@]|9|@]|A|$]]|$1|2Z|3|30|5|31|7|6Y|8|@$Z|6Z|10|70|11|23]|$Z|71|10|72|11|23]|$Z|73|10|74|11|12]|$Z|75|10|76|11|12]|$Z|77|10|78|11|12]]|9|@]|A|$]]|$1|32|3|-4|5|6|7|79|8|@]|9|@]|A|$]]|$1|33|3|-4|5|6|7|7A|8|@]|9|@]|A|$]]|$1|34|3|35|5|6|7|7B|8|@$Z|7C|10|7D|11|23]]|9|@]|A|$]]|$1|36|3|-4|5|6|7|7E|8|@]|9|@]|A|$]]|$1|37|3|38|5|31|7|7F|8|@$Z|7G|10|7H|11|23]|$Z|7I|10|7J|11|12]|$Z|7K|10|7L|11|12]]|9|@]|A|$]]|$1|39|3|-4|5|6|7|7M|8|@]|9|@]|A|$]]|$1|3A|3|-4|5|6|7|7N|8|@]|9|@]|A|$]]|$1|3B|3|3C|5|6|7|7O|8|@$Z|7P|10|7Q|11|23]]|9|@]|A|$]]|$1|3D|3|3E|5|U|7|7R|8|@]|9|@]|A|$V|W]]|$1|3F|3|3G|5|6|7|7S|8|@$Z|7T|10|7U|11|23]]|9|@]|A|$]]|$1|3H|3|-4|5|6|7|7V|8|@]|9|@]|A|$]]|$1|3I|3|3J|5|F|7|7W|8|@$Z|7X|10|7Y|11|23]]|9|@$Z|7Z|10|80|1|81]]|A|$]]|$1|3K|3|-4|5|6|7|82|8|@]|9|@]|A|$]]|$1|3L|3|3M|5|6|7|83|8|@$Z|84|10|85|11|23]]|9|@]|A|$]]|$1|3N|3|3O|5|U|7|86|8|@]|9|@]|A|$V|W]]|$1|3P|3|3Q|5|6|7|87|8|@$Z|88|10|89|11|23]]|9|@$Z|8A|10|8B|1|8C]]|A|$]]|$1|3R|3|3S|5|6|7|8D|8|@$Z|8E|10|8F|11|23]]|9|@]|A|$]]|$1|3T|3|-4|5|6|7|8G|8|@]|9|@]|A|$]]]|3U|$3V|$5|3W|3X|3Y|A|$3Z|40]]|41|$5|3W|3X|3Y|A|$3Z|42]]|43|$5|3W|3X|3Y|A|$3Z|44]]|45|$5|3W|3X|3Y|A|$3Z|44]]|46|$5|3W|3X|3Y|A|$3Z|40]]|47|$5|3W|3X|3Y|A|$3Z|48]]]]

Given your specifications (plus additional info in the comments),
<ul>
<li>You have a numeric ID column (integer numbers) with only few (or moderately few) gaps.</li>
<li>Obviously no or few write operations.</li>
<li>Your ID column has to be indexed! A primary key serves nicely.</li>
</ul>
The query below does not need a sequential scan of the big table, only an index scan.
First, get estimates for the main query:
<pre class="lang-sql prettyprint-override"><code>SELECT count(*) AS ct -- optional
 , min(id) AS min_id
 , max(id) AS max_id
 , max(id) - min(id) AS id_span
FROM big;
</code></pre>
The only possibly expensive part is the <code>count(*)</code> (for huge tables). Given above specifications, you don't need it. An estimate will do just fine, available at almost no cost (<a href="https://stackoverflow.com/questions/7943233/fast-way-to-discover-the-row-count-of-a-table/7945274#7945274">detailed explanation here</a>):
<pre class="lang-sql prettyprint-override"><code>SELECT reltuples AS ct FROM pg_class
WHERE oid = 'schema_name.big'::regclass;
</code></pre>
As long as <code>ct</code> isn't much smaller than <code>id_span</code>, the query will outperform other approaches.
<pre class="lang-sql prettyprint-override"><code>WITH params AS (
 SELECT 1 AS min_id -- minimum id &lt;= 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
</code></pre>
<ul>
<li>Generate random numbers in the <code>id</code> space. You have &quot;few gaps&quot;, so add 10 % (enough to easily cover the blanks) to the number of rows to retrieve.
</li>
<li>Each <code>id</code> can be picked multiple times by chance (though very unlikely with a big id space), so group the generated numbers (or use <code>DISTINCT</code>).
</li>
<li>Join the <code>id</code>s to the big table. This should be very fast with the index in place.
</li>
<li>Finally trim surplus <code>id</code>s that have not been eaten by dupes and gaps. Every row has a completely equal chance to be picked.
</li>
</ul>
<h3>Short version</h3>
You can simplify this query. The CTE in the query above is just for educational purposes:
<pre class="lang-sql prettyprint-override"><code>SELECT *
FROM (
 SELECT DISTINCT 1 + trunc(random() * 5100000)::integer AS id
 FROM generate_series(1, 1100) g
 ) r
JOIN big USING (id)
LIMIT 1000;
</code></pre>
<h2>Refine with rCTE</h2>
Especially if you are not so sure about gaps and estimates.
<pre class="lang-sql prettyprint-override"><code>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
</code></pre>
We can work with a smaller surplus in the base query. If there are too many gaps so we don't find enough rows in the first iteration, the rCTE continues to iterate with the recursive term. We still need relatively few gaps in the ID space or the recursion may run dry before the limit is reached - or we have to start with a large enough buffer which defies the purpose of optimizing performance.
Duplicates are eliminated by the <code>UNION</code> in the rCTE.
The outer <code>LIMIT</code> makes the CTE stop as soon as we have enough rows.
This query is carefully drafted to use the available index, generate actually random rows and not stop until we fulfill the limit (unless the recursion runs dry). There are a number of pitfalls here if you are going to rewrite it.
<h2>Wrap into function</h2>
For repeated use with varying parameters:
<pre class="lang-sql prettyprint-override"><code>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 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
 )
 TABLE random_pick
 LIMIT _limit;
END
$func$;
</code></pre>
Call:
<pre><code>SELECT * FROM f_random_sample();
SELECT * FROM f_random_sample(500, 1.05);
</code></pre>
You could even make this generic to work for any table: Take the name of the PK column and the table as polymorphic type and use <code>EXECUTE</code> ... But that's beyond the scope of this question. See:
<ul>
<li><a href="https://stackoverflow.com/questions/11740256/refactor-a-pl-pgsql-function-to-return-the-output-of-various-select-queries/11751557#11751557">Refactor a PL/pgSQL function to return the output of various SELECT queries</a></li>
</ul>
<h3>Possible alternative</h3>
IF your requirements allow identical sets for repeated calls (and we are talking about repeated calls) I would consider a materialized view. Execute above query once and write the result to a table. Users get a quasi random selection at lightening speed. Refresh your random pick at intervals or events of your choosing.
<h2>Postgres 9.5 introduces <a href="https://www.postgresql.org/docs/current/sql-select.html#SQL-FROM" rel="noreferrer"><code>TABLESAMPLE SYSTEM (n)</code></a></h2>
Where <code>n</code> is a percentage. <a href="https://www.postgresql.org/docs/current/sql-select.html#SQL-FROM" rel="noreferrer">The manual:</a>
<blockquote>
The <code>BERNOULLI</code> and <code>SYSTEM</code> sampling methods each accept a single
argument which is the fraction of the table to sample, expressed as a
percentage between 0 and 100. This argument can be any <code>real</code>-valued expression.
</blockquote>
Bold emphasis mine. It's very fast, but the result is not exactly random. The manual again:
<blockquote>
The <code>SYSTEM</code> method is significantly faster than the <code>BERNOULLI</code> method
when small sampling percentages are specified, but it may return a
less-random sample of the table as a result of clustering effects.
</blockquote>
The number of rows returned can vary wildly. For our example, to get roughly 1000 rows:
<pre><code>SELECT * FROM big TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);
</code></pre>
Related:
<ul>
<li><a href="https://stackoverflow.com/questions/7943233/fast-way-to-discover-the-row-count-of-a-table/7945274#7945274">Fast way to discover the row count of a table in PostgreSQL</a></li>
</ul>
Or install the additional module <a href="https://www.postgresql.org/docs/current/tsm-system-rows.html" rel="noreferrer">tsm_system_rows</a> to get the number of requested rows exactly (if there are enough) and allow for the more convenient syntax:
<pre><code>SELECT * FROM big TABLESAMPLE SYSTEM_ROWS(1000);
</code></pre>
See <a href="https://stackoverflow.com/a/41337788/939860">Evan's answer</a> for details.
But that's still not exactly random.

blocks|key|212549|text|postgresql+order+by+random()，按随机顺序选择行：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|212550|这很慢，因为它会对整个表进行排序，以保证每一行都有完全相等的机会被选中。全表扫描对于完美的随机性来说是不可避免的。|212551|select+your_columns+from+your_table+ORDER+BY+random()|code-block|syntax|javascript|212552|postgresql+order+by+random()，带有distinct：|212553|select+*+from+
++(select+distinct+your_columns+from+your_table)+table_alias
ORDER+BY+random()|212554|postgresql+order+by+random+limit一行：|212555|这也很慢，因为它必须进行表扫描，以确保可能被选中的每一行都有相同的机会被选中，就在此时：|212556|select+your_columns+from+your_table+ORDER+BY+random()+limit+1|212557|使用元素周期表扫描的恒定时间选择随机N行：|212558|如果您的表很大，那么上面的表扫描是一个需要5分钟才能完成的即兴表演。|212559|为了走得更快，您可以在后台安排一个表扫描重建索引，这将保证在O(1)恒定时间速度下进行完全随机的选择，除了在夜间重建索引表扫描期间，它必须等待维护完成，才能接收到另一个随机行。|offset|length|style|CODE|212560|--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.|212561|改编自https://www.gab.lc/articles/bigdata_postgresql_order_by_random|212562|或者，您可以去掉映射表，使其成为big_data上的1个新列。然后，您只需在function/procedure+(两者都是固定时间操作)中“在0+...+max(id)之间选择一个随机整数”，然后返回该索引。如果由于最近未执行重新索引而不再存在该索引，请选择另一个索引。|212563|entityMap|0|LINK|mutability|MUTABLE|url|https://www.gab.lc/articles/bigdata_postgresql_order_by_random^0|0|0|0|0|0|0|0|0|0|0|U|4|0|0|3|1Q|0|0|G|8|20|1|26|7|0^^$0|@$1|2|3|4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1H|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1I|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1J|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|1K|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|1L|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1M|8|@]|9|@]|A|$]]|$1|Q|3|R|5|F|7|1N|8|@]|9|@]|A|$G|H]]|$1|S|3|T|5|6|7|1O|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1P|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1Q|8|@$Y|1R|Z|1S|10|11]]|9|@]|A|$]]|$1|12|3|13|5|F|7|1T|8|@]|9|@]|A|$G|H]]|$1|14|3|15|5|6|7|1U|8|@]|9|@$Y|1V|Z|1W|1|1X]]|A|$]]|$1|16|3|17|5|6|7|1Y|8|@$Y|1Z|Z|20|10|11]|$Y|21|Z|22|10|11]|$Y|23|Z|24|10|11]]|9|@]|A|$]]|$1|18|3|-4|5|6|7|25|8|@]|9|@]|A|$]]]|19|$1A|$5|1B|1C|1D|A|$1E|1F]]]]

<h2>postgresql order by random(), select rows in random order:</h2>
This is slow because it orders the whole table to guarantee that every row gets an exactly equal chance of being chosen. A full table scan is unavoidable for perfect randomness.
<pre><code>select your_columns from your_table ORDER BY random()
</code></pre>
<h2>postgresql order by random() with a distinct:</h2>
<pre><code>select * from 
 (select distinct your_columns from your_table) table_alias
ORDER BY random()
</code></pre>
<h2>postgresql order by random limit one row:</h2>
This is also slow, because it has to table scan to make sure every row that might be chosen has an equal chance of being chosen, right this instant:
<pre><code>select your_columns from your_table ORDER BY random() limit 1
</code></pre>
<h2>Constant Time Select Random N rows with periodic table scan:</h2>
If your table is huge then the above table-scans are a show stopper taking up to 5 minutes to finish.
To go faster you can schedule a behind the scenes nightly table-scan reindexing which will guarantee a perfectly random selection in an <code>O(1)</code> constant-time speed, except during the nightly reindexing table-scan, where it must wait for maintenance to finish before you may receive another random row.
<pre><code>--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.
</code></pre>
Adapted from: <a href="https://www.gab.lc/articles/bigdata_postgresql_order_by_random" rel="nofollow noreferrer">https://www.gab.lc/articles/bigdata_postgresql_order_by_random</a>
Alternatively you could get rid of the mapper table and make it 1 new column on <code>big_data</code>. Then you simply &quot;choose a random integer between <code>0</code> ... <code>max(id)</code>&quot; in the function/procedure (Both are constant time operations) then return that index, if it no longer exists because a re-index hasn't been done recently, choose another.

blocks|key|1231929|text|物化视图“可能的替代”outlined+by+Erwin+Brandstetter的变体是可能的。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1231930|例如，假设您不希望在返回的随机化值中存在重复项。因此，您需要在包含您的(非随机化的)值集的主表上设置一个布尔值。|1231931|假设这是输入表：|1231932|id_values++id++%7C+++used
+++++++++++----%2B--------
+++++++++++1+++%7C+++FALSE
+++++++++++2+++%7C+++FALSE
+++++++++++3+++%7C+++FALSE
+++++++++++4+++%7C+++FALSE
+++++++++++5+++%7C+++FALSE
+++++++++++...|code-block|syntax|javascript|1231933|根据需要填充ID_VALUES表。然后，如Erwin所述，创建一个将ID_VALUES表随机化一次的实例化视图：|style|CODE|1231934|CREATE+MATERIALIZED+VIEW+id_values_randomized+AS
++SELECT+id
++FROM+id_values
++ORDER+BY+random();|1231935|请注意，实体化视图不包含used列，因为该列很快就会过期。视图也不需要包含id_values表中的其他列。|1231936|为了获得(和“消费”)随机值，在id_values上使用更新返回，使用join从id_values_randomized中选择id_values，并应用所需的条件来仅获得相关的可能性。例如：|1231937|UPDATE+id_values
SET+used+=+TRUE
WHERE+id_values.id+IN+
++(SELECT+i.id
++++FROM+id_values_randomized+r+INNER+JOIN+id_values+i+ON+i.id+=+r.id
++++WHERE+(NOT+i.used)
++++LIMIT+5)
RETURNING+id;|1231938|根据需要更改LIMIT+--如果一次只需要一个随机值，请将LIMIT更改为1。|1231939|有了id_values上的适当索引，我相信返回更新应该会执行得非常快，而且负载很少。它通过一次数据库往返返回随机化值。“合格”行的条件可以根据需要而变得复杂。可以随时将新行添加到id_values表中，一旦实体化视图刷新(可能会在非高峰时间运行)，应用程序就可以访问这些新行。实例化视图的创建和刷新将很慢，但只需要在将新的id添加到id_values表中时执行。|1231940|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/8675160/430128^0|B|U|0|0|0|0|0|6|9|Y|9|0|0|11|9|0|G|9|14|K|1R|9|0|0|6|5|T|5|11|1|0|2|9|2H|9|4M|9|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@$A|1B|B|1C|1|1D]]|C|$]]|$1|D|3|E|5|6|7|1E|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|1F|8|@]|9|@]|C|$]]|$1|H|3|I|5|J|7|1G|8|@]|9|@]|C|$K|L]]|$1|M|3|N|5|6|7|1H|8|@$A|1I|B|1J|O|P]|$A|1K|B|1L|O|P]]|9|@]|C|$]]|$1|Q|3|R|5|J|7|1M|8|@]|9|@]|C|$K|L]]|$1|S|3|T|5|6|7|1N|8|@$A|1O|B|1P|O|P]]|9|@]|C|$]]|$1|U|3|V|5|6|7|1Q|8|@$A|1R|B|1S|O|P]|$A|1T|B|1U|O|P]|$A|1V|B|1W|O|P]]|9|@]|C|$]]|$1|W|3|X|5|J|7|1X|8|@]|9|@]|C|$K|L]]|$1|Y|3|Z|5|6|7|1Y|8|@$A|1Z|B|20|O|P]|$A|21|B|22|O|P]|$A|23|B|24|O|P]]|9|@]|C|$]]|$1|10|3|11|5|6|7|25|8|@$A|26|B|27|O|P]|$A|28|B|29|O|P]|$A|2A|B|2B|O|P]]|9|@]|C|$]]|$1|12|3|-4|5|6|7|2C|8|@]|9|@]|C|$]]]|13|$14|$5|15|16|17|C|$18|19]]]]

A variation of the materialized view "Possible alternative" <a href="https://stackoverflow.com/a/8675160/430128">outlined by Erwin Brandstetter</a> is possible.

Say, for example, that you don't want duplicates in the randomized values that are returned. So you will need to set a boolean value on the primary table containing your (non-randomized) set of values.

Assuming this is the input table:

<pre><code>id_values id | used
 ----+--------
 1 | FALSE
 2 | FALSE
 3 | FALSE
 4 | FALSE
 5 | FALSE
 ...
</code></pre>

Populate the <code>ID_VALUES</code> table as needed. Then, as described by Erwin, create a materialized view that randomizes the <code>ID_VALUES</code> table once:



<pre class="lang-sql prettyprint-override"><code>CREATE MATERIALIZED VIEW id_values_randomized AS
 SELECT id
 FROM id_values
 ORDER BY random();
</code></pre>

Note that the materialized view does not contain the used column, because this will quickly become out-of-date. Nor does the view need to contain other columns that may be in the <code>id_values</code> table.

In order to obtain (and "consume") random values, use an UPDATE-RETURNING on <code>id_values</code>, selecting <code>id_values</code> from <code>id_values_randomized</code> with a join, and applying the desired criteria to obtain only relevant possibilities. For example:

<pre class="lang-sql prettyprint-override"><code>UPDATE id_values
SET used = TRUE
WHERE id_values.id IN 
 (SELECT i.id
 FROM id_values_randomized r INNER JOIN id_values i ON i.id = r.id
 WHERE (NOT i.used)
 LIMIT 5)
RETURNING id;
</code></pre>

Change <code>LIMIT</code> as necessary -- if you only need one random value at a time, change <code>LIMIT</code> to <code>1</code>.

With the proper indexes on <code>id_values</code>, I believe the UPDATE-RETURNING should execute very quickly with little load. It returns randomized values with one database round-trip. The criteria for "eligible" rows can be as complex as required. New rows can be added to the <code>id_values</code> table at any time, and they will become accessible to the application as soon as the materialized view is refreshed (which can likely be run at an off-peak time). Creation and refresh of the materialized view will be slow, but it only needs to be executed when new id's are added to the <code>id_values</code> table.

blocks|key|1231954|text|如果只想要一行，可以使用从count派生的计算offset。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1231955|select+*+from+table_name+limit+1
offset+floor(random()+*+(select+count(*)+from+table_name));|code-block|syntax|javascript|1231956|entityMap^0|D|5|N|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

If you want just one row, you can use a calculated <code>offset</code> derived from <code>count</code>.

<pre class="lang-sql prettyprint-override"><code>select * from table_name limit 1
offset floor(random() * (select count(*) from table_name));
</code></pre>

blocks|key|4000481|text|添加一个名为r的列，类型为serial。索引r。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4000482|假设我们有200,000行，我们将生成一个随机数n，其中0<+n+<=+200,000。|4000483|使用r+>+n选择行，对它们进行ASC排序，然后选择最小的行。|4000484|代码：|4000485|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);|code-block|syntax|javascript|4000486|代码是不言自明的。中间的子查询用于快速估计https://stackoverflow.com/a/7945274/1271094中的表行计数。|4000487|在应用程序级别，如果n+>行数或需要选择多行，则需要再次执行该语句。|4000488|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/7945274/1271094^0|6|1|D|6|M|1|0|O|1|V|1|0|2|5|G|3|0|0|0|L|17|0|0|A|1|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]|$9|15|A|16|B|C]|$9|17|A|18|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|19|8|@$9|1A|A|1B|B|C]|$9|1C|A|1D|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|1E|8|@$9|1F|A|1G|B|C]|$9|1H|A|1I|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|1J|8|@]|D|@]|E|$]]|$1|L|3|M|5|N|7|1K|8|@]|D|@]|E|$O|P]]|$1|Q|3|R|5|6|7|1L|8|@]|D|@$9|1M|A|1N|1|1O]]|E|$]]|$1|S|3|T|5|6|7|1P|8|@$9|1Q|A|1R|B|C]]|D|@]|E|$]]|$1|U|3|-4|5|6|7|1S|8|@]|D|@]|E|$]]]|V|$W|$5|X|Y|Z|E|$10|11]]]]

Add a column called <code>r</code> with type <code>serial</code>. Index <code>r</code>.

Assume we have 200,000 rows, we are going to generate a random number <code>n</code>, where 0 &lt; <code>n</code> &lt;= 200, 000.

Select rows with <code>r &gt; n</code>, sort them <code>ASC</code> and select the smallest one.

Code:

<pre><code>select * from YOUR_TABLE 
where r &gt; (
 select (
 select reltuples::bigint AS estimate
 from pg_class
 where oid = 'public.YOUR_TABLE'::regclass) * random()
 )
order by r asc limit(1);
</code></pre>

The code is self-explanatory. The subquery in the middle is used to quickly estimate the table row counts from <a href="https://stackoverflow.com/a/7945274/1271094">https://stackoverflow.com/a/7945274/1271094</a> .

In application level you need to execute the statement again if <code>n</code> > the number of rows or need to select multiple rows.

blocks|key|4000522|text|从PostgreSQL+9.5开始，有了一个专门用于从表中获取随机元素的新语法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4000523|SELECT+*+FROM+mytable+TABLESAMPLE+SYSTEM+(5);|code-block|syntax|javascript|4000524|这个例子给出了mytable中5%25的元素。|offset|length|style|CODE|4000525|请参阅文档的更多说明：http://www.postgresql.org/docs/current/static/sql-select.html|4000526|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/static/sql-select.html^0|0|0|7|7|0|B|1P|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@$I|Z|J|10|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|11|8|@]|9|@$I|12|J|13|1|14]]|A|$]]|$1|O|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

Starting with PostgreSQL 9.5, there's a new syntax dedicated to getting random elements from a table :
<pre><code>SELECT * FROM mytable TABLESAMPLE SYSTEM (5);
</code></pre>
This example will give you 5% of elements from <code>mytable</code>.
See more explanation on the documentation: <a href="http://www.postgresql.org/docs/current/static/sql-select.html" rel="noreferrer">http://www.postgresql.org/docs/current/static/sql-select.html</a>

blocks|key|1229607|text|select+*+from+table+order+by+random()+limit+1000;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1229608|如果您知道需要多少行，请查看+.|unstyled|offset|length|style|BOLD|1229609|tsm_system_rows|1229610|1229611|模块提供了表采样方法SYSTEM_ROWS，可以在SELECT命令的TABLESAMPLE子句中使用。|blockquote|1229612|1229613|此表采样方法接受单个整数参数，该参数是要读取的最大行数。生成的示例将始终包含相同数量的行，除非表没有包含足够的行，在这种情况下，整个表都将被选中。与内置系统采样方法类似，SYSTEM_ROWS执行块级采样，因此采样不是完全随机的，而是可能受到群集效应的影响，特别是在仅请求少量行的情况下。|1229614|1229615|首先安装扩展|1229616|CREATE+EXTENSION+tsm_system_rows;|1229617|那么你的查询，|1229618|SELECT+*
FROM+table
TABLESAMPLE+SYSTEM_ROWS(1000);|1229619|entityMap^0|0|F|1|0|0|0|0|0|21|1Z|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|15|8|@$G|16|H|17|I|J]]|9|@]|A|$]]|$1|K|3|L|5|F|7|18|8|@]|9|@]|A|$]]|$1|M|3|-4|5|F|7|19|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|1A|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|F|7|1B|8|@]|9|@]|A|$]]|$1|R|3|S|5|F|7|1C|8|@$G|1D|H|1E|I|J]]|9|@]|A|$]]|$1|T|3|-4|5|F|7|1F|8|@]|9|@]|A|$]]|$1|U|3|V|5|F|7|1G|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1H|8|@]|9|@]|A|$B|C]]|$1|Y|3|Z|5|F|7|1I|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1J|8|@]|9|@]|A|$B|C]]|$1|12|3|-4|5|F|7|1K|8|@]|9|@]|A|$]]]|13|$]]

<pre><code>select * from table order by random() limit 1000;
</code></pre>

If you know how many rows you want, check out <a href="https://www.postgresql.org/docs/current/static/tsm-system-rows.html" rel="noreferrer"><code>tsm_system_rows</code></a>.

<h1>tsm_system_rows</h1>

<blockquote>
 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.
</blockquote>

First install the extension

<pre><code>CREATE EXTENSION tsm_system_rows;
</code></pre>

Then your query,

<pre><code>SELECT *
FROM table
TABLESAMPLE SYSTEM_ROWS(1000);
</code></pre>

blocks|key|4000568|text|这是一个适合我的决定。我想它很容易理解和执行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4000569|SELECT+
++field_1,+
++field_2,+
++field_2,+
++random()+as+ordering
FROM+
++big_table
WHERE+
++some_conditions
ORDER+BY
++ordering+
LIMIT+1000;|code-block|syntax|javascript|4000570|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is a decision that works for me. I guess it's very simple to understand and execute.

<pre><code>SELECT 
 field_1, 
 field_2, 
 field_2, 
 random() as ordering
FROM 
 big_table
WHERE 
 some_conditions
ORDER BY
 ordering 
LIMIT 1000;
</code></pre>

blocks|key|4000590|text|我知道我来晚了，但是我刚刚发现了一个叫pg_sample的很棒的工具|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4000591|4000592|+pg_sample+-从较大的PostgreSQL数据库中提取一个小的样本数据集，同时保持引用完整性。|blockquote|style|CODE|4000593|4000594|4000595|我用一个350M行的数据库试过了，它真的很快，不知道它的随机性。|4000596|./pg_sample+--limit="small_table+=+*"+--limit="large_table+=+100000"+-U+postgres+source_db+%7C+psql+-U+postgres+target_db|code-block|syntax|javascript|4000597|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/mla/pg_sample^0|J|9|0|0|0|1|9|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|D|3|-4|5|6|7|14|8|@]|9|@]|C|$]]|$1|E|3|F|5|G|7|15|8|@$A|16|B|17|H|I]]|9|@]|C|$]]|$1|J|3|-4|5|6|7|18|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|19|8|@]|9|@]|C|$]]|$1|L|3|M|5|6|7|1A|8|@]|9|@]|C|$]]|$1|N|3|O|5|P|7|1B|8|@]|9|@]|C|$Q|R]]|$1|S|3|-4|5|6|7|1C|8|@]|9|@]|C|$]]]|T|$U|$5|V|W|X|C|$Y|Z]]]]

I know I'm a little late to the party, but I just found this awesome tool called <a href="https://github.com/mla/pg_sample" rel="nofollow noreferrer">pg_sample</a>:

<blockquote>
 <code>pg_sample</code> - extract a small, sample dataset from a larger PostgreSQL database while maintaining referential integrity.
</blockquote>

I tried this with a 350M rows database and it was really fast, don't know about the randomness.

<pre class="lang-none prettyprint-override"><code>./pg_sample --limit="small_table = *" --limit="large_table = 100000" -U postgres source_db | psql -U postgres target_db
</code></pre>

blocks|key|212716|text|从我的经验中得到的一个教训是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|212717|offset+floor(random()+*+N)+limit+1+order+by+random()+limit+1**.**不如快|offset|length|style|BOLD|CODE|212718|我认为offset方法会更快，因为它可以节省Postgres中的排序时间。事实证明并非如此。|212719|entityMap^0|0|0|Y|Z|R|1V|1|0|Y|Z|P|0|0|1A|3|6|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@$D|O|E|P|F|G]|$D|Q|E|R|F|G]|$D|S|E|T|F|G]|$D|U|E|V|F|H]|$D|W|E|X|F|H]]|9|@]|A|$]]|$1|I|3|J|5|6|7|Y|8|@$D|Z|E|10|F|G]|$D|11|E|12|F|H]]|9|@]|A|$]]|$1|K|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|L|$]]

One lesson from my experience: 

<code>offset floor(random() * N) limit 1</code> is not faster than <code>order by random() limit 1</code>. 

I thought the <code>offset</code> approach would be faster because it should save the time of sorting in Postgres. Turns out it wasn't.

I want a random selection of rows in PostgreSQL, I tried this:
<pre><code>select * from table where random() &lt; 0.01;
</code></pre>
But some other recommend this:
<pre><code>select * from table order by random() limit 1000;
</code></pre>
I have a very large table with 500 Million rows, I want it to be fast.
Which approach is better? What are the differences? What is the best way to select random rows?

Best way to select random rows PostgreSQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我想在PostgreSQL中随机选择行，我试了一下：select * from table where random() < 0.01;但其他一些人建议这样做：select * from table order by random() limit 1000;我有一个非常大的表，有5亿行，我想让它更快。哪种方法更好？有什...

问选择随机行的最佳方法PostgreSQL
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问选择随机行的最佳方法PostgreSQLEN