Select k random rows from postgres django ORM
<p>We have a requirement, that  we want to select k random rows from a database.
So, our intial thought was going like this :-</p>

<pre><code>table.objects.filter(..).order_by('?')[:k]
</code></pre>

<p>but then we read over the internet that this is highly inefficient solution so we came up with this (not so innovative):-</p>

<pre><code>random.sample(table.objects.filter(..), k)
</code></pre>

<p>But this seems to be more slower than previous.</p>

<p>So, we want to know what is correct approach for selection exactly <em>k</em> rows from the database, which in our case is postgres.</p>

<p>As Daniel Roseman mentioned in a comment, the reason why</p>

<pre><code>random.sample(table.objects.filter(..), k)
</code></pre>

<p>is slow, is because you'd have to fetch <em>all</em> objects, then find <code>k</code> out of that query set.</p>

<p>I have encountered exactly the same type of problem and the way we solved this was to</p>

<ul>
<li>Find <code>max_id</code> in the table.</li>
<li>Pick <code>k</code> numbers from the set of <code>1, ..., max_id</code></li>
<li>Do <code>table.objects.filter(id__in=set_of_ks)</code></li>
</ul>

<p>This of course assumes that there are no "holes" in the set of table IDs.</p>

python django postgresql orm