Could you please attache the plan with explain buffers verbose? thank you
2013/11/21 Robert DiFalco <robert.difa...@gmail.com> > UNION and subselect both performed better than EXISTS for this particular > case. > > > On Thu, Nov 21, 2013 at 12:31 PM, desmodemone <desmodem...@gmail.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.difa...@gmail.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? >>> >> >> >
