Search Postgresql Archives

Re: Update table with random values from another table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 12/02/09, Tom Lane (tgl@xxxxxxxxxxxxx) wrote:
> =?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= <gryzman@xxxxxxxxx> writes:
> > On Thu, Feb 12, 2009 at 1:10 PM, Rory Campbell-Lange
> > <rory@xxxxxxxxxxxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux