Re: UNION versus SUB SELECT

Could you please attache the plan with explain buffers verbose?

thank you

2013/11/21 Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>

> UNION and subselect both performed better than EXISTS for this particular
> case.
>
>
> On Thu, Nov 21, 2013 at 12:31 PM, desmodemone <desmodemone(at)gmail(dot)com>wrote:
>
>> Hi Robert, could you try with "exists" ?
>>
>> SELECT c.*
>> FROM contacts c
>> WHERE exists ( SELECT 1 FROM phone p WHERE p.addr =? and p.contact_id=
>> c.id )
>> OR exists (SELECT 1 FROM email e WHERE e.addr = ? and e.contact_id=c.id);
>>
>>
>>
>>
>>
>> 2013/11/21 Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>
>>
>>> I have found this:
>>>
>>> SELECT c.*
>>> FROM contacts c
>>> WHERE c.id IN ( SELECT p.contact_id FROM phone p WHERE p.addr = ? )
>>> OR c.id IN (SELECT e.contact_id FROM email e WHERE e.addr = ? );
>>>
>>> To have a worse plan than:
>>>
>>> SELECT * FROM contacts where id IN (
>>> ( SELECT c.id FROM contacts c
>>> JOIN phone p ON c.id = p.contact_id AND p.addr = ?
>>> UNION
>>> SELECT c.id FROM contacts c
>>> JOIN email e ON c.id = e.contact_id AND e.addr = ? );
>>>
>>> Maybe this is no surprise. But after discovering this my question is
>>> this, is there another option I dont' know about that is logically the same
>>> that can perform even better than the UNION?
>>>
>>
>>
>