Can you just create an extra serial column and make sure that one is
always in order and no holes in it? (i.e. a nightly process, etc...)???
If so, then something like this truly flies:
select * from accounts where aid = (select cast(floor(random()*10)+1 as int));
My times on it on a
Hi,
i have a table of around 3 million rows from which i regularly (twice a second
at the moment) need to select a random row from
currently i'm doing order by rand() limit 1 - but i suspect this is
responsible for the large load on my db server - i guess that PG is doing far
too much work
On Sat, 30 Aug 2003, Richard Jones wrote:
Hi,
i have a table of around 3 million rows from which i regularly (twice a second
at the moment) need to select a random row from
currently i'm doing order by rand() limit 1 - but i suspect this is
responsible for the large load on my db server - i
On Saturday 30 August 2003 1:08 pm, you wrote:
On Sat, 30 Aug 2003, Richard Jones wrote:
Hi,
i have a table of around 3 million rows from which i regularly (twice a
second at the moment) need to select a random row from
currently i'm doing order by rand() limit 1 - but i suspect this is
i was hoping there was some trickery with sequences that would allow me to
easily pick a random valid sequence number..?
I would suggest renumbering the data.
ALTER SEQUENCE ... RESTART WITH 1;
UPDATE table SET pkey = DEFAULT;
Of course, PostgreSQL may have trouble with that update due to
On Sat, 2003-08-30 at 08:09, Richard Jones wrote:
Hi,
i have a table of around 3 million rows from which i regularly (twice a second
at the moment) need to select a random row from
currently i'm doing order by rand() limit 1 - but i suspect this is
responsible for the large load on my db
I said:
3. Your query now looks like
SELECT * FROM table WHERE random_id = random()
ORDER BY random_id LIMIT 1;
Correction: the above won't give quite the right query because random()
is marked as a volatile function. You can hide the random() call inside
a user-defined function
