Phil,
> Question... does the above query have to "look" at every row in tablea,
> assign each a random number, sort it, and then return just one result?
Yeah. It's resource-intensive if you have, say, 8 million rows.
> If so, would it make more sense to setup a periodic process (via cron say)
> to do extract 10-20 products into a featured_product table and query that?
>
> Just thinking in terms of the amount of pounding the DB would take...
Well, if you have a large table, the logical approach would be a PL/pgSQL
function that selects a random row based on the number of rows in the table *
random, and then picks a row based on LIMIT and OFFSET. This would be
somewhat less resource-intensive for a large table, but has the disadvantage
that it would have to be re-run for each random row you wanted to retrieve.
--
-Josh Berkus
Aglio Database Solutions
San Francisco