Weeeell, we are getting close to something, that might work :) But the
problem with this solution is, that OFFSET refuses to take anything but
constant number (ie. 10). This might be overcome by first getting the count
in one query, then compute the random offest and build a new query with this
number as an OFFSET argument. It will probably be faster, but it is not too
clean - i would have to use some outside scripting or create a pl/pgSQL
function for that. I have access to only 7.1.3 version at the moment, maybe
7.2 can handle this like you wrote ?
Michal
-----Original Message-----
From: Doug McNaught [mailto:doug@wireboard.com]
Sent: Friday, August 02, 2002 6:20 PM
To: Michal Taborsky
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Selecting random row
"Michal Taborsky" <michal@taborsky.cz> writes:
> This does decrease the the cost, but only very little (10500 -> 9800),
which
> is not the solution. That thing with sequential index might work for some
> cases, that is true. Unfortunately not in this one, because I actually do
> not select random row from a table, but from a complex select query
> resultset. But thanks for the suggestions.
Why not
SELECT * FROM table LIMIT 1 OFFSET random(SELECT count(*) FROM TABLE);
I don't know if this is the exact syntax but you get the idea...
-Doug