Re: ORDER BY random() LIMIT 1 slowness

Dear Jean-Luc,

I don't think my simplified example missed any of your solution's features.
The essence, in my eyes, is that it has nothing to do with tables. It's only
related to sequences.

In short, you _cannot_ use currval() in any single _session_ until you use
nextval() in the same session, even if you created the sequence in the very
same session. Using a serial field in a table or using the sequence directly
is indifferent.

Or I'm missing something here.

As for Tom's solution:

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Wednesday, December 18, 2002 4:56 PM

> Personally though, I'd skip the sequence entirely and do
>
> create table poetry (...,
> rand float8 default random());
> create index on poetry.rand
>
> select * from poetry where rand > random() order by rand limit 1;

I'm not sure it's as flat as a random number should be. I have some relation
to mathematics but can't see it clearly right now. I fear it's more likely a
normal distribution, not linear (or whatsits called). But if I needed
something like this, I'd be happy with this solution anyway.

G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------
----- Original Message -----
From: "Jean-Luc Lachance" <jllachan(at)nsd(dot)ca>
Sent: Wednesday, December 18, 2002 5:55 PM

Gabor,

You are right about the missing 'r', but I think you missed my point.
You should modify your table so that it has a serial field and reload
it.

JLL

P.S. I run 7.2 so ALTER TABLE ADD rand SERIAL; does not work, but it may
work under 7.3