Re: UNION versus SUB SELECT - Mailing list pgsql-performance

From desmodemone
Subject Re: UNION versus SUB SELECT
Date
Msg-id CAEs9oFmh_HWbC_JZ9Ng1G8hXm4YrfPrNM+51M+F0h6pTMo-E5A@mail.gmail.com
Whole thread Raw
In response to UNION versus SUB SELECT  (Robert DiFalco <robert.difalco@gmail.com>)
Responses Re: UNION versus SUB SELECT  (Robert DiFalco <robert.difalco@gmail.com>)
Re: UNION versus SUB SELECT  (Merlin Moncure <mmoncure@gmail.com>)
List pgsql-performance
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.difalco@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?

pgsql-performance by date:

Previous
From: Robert DiFalco
Date:
Subject: UNION versus SUB SELECT
Next
From: Robert DiFalco
Date:
Subject: Re: UNION versus SUB SELECT