Re: SQL performance

On 2 June 2013 21:39, Robert DiFalco <robert.difalco@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