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

On Mon, Oct 26, 2009 at 2:48 PM, Ivan Sergio Borgonovo
<mail(at)webthatworks(dot)it> wrote:
> 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?

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