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 100,0
Considering that we'd have to index the random field too, it'd be neater in
the long term to re-number the primary key. Although, being a primary key,
that's foreign-keyed from absolutely everywhere, so that'd probably take an
amusingly long time.
...and no we're not from Micronesia, we're from ev
On Sat, 2003-08-30 at 09:01, Rod Taylor wrote:
> > 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;
>
>
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 functio
Richard Jones <[EMAIL PROTECTED]> writes:
>>> 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
> i was hoping there was some trickery with sequences that would allow me to
> easily pick a random valid sequence numbe
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
> 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
e
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 sus
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 ser
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 ju
10 matches
Mail list logo
