Absolutely:
explain analyze verbose
select c.user_id
from contact_entity c left outer join contact_entity c1 on c1.owner_id = c.user_id and c1.user_id = c.owner_id
where NOT c.blocked AND NOT c1.blocked AND c.owner_id = 24 AND c.user_id != 24
AND c.user_id IS NOT NULL AND c1.user_id IS NOT NULL
group by c.user_id;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Group (cost=0.00..9.00 rows=1 width=8) (actual time=0.170..0.301 rows=8 loops=1)
Output: c.user_id
-> Merge Join (cost=0.00..9.00 rows=1 width=8) (actual time=0.166..0.270 rows=17 loops=1)
Output: c.user_id
Merge Cond: (c.user_id = c1.owner_id)
-> Index Scan using idx_contact_mutual on public.contact_entity c (cost=0.00..5.10 rows=2 width=16) (actual time=0.146..0.164 rows=11 loops=1)
Output: c.id, c.blocked, c.first_name, c.last_name, c.owner_id, c.user_id
Index Cond: ((c.owner_id = 24) AND (c.user_id IS NOT NULL))
Filter: (c.user_id <> 24)
Rows Removed by Filter: 1
-> Index Scan using idx_contact_mutual on public.contact_entity c1 (cost=0.00..6.45 rows=1 width=16) (actual time=0.012..0.049 rows=18 loops=1)
Output: c1.id, c1.blocked, c1.first_name, c1.last_name, c1.owner_id, c1.user_id
Index Cond: ((c1.user_id IS NOT NULL) AND (c1.user_id = 24))
Total runtime: 0.388 ms
(14 rows)
explain analyze verbose
select c.user_id
from contact_entity c
where c.owner_id=24 and c.user_id<>24 and c.user_id IS NOT NULL and NOT c.blocked and (exists(
select 1
from contact_entity c1
where NOT c1.blocked and c1.owner_id=c.user_id and c1.user_id IS NOT NULL and c1.user_id=c.owner_id))
group by c.user_id;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Group (cost=0.00..9.00 rows=1 width=8) (actual time=0.048..0.159 rows=8 loops=1)
Output: c.user_id
-> Merge Semi Join (cost=0.00..9.00 rows=1 width=8) (actual time=0.044..0.137 rows=9 loops=1)
Output: c.user_id
Merge Cond: (c.user_id = c1.owner_id)
-> Index Scan using idx_contact_mutual on public.contact_entity c (cost=0.00..5.10 rows=2 width=16) (actual time=0.024..0.042 rows=11 loops=1)
Output: c.id, c.blocked, c.first_name, c.last_name, c.owner_id, c.user_id
Index Cond: ((c.owner_id = 24) AND (c.user_id IS NOT NULL))
Filter: (c.user_id <> 24)
Rows Removed by Filter: 1
-> Index Scan using idx_contact_mutual on public.contact_entity c1 (cost=0.00..6.45 rows=1 width=16) (actual time=0.011..0.047 rows=16 loops=1)
Output: c1.id, c1.blocked, c1.first_name, c1.last_name, c1.owner_id, c1.user_id
Index Cond: ((c1.user_id IS NOT NULL) AND (c1.user_id = 24))
Total runtime: 0.224 ms
(14 rows)
The only difference I see between the EXISTS and LEFT OUTER JOIN is the Merge Join versus the Merge Semi Join. Then again, there may be a third option for this query besides those two that will be much better. But those are the only two reasonable variations I can think of.
The GROUP BY versus the DISTINCT on c.user_id makes no impact at all on the plan. They are exactly the same.
On Sun, Jun 2, 2013 at 12:42 PM, Szymon Guz <mabewlun@xxxxxxxxx> wrote:
On 2 June 2013 21:39, Robert DiFalco <robert.difalco@xxxxxxxxx> wrote:
I have a table called contacts. It has a BIGINT owner_id which references a record in the user table. It also has a BIGINT user_id which may be null. Additionally it has a BOOLEAN blocked column to indicate if a contact is blocked. The final detail is that multiple contacts for an owner may reference the same user.I have a query to get all the user_ids of a non-blocked contact that is a mutual contact of the user. The important part of the table looks like this:CREATE TABLE contacts(id BIGINT PRIMARY KEY NOT NULL, // generatedblocked BOOL,owner_id BIGINT NOT NULL,user_id BIGINT,FOREIGN KEY ( owner_id ) REFERENCES app_users ( id ) ON DELETE CASCADE,
FOREIGN KEY ( user_id ) REFERENCES app_users ( id ) ON DELETE SET NULL);CREATE INDEX idx_contact_owner ON contacts ( owner_id );CREATE INDEX idx_contact_mutual ON contacts ( owner_id, user_id ) WHERE user_id IS NOT NULL AND NOT blocked;The query looks like this:explain analyze verboseselect c.user_idfrom contact_entity cwhere c.owner_id=24 and c.user_id<>24 and c.user_id IS NOT NULL and NOT c.blocked and (exists (select 1from contact_entity c1where NOT c1.blocked and c1.owner_id=c.user_id and c1.user_id IS NOT NULL and c1.user_id=24))group by c.user_id;This will get all the users for user 24 that are mutual unblocked contacts but exclude the user 24.I have run this through explain several times and I'm out of ideas on the index. I note that I can also right the query like this:explain analyze verboseselect distinct c.user_idfrom contact_entity c left outer join contact_entity c1 on c1.owner_id = c.user_id and c1.user_id = c.owner_idwhere NOT c.blocked AND NOT c1.blocked AND c.owner_id = 24 AND c.user_id <> 24AND c.user_id IS NOT NULL AND c1.user_id IS NOT NULLgroup by c.user_id;I don't notice a big difference in the query plans. I also notice no difference if I replace the GROUP BY with DISTINCT.My question is, can this be tightened further in a way I haven't been creative enough to try? Does it matter if I use the EXISTS versus the OUTER JOIN or the GROUP BY versus the DISTINCT.Is there a better index and I just have not been clever enough to come up with it yet? I've tried a bunch.Thanks in advance!!RobertHi Robert,could you show us the plans?thanks,Szymon
