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()*100000)+1 as int)); My times on it on a 100,000 row table are < 1 millisecond. Note that you have to have a hole free sequence AND know how many rows there are, but if you can meet those needs, this is screamingly fast. On Sat, 30 Aug 2003, Russell Garrett wrote: > 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 ever so slightly less exotic > London. Though Micronesia might be nice... > > Russ (also from last.fm but without the fancy address) > > [EMAIL PROTECTED] wrote: > > 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; > >> > >> Of course, PostgreSQL may have trouble with that update due to > >> evaluation of the unique constraint immediately -- so drop the > >> primary key first, and add it back after. > > > > And if there are child tables, they'd all have to be updated, too. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
