SQL performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux