Re: Update table with random values from another table

On 12/02/09, Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> =?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= <gryzman(at)gmail(dot)com> writes:
> > On Thu, Feb 12, 2009 at 1:10 PM, Rory Campbell-Lange
> > <rory(at)campbell-lange(dot)net> wrote:
> >> UPDATE
> >> users
> >> SET t_firstname =
> >> (select firstname from testnames order by random() limit 1),
> >> t_surname =
> >> (select lastname from testnames order by random() limit 1)
> >> WHERE
> >> n_role IN (2,3);
> >>
> >> Doesn't return either after 60 seconds on a 8 core machine with 8GB of
> >> RAM and 15K disks in R10 (no swap in use).
>
> > That would be because, for every row in users table, postgres has to
> > run two subselects, with order by random() (which in it self is quite
> > expensive).
>
> Well, no, because those subselects are independent of the parent query;
> I'd expect PG to do them just once. Do they show up as "SubPlans" or
> "InitPlans" in EXPLAIN?

Hi Tom

I don't know what the problem was, but I restarted my psql session and the
query runs in 2.181 ms. The plan is an InitPlan. Apologies for the specious
post.

I'm still unable to work out how to update some columns in my user's table each
with a unique record from my testnames table :).

Rory