2009/10/14 Scott Marlowe <scott.marl...@gmail.com> > On Wed, Oct 14, 2009 at 1:20 AM, Pavel Stehule <pavel.steh...@gmail.com> > wrote: > > 2009/10/14 Thom Brown <thombr...@gmail.com>: > >> 2009/10/14 Scott Marlowe <scott.marl...@gmail.com>: > >> Why not just do something like: > >> > >> SELECT thisfield, thatfield > >> FROM my_table > >> WHERE thisfield IS NOT NULL > >> ORDER BY RANDOM() > >> LIMIT 1; > >> > > > > this works well on small tables. On large tables this query is extremely > slow. > > Exactly. If you're running that query over and over your "performance > test" is on how well pgsql can run that very query. :) Anything else > you do is likely to be noise by comparison. > > What I am using often to get a set of random rows is SELECT thisfield, thatfield FROM my_table WHERE random() < rowsneeded::float8/(select count * from my_table); Of course it does not give exact number of rows, but close enough for me. As of taking one row I'd try: select * from ( SELECT thisfield, thatfield FROM my_table WHERE random() < 100.0/(select count * from my_table)) a order by random() limit 1
I'd say probability of returning no rows is quite low and query can be extended even more by returning first row from table in this rare case.
