Re: mal advice in FAQ 4.1.

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Gregory Stark" <stark(at)enterprisedb(dot)com>
Cc: nikolay(at)samokhvalov(dot)com, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: mal advice in FAQ 4.1.
Date: 2007-10-09 16:40:23
Message-ID: 162867790710090940wdf3d1dv7cc03e9d3a7483f7@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

2007/10/9, Gregory Stark <stark(at)enterprisedb(dot)com>:
> "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.
>

It needs always seq scan :(, and take space on buffer cache. Solution
based on random generated PK are much faster. I collaborate with one
my customer. He shows random products from 10K products on every page
of one eShop. And he cannot understand, so ORDER random() LIMIT is bad
trick, because this trick is on PostgreSQL FAQ.

Pavel

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2007-10-09 16:52:47 Re: Possible bugreport 8.3 beta1 on Win32: Looking like a deadlock with AutoVacuum
Previous Message D'Arcy J.M. Cain 2007-10-09 16:33:15 Re: type money causes unrestorable dump