Re: Why are queries with subselects so slow? - Mailing list pgsql-general

"Robert Wille" <robertw@willeweb.com> writes:
> [ this is awfully slow: ]

> update image set state =3D ((state & -35184340369664::int8) | 0::int8) wher=
> e containerid in (select containerid from ancestry where ancestorid =3D 122=
> 8067)

IN is notoriously slow.  It'll be better in 7.4, but in the meantime
it's best avoided.  It appears from your schema that the subselect
cannot generate duplicate containerids, so you should be able to do
it like this:

update image set state = ((state & -35184340369664::int8) | 0::int8)
from ancestry
where image.containerid = ancestry.containerid
      and ancestry.ancestorid = 1228067;

            regards, tom lane