On 2 June 2013 21:39, Robert DiFalco <robert.difa...@gmail.com> 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, // 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 > Hi Robert, could you show us the plans? thanks, Szymon
