Re: joining an array with a table or...?

On Mon, 26 Oct 2009 14:56:26 -0400
Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Mon, Oct 26, 2009 at 11:05 AM, Ivan Sergio Borgonovo
> <mail(at)webthatworks(dot)it> wrote:
> > To make it more concrete I came up with:
> >
> > select coalesce(u.mail,j.mail) from (
> >  select (array['m(at)example1(dot)com','m(at)example2(dot)com'])[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 ('m(at)example1(dot)com'), ('m(at)example2(dot)com')
> ) 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?

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it