SQL performance

From: Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: SQL performance
Date: 2013-06-02 19:39:02
Message-ID: CAAXGW-wJjFtEygtoegnxKScMkedvCAP1FZKDd0Sm8EP=fq1WYQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Szymon Guz 2013-06-02 19:42:12 Re: SQL performance
Previous Message Jeff Janes 2013-06-01 02:38:19 Re: Best practice when reindexing in production