Re: mal advice in FAQ 4.1.

"Nikolay Samokhvalov" <samokhvalov(at)gmail(dot)com> writes:

> Hubert recently posted his thoughts on this topic:
> http://www.depesz.com/index.php/2007/09/16/my-thoughts-on-getting-random-row/
>
> I've encountered with this problem several times in web development and
> every time found out that the best (in terms of performance) solution is to
> use some pseudo random approach (such as ">= random() limit 1" or "limit 1
> offset random()*N" or even pre-caching rows on app side).

"ORDER BY random() LIMIT 1" should be faster in 8.3 due to the bounded-sort
optimization. It should be basically the same as the two options above as far
as how many comparisons are done and how much memory is used. It does have to
call random() for every record whereas the solutions above only call random()
once.

But I think all of these are basically the same to a first degree
approximation. They all have to do a scan of all the records being considered.
If you want something faster you need a solution which can use an index to
scan only the target record. There are ways of doing that but they require
some application knowledge.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com