On Mon, Oct 26, 2009 at 2:48 PM, Ivan Sergio Borgonovo <[email protected]> wrote: > On Mon, 26 Oct 2009 14:56:26 -0400 > Merlin Moncure <[email protected]> wrote: > >> On Mon, Oct 26, 2009 at 11:05 AM, Ivan Sergio Borgonovo >> <[email protected]> wrote: >> > To make it more concrete I came up with: >> > >> > select coalesce(u.mail,j.mail) from ( >> > select (array['[email protected]','[email protected]'])[i] as mail >> > from generate_series(1,2) i) j >> > left join users u on upper(u.mail)=upper(j.mail); >> >> how about this: >> select coalesce(u.mail,j.mail) from >> ( >> values ('[email protected]'), ('[email protected]') >> ) j(mail) >> left join users u on upper(u.mail)=upper(j.mail); > > Yours is between 4 to 10 times faster excluding time on client side > to escape the strings. > > I'll play a bit with client code to see if the advantage is kept. > > It looks nicer too. > > Currently I'm testing with very few match between input array and > user table. > Will this have different impact on the 2 methods?
nope, in both cases the server has to build a set first before executing the join, so it comes down to doing the awkward 'create then expand the array' vs. 'direct set build method'. The array method wouldn't be quite as bad if php had the ability to operate directly with postgresql arrays... merlin -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
