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, // generated
blocked 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 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=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 verbose
select distinct 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;
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!!
Robert